Query running... forever !? (1 Viewer)

[arj446]

Hi

We used to work on Access 1997 until yesterday, when we switched to Access 2010. For some reason, some very simple queries that used to work perfectly fine are so slow in the new version of Access that it's not even funny...

Here's the SQL code of one of those queries :

SELECT Inv_Inquiry_BSKUFILE.Branch, Inv_Inquiry_BSKUFILE.VC, Inv_Inquiry_BSKUFILE.item, SCSFPRD948_BSKUFIL.SKSPLY
FROM Inv_Inquiry_BSKUFILE INNER JOIN SCSFPRD948_BSKUFIL ON (Inv_Inquiry_BSKUFILE.item = SCSFPRD948_BSKUFIL.SKITEM) AND (Inv_Inquiry_BSKUFILE.VC = SCSFPRD948_BSKUFIL.SKVLIN) AND (Inv_Inquiry_BSKUFILE.Branch = SCSFPRD948_BSKUFIL.SKCLNT);

Used to take 15 seconds, it now can take up to 10 minutes.


The (real) funny thing is that a not so different query :

SELECT Inv_Inquiry_BSKUFIL_TR.Branch, Inv_Inquiry_BSKUFIL_TR.VC, Inv_Inquiry_BSKUFIL_TR.item, SCSFPRD948_BSKUFIL.SKSPLY, [Branch_Traction_ALL + Region].Region
FROM (Inv_Inquiry_BSKUFIL_TR LEFT JOIN SCSFPRD948_BSKUFIL ON (Inv_Inquiry_BSKUFIL_TR.item = SCSFPRD948_BSKUFIL.SKITEM) AND (Inv_Inquiry_BSKUFIL_TR.VC = SCSFPRD948_BSKUFIL.SKVLIN) AND (Inv_Inquiry_BSKUFIL_TR.Branch = SCSFPRD948_BSKUFIL.SKCLNT)) LEFT JOIN [Branch_Traction_ALL + Region] ON Inv_Inquiry_BSKUFIL_TR.Branch = [Branch_Traction_ALL + Region].Branch;

takes 15 seconds max to run for the same number of records, although it's using "LEFT JOIN" instead of "INNER JOIN". And even funnier, in the second query, if I remove the [Branch_Traction_ALL + Region] table, again the query takes an eternity to run.

I precise that the [SCSFPRD948_BSKUFIL] table is a massive linked table from an ODBC database using IBM iSeries drivers.

Any insight / help would be greatly appreciated !

Thanks,


T.

 

[Guus2005]

a few things you can do:

Compact the Access database
Use passthrough queries
Investigate the network speed.
Make sure the right index tables are set on the ODBC Database.


HTH

 

[arj446]

a few things you can do:

Compact the Access database
Use passthrough queries
Investigate the network speed.
Make sure the right index tables are set on the ODBC Database.


HTH

Hey Guus

Thanks for your answer. I did compact the database, and the network speed is optimum : as I said, the "slowness" was not a problem with the former version of Access.

You kind of lost me at...

Use passthrough queries
Make sure the right index tables are set on the ODBC Database

 

[jdraw]

Further to Gus's suggestions, I would like to ask what testing did you do before the "cutover" to Acc2010? Did you not have any issues during testing?

As for passthrough and indexes, you'll have to look at (Google and Access help) Pass through queries with Access.
Gus is asking if you have the proper indexes on your tables.

Here's a link that may be useful regarding performance.
http://www.fmsinc.com/microsoftaccess/performance/linkeddatabase.html

 

[arj446]

Further to Gus's suggestions, I would like to ask what testing did you do before the "cutover" to Acc2010? Did you not have any issues during testing?

No issues during testing, I was even surprised some of the queries took so little time.

As for passthrough and indexes, you'll have to look at (Google and Access help) Pass through queries with Access.
Gus is asking if you have the proper indexes on your tables.

I'm looking into the passthrough queries and it's pretty interesting : had no idea you could do that. I'll definitely give it a try as soon as my daily "ToDo" is cleared.

As for the indexes... I'm not sure what I can do or should be doing there. Those are linked tables : from what I can see, I can't change the indexes and the fields I'm querying look indexed properly anyway.

 

[jdraw]

arj446

I sent a link re performance in my last post. I think I was editing when you were answering.

Also, google ... Kallal and Access performance. Albert has written several posts on performance /tuning.
Here's one
http://bytes.com/topic/access/answers/196076-access-performance-problems
Good luck.

 

[arj446]

OK so... I went through the documentation and links you sent : it doesn't really apply to me. First, I'm the only user on this database (it's a local one). What's more all the tables are linked from the server. And the query I'm running is the simplest.

In the example I gave on my first post :

SELECT Inv_Inquiry_BSKUFILE.Branch, Inv_Inquiry_BSKUFILE.VC, Inv_Inquiry_BSKUFILE.item, SCSFPRD948_BSKUFIL.SKSPLY
FROM Inv_Inquiry_BSKUFILE INNER JOIN SCSFPRD948_BSKUFIL ON (Inv_Inquiry_BSKUFILE.item = SCSFPRD948_BSKUFIL.SKITEM) AND (Inv_Inquiry_BSKUFILE.VC = SCSFPRD948_BSKUFIL.SKVLIN) AND (Inv_Inquiry_BSKUFILE.Branch = SCSFPRD948_BSKUFIL.SKCLNT);

The SCSFPRD948_BSKUFIL table is linked from the server, and the Inv_Inquiry_BSKUFIL is a local table I populate by copy/paste. So, nothing but basic.

I more or less understand your remarks about indexing, but then again : the primary key on the BSKUFIL table is the combination of 4 fields (SKCLNT/ SKRRLP / SKITEM / SKVLIN). As we're not using SKRRLP (it's basically set to blank by default), I tried indexing the Inv_Inquiry_BSKUFIL and created a primary key by combining (SKCLNT / SKITEM / SKVLIN) : the query is even slower. Index but no primary key : the same...

I also tried creating and using a passthrough query (which queries the SKSPLY field from the BSKUFIL table) with absolutely no gain of performance : again, the query is even slower.

 

[JoanneJames]

I have starting looking into the same problem as well. We moved from Access 2003 to 2010 - and now, a query that took 5 minutes to run prior (has many left-joins), now takes 30 minutes to run. I believe that this has to do with either the new version of Access or perhaps an OS update. I talked with our network guy and it runs same time on all 4 terminal services servers - so is not a memory issue. We noticed the CPU % is higher (around 25%) than before (around 5%).

He also said that that a database update (Progress) on another server (not a TS server), was taking 5 times as long also. I think that 2008 sp2 is on all of these with 2-8 processors (don't have details on). I was wondering if it's possible that the servers aren't configured properly for use with # processors?

This started around Feb 7 or so - we were also wondering if some update during that time could be the cause.

 

[jdraw]

arj446,

Why are there 3 copies of your query in your quote???
I don't see issues with the query.

SELECT Inv_Inquiry_BSKUFILE.Branch
, Inv_Inquiry_BSKUFILE.VC
, Inv_Inquiry_BSKUFILE.item
, SCSFPRD948_BSKUFIL.SKSPLY
FROM Inv_Inquiry_BSKUFILE INNER JOIN SCSFPRD948_BSKUFIL ON
(Inv_Inquiry_BSKUFILE.item = SCSFPRD948_BSKUFIL.SKITEM) AND
(Inv_Inquiry_BSKUFILE.VC = SCSFPRD948_BSKUFIL.SKVLIN) AND
(Inv_Inquiry_BSKUFILE.Branch = SCSFPRD948_BSKUFIL.SKCLNT)

Perhaps someone else using Access and IBM can offer more info.

 

[arj446]

arj446,

Why are there 3 copies of your query in your quote???
I don't see issues with the query.

Perhaps someone else using Access and IBM can offer more info.

My mistake : it's now corrected. Thanks !!