Access queries running slow

Question

I have an application with the front end in Access 2007 (Access 2000 file) with the backend on SQL Server 2008. The application has been running for two years now and I have noticed that on some PCs the queries are taking forever to run while on others it runs quite fast. Each PC has its own file of Access, so they don't share the file.

Simple queries with just one table take a long time to run. The biggest table has 578,000 records.

Sometimes it takes 10 seconds to load a simple table with just 6 records. We are on a gigabit network.

Answer 1

I have removed the linked tables in Access and added them again. This seemed to improve the query times considerably.

Which Driver is better to connect to SQL Server 2008: SQL Server Native Client 10.0 v.2007.100.2531.00 or SQL Server v. 2000.85.1132.00

The ODBC Driver connection that I used is SQL Server Microsoft SQL Server ODBC Driver Version 03.85.1132

Answer 2

SET EXPLAIN ON... to see the overhead involved in the queries.

Try re-building your indexes and use clustering where appropiate to group the rows by customer id or whatever column your app requires. If this doesn't help, then the problem could be that SQL'08 has more overhead than '05 and you may need to increase memory or more CPU power.

Answer 3

do the pcs that take forever to run have less memory than the speedy ones?

also check the versions of the ODBC drivers on the clients.

Answer 4

Try updating statistics on the SQl server.

Answer 5

That could be caused by users joining a local table with a table on SQL Server.

Joining a local table with an SQL Server table forces Access to retrieve the entire SQL Server table and perform the join on the local computer.

We have had similar problems in the past.