Query Slow In one database but not another

M

mattbob.brock

Hi- I have a problem with a query I am using.
I am updating a Access 2000 Database by rewriting much of its code. A
particular query uses several linked tables to an SQL Server and when I
run it in on the older database it executes almost instintaneously. If
I import the same linked tables, and tables it uses into the new
database, it gives the correct results, but takes about five minutes to
run. I am stumped at what could be causing the significant delay for
the query to run and was wondering if anyone heard of this before.
Like I said, I imported EVERY table, and linked table that it uses to
execute the query, as well as imported the query itself, so I do not
understand why the time to execute would be so significantly different.
Thanks for any help.

MattBob
 
T

theonesteve

What version of Access are you importing these tables into?

Regardless of version, check to ensure that the tables all have primary
keys and that unique fields are indexed.
 
M

mattbob.brock

I am using Access 2000 in both databases. And yes, there are primary
keys set for the tables. This problem is very perplexing- I don't
understand why it would do something like this at all.
 
M

mattbob.brock

I tried converting the database to Access 2002 format as well, and this
had no effect.
 
T

Tom Wickerath

Hi MattBob,

I have seen a fast query in Access 97 become painfully slow in Access 2002,
but in that case, the back-end file was .mdb (JET 3.5 for Access 97 and JET 4
for Access 2002), and the query was VERY ugly looking.

On the new .mdb file with the slow performance, try deleting the linked
tables, compact the database, and then create the links from scratch.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I am using Access 2000 in both databases. And yes, there are primary
keys set for the tables. This problem is very perplexing- I don't
understand why it would do something like this at all.
 
J

JerryW

Hi,

I'm a little confused. Did you import all the data from SQL Server or did
you relink to SQL Server? If you imported the data into Access tables, did
you create the indexes needed to support the query?

Did you set up the relationships in the Relationships window in the new
database?

If linking, check that you are linked to SQL Server and not through the old
Access database.

Also go to Tools, Options, General tab and turn off Name AutoCorrect.
 
M

mattbob.brock

I do not import any data. I imported the links themselves from the
other access database because I was getting this lag and I was not sure
if it may be because of how I was linking the tables (DAO instead of
DSN)....
But this appears to not be the case. All relationships from the other
database are also set in the new database also.

I have even tried creating a new access database with the one local
table I need and link to the SQL Server again and run the query but the
lag is the same.

I turned off Name AutoCorrect but this appears to have no effect.
 
M

mattbob.brock

So.... my next attempt is interesting.

I imported into a blank database every single object in the older
access database and then recreated all the relationships. I thought it
would be a copy of the older database at this point, right? Well no-
it still has the lag when running the SQL query. I verified the
options are all set the same and everything. I have no clue-
everything appears to be the same, but the Query takes much longer to
execute. What could the author of the older database have done to get
the query to run so fast????

The SQL query is a 3 table join (2 linked and 1 local) but returns only
under 20 results. I can't believe it takes so long to run.
 
R

Ron Hinds

The other database may be using SQL Pass-Through queries. This allows
processing of (in this case, part of) the query by the SQL Server. If it is
just a pure Jet operation then Access will have to bring *all* of the
records from *both* of the server tables over the network, then do the join
locally, then do any filtering, also locally. If you need to join a local
table to server tables, you need to create a named SQL Pass-Through query
that joins (and if possible, filters) the two server tables, then use that
named query to do the join to your local table.
 
Top