speeding up slow query

[jeffmoore]

How do I speed up a slow sql query?
Can I do this with a query def?
TIA
Jeff

 

[BuGlen]

Hi Jeff,

This is a hard question to answer without seeing the SQL being used.

Can you post a sample of the SQL?

- Glen

Know thy data.

 

[jeffmoore]

Okay you asked for it....

str1 = [Forms]![sampletestrequest]![wfcprodcode]

str2 = [Forms]![sampletestrequest]![TestType].Value

strSQL1 = "SELECT [PhysicalProperties].*, [PCSPECS].[Specification], [PCSPECS].[P/C], [PhysicalProperties].[Notes], False AS Selected FROM ([PCSPECS]"

strsql2 = " INNER JOIN [PhysicalProperties] ON [PCSPECS].[Specification] = [PhysicalProperties].[Spec]) INNER JOIN [Product] ON [PCSPECS].[P/C] = [Product].[ProdCode]"

strsql3 = " WHERE ((([PCSPECS].[Specification])='" & str2 & "') AND (([PCSPECS].[P/C])= " & str1 & &quot AND (([PhysicalProperties].[Notes])=[PCSPECS].[Pick Class] Or ([PhysicalProperties].[Notes]) Is Null) ) ;"

strsql = strSQL1 & strsql2 & strsql3

Set rst = db.OpenRecordset(strsql)

TIA
Jeff

 

[BuGlen]

Hi Jeff,

The SQL looks fairly well defined. Do you have indexes defined on your INNER JOIN columns? Indexes can help make joins more efficient, especially if the columns being used hold unique values.

Hope this helps.

- Glen

Know thy data.

 

[jeffmoore]

I have indexes on pcspecs.specifications and pcspecs.p/c
should I have any further indexes?
the query is running against linked tables.
also there is some extranious data in the tables... how does that stuff effect performance?
I've noticed that linked tables are far slower than tables that are part of the frontend.
Jeff

 

[BuGlen]

You could also add indexes to your [PhysicalProperties].[Spec] and [Product].[ProdCode] columns. Having indexes on both sides of the join should help performance. Also, adding indexes to the columns that you frequently in your WHERE clauses might help as well.

Linked tables will have slower access rates when the database is sitting on the network. A simple test to see if the network access is a problem is to copy the backend DB to your local workstation and link the tables to the local copy.

Here is a good article on Microsoft Support related to optimizing queries in Access 2000:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q209126

Hope this helps.

- Glen

Know thy data.

 

[jeffmoore]

Thanks I'll check out the article.
BTW any ideas on this ...
An empty linked table takes 10-12 secs to open(table view) while a slimilar table in the same backend takes less than a second to open.
Thanks for the input
Jeff

 

[BuGlen]

Have you compacted the back end DB lately? If the table had data which has been deleted and the DB hasn't been compacted, it may still be allocating space for that table.

- Glen

Know thy data.

 

[jeffmoore]

No, but there was never that much data in the table, max 30 records.
Would it help if I up sized the backend to a sql server?
I wrote a lot of the stuff in a mix of sql and DAO. if i up sized it would the dao stuff break?
Jeff

 

[cmmrfrds]

Are the linked table ODBC or Access tables? If ODBC, then make the query a pass-thru query and it will be much faster. It will return just the results set not the entire table to the Access app.

 

[jeffmoore]

access tables. I just put all the access tables in a mdb and link the tables to the frontend.
Jeff

 

[JonFer]

Another cause of slowdown in Access 2000 and above if you have many tables in your database is the Subdatasheet table option. In the table design properties, change this option to [None] if it is [Auto].

Opening a table with a sort saved on it runs the sort so you can check that as well. This shouldn't affect queries, just opening the table directly.

 

[jeffmoore]

how do i check to see if a sort is saved on the table?
Jeff

 

[JeremyNYC]

Why is the Property table in there? It's late, so maybe I'm not reading this closely enough, but it looks like the table is in there but you get no data from in and don't filter based on anything from it. If you don't need it, get rid of it.

Also, do you really need all of the fields from the PhysicalProperties table? If not, just grab the fields you need.

Also, do compact, as it might have some effect.

Here's a link to my favorite speed page:

http://www.fmsinc.com/tpapers/faster/index.html.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[JonFer]

Sorts saved with a table can be seen in the table properties popup when in table design view.