Query runs long in one db, fast in another....

[rfuscjr via AccessMonster.com]

This is truly bizzare. I have a query that runs for hours in one Access db.
When I import it into another Access db, it runs in minutes. I compacted and
repaired the original, relinked tables etc. Nothing makes it run faster. I
have imported the query in several dbs. I did find another where it appeared
to run very long. Still in others it runs in minutes. Ideas?

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200704/1

[Rich P]

One thing to note here is that if it is taking Jet more than a few
seconds to process a query, then it is either time to use a bigger
engine (like sql server)

-or if this is an option -

to break down the original query into smaller queries that Jet can
handle (eliminate as many joins as you can - those are the performance
killers in Access). You will be amazed how fast Jet can process a bunch
of byte sized queries in a loop for example, instead of one huge one in
one shot.

I had a job once years ago where people were running Access queries
(with a bunch of redundant joins) against large Access tables (well,
large for Access), and the queries were taking in excess of 45 minutes
to run. Once I convinced them to upgrade to sql Server(2000), the exact
same queries (with all the same redundant joins) wrapped in sql server
stored procedures took only miliseconds to run using ADO.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Tom van Stiphout]

On 06 Apr 2007 21:34:15 GMT, Rich P <rp*****@aol.co mwrote:

I agree with you on redundant joins: redundancy is never a good idea.
But given a correct albeit complex query on a correct database design,
there are only very few scenarios where VBA code will be faster than a
query. That's the power of set-based instructions.

Also, another db engine with the same bad design and bad query does
not necessarily perform significantly different. I think your example
to the contrary is an exception, or was not as much the same as you're
stating here. Of course you were trying to compare apples to apples
(as much as that's possible with these two db engines) and for example
you had the Access back-end database on the local machine.

Breaking out a complex query in smaller pieces is not necessarily
going to make a difference. That's what the query processor does
anyway: get the total statement (be it from one humongous query or
from several smaller ones) and then decomposing it to come up with
smaller parts to execute. Your "hints" to Jet that you think it should
process as particular sub-queries are going to be ignored.

-Tom.

>One thing to note here is that if it is taking Jet more than a few
seconds to process a query, then it is either time to use a bigger
engine (like sql server)

-or if this is an option -

to break down the original query into smaller queries that Jet can
handle (eliminate as many joins as you can - those are the performance
killers in Access). You will be amazed how fast Jet can process a bunch
of byte sized queries in a loop for example, instead of one huge one in
one shot.

I had a job once years ago where people were running Access queries
(with a bunch of redundant joins) against large Access tables (well,
large for Access), and the queries were taking in excess of 45 minutes
to run. Once I convinced them to upgrade to sql Server(2000), the exact
same queries (with all the same redundant joins) wrapped in sql server
stored procedures took only miliseconds to run using ADO.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[kotaro24]

Check the 'Indexes' button on the table(s) in each database you are
querying. It's possible that, if you imported the query but the tables
were already in place, that there is an index that is aiding the query
on one of the databases.