SQL Server - MS Access ODBC Connection Slow

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

SQL Server - MS Access ODBC Connection Slow

Author

Topic

ihaighthis
Starting Member

3 Posts

Posted - 2011-02-25 : 17:07:07

I am new to SQL Servers, but have a lot of experience with MS Access. We have put a lot of our data on an SQL Server because of the size limits in MS Access. We have several tables with over 2 million records. Now that the data sets are large the queries we try to run in MS Access take forever. Sometimes they say there is not enough temp space. I am sure we have not set up the server for optimal performance. Can anyone give me some ideas on how to improve the performance?
Thank you so much for your help!

Tammy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-25 : 17:09:06

You'll need to investigate your indexes for starters. Do you have more than just the PK on your tables as far as indexes go?

Show us a sample query with DDL (including indexes for the table(s)) for us to help you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ihaighthis
Starting Member

3 Posts

Posted - 2011-02-25 : 17:29:26

Here is a query that takes a long time:
SELECT t_SmartInstall.MTRSERV_REQ_NUM, t_SmartInstall.INSTL_SERV_NUM INTO t_SmartInstallAppendStep2
FROM t_SmartInstall LEFT JOIN q_SmartInstallNew ON t_SmartInstall.MTRSERV_REQ_NUM = q_SmartInstallNew.MTRSERV_REQ_NUM
WHERE (((q_SmartInstallNew.MTRSERV_REQ_NUM) Is Null));
t_SmartInstall is a local table in MS Access
q_SmartInstallNew is a passthrough query to an SQL Server table
t_SmartInstallAppendStep2 is a local table in MS Access

No Primary Key or Indexes are defined for t_SmartInstallNew (the SQL Server table)

I do not know what DDL is.
Thanks.

Tammy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-25 : 17:41:38

If you have no indexes, then that's the problem. Add indexes to support your queries, and then problem solved.

Start with adding primary keys to every table!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-25 : 18:10:40

Access-ODBC will instantiate cursors against SQL Server linked tables, and they can be extremely slow even with a good indexing scheme. Joining local and linked tables is most likely going to perform badly no matter how many indexes you have. Same applies to 2 or more linked tables. Any table that has more than 500-1000 rows especially should not join across DB platforms.

You should start using pass-through queries instead of querying linked tables. It's better to upsize/transfer all of your local Access tables to SQL Server, and have it handle all the query processing. This should have no impact on any forms or reports, as long as the linked table has the same name, or you modify them to use the new linked table.

ihaighthis
Starting Member

3 Posts

Posted - 2011-02-25 : 18:18:47

Ok I will try this. As far as indexing goes, what criteria would you suggest when deciding which fields to index? I already know what needs to be the primary key.
Thanks again.

Tammy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources