473,320 Members | 1,883 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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.com
http://www.accessmonster.com/Uwe/For...ccess/200704/1

Apr 6 '07 #1
3 2590
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 ***
Apr 6 '07 #2
On 06 Apr 2007 21:34:15 GMT, Rich P <rp*****@aol.comwrote:

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 ***
Apr 7 '07 #3
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.

Apr 9 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
6
by: Umar Farooq | last post by:
Hello all, Please bear with the long explanation of my scenario. As I'm relatively new to the query world, I like to write my queries using the visual toos such as the "View" option in SQL...
2
by: AG | last post by:
I am able to run a query which runs FAst in QA but slow in the application.It takes about 16 m in QA but 1000 ms on the Application.What I wanted to know is why would the query take a long time...
1
by: Nicolae Fieraru | last post by:
Hi All, I have a Table1 with ID, FirstName, Surname, Address1, Address2, PostCode, Suburb, Purchase. The table list all purchases made by all customers. A customer can have multiple purchases...
6
by: lesperancer | last post by:
SELECT distinct b.t_orno, b.t_pono FROM tblMonthlyBooking AS b, tblFilterDate, tblFilterDate AS tblFilterDate_1 WHERE (((b.t_yearMonth) Between . And .)); tblMonthlyBooking is a sql server...
7
by: KoliPoki | last post by:
Hello every body. I have a small issue. Problem: I have a table with 4 descriptor columns (type). I need to formulate a query to retrieve a count for each type so I can group by...etc. The...
4
by: shawno | last post by:
Hello, We are currently in the process of migrating our databases from a relic of a server to a new 4 processor dual-core box with 4 gigs of RAM. Overall, database performance is obviously...
8
by: SaltyBoat | last post by:
Needing to import and parse data from a large PDF file into an Access 2002 table: I start by converted the PDF file to a html file. Then I read this html text file, line by line, into a table...
1
by: gkinu | last post by:
I have 2 tables with a parent-child relationship. Parent table's primary key field is Entry_no and is of type VarChar(50). This parent table has about 50 fields. The child has 3 fields, Entry_No...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.