Access queries running slow

views:

answers:

+2 Q:

Access queries running slow

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.

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.

Lieven 2010-10-19 13:14:04

I haven't changed anything on the database for the past year, and there are no running local tables.

Rick 2010-10-19 13:21:35

What have you tried? Have you looked at your Indexing? Do you have Primary/Foreign Key relationships defined?

Brad 2010-10-19 13:14:11

I do have Primary Keys on all my tables and some foreign keys. Can I add some indexes to speed up some really slow queries?

Rick 2010-10-19 13:48:28

You can add indexes, but they have to be carfully considered. I'm not sure how Access optimizes queries into SQL, but try recreting the queries in SSMS and seeing what the execution plan (CTRL+M) is like. It may even suggest some indexes for you, but be careful -- it's not always right. And don't overdo it.

Brad 2010-10-19 14:00:37

Can you post an example slow-running query?

Brad 2010-10-19 14:01:19

Try updating statistics on the SQl server.

HLGEM 2010-10-19 14:02:30

+1 A:

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.

Beth 2010-10-19 15:39:07

Most PCs have 2 GBs of RAM

Rick 2010-10-21 11:38:15

+1 A:

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.

Frank Computer 2010-10-25 20:01:32

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

Rick 2010-10-25 20:02:11

I would use the SQL Server Native client 10.0. That's the newest one, and is designed to take advantage of the new features in 2008. It's also faster than any other ODBC driver I've ever used with SQL Server.

David-W-Fenton 2010-10-26 21:23:24

ansaurus

tags:

views:

answers:

Access queries running slow

related questions