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.
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 |
|
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_SmartInstallAppendStep2FROM t_SmartInstall LEFT JOIN q_SmartInstallNew ON t_SmartInstall.MTRSERV_REQ_NUM = q_SmartInstallNew.MTRSERV_REQ_NUMWHERE (((q_SmartInstallNew.MTRSERV_REQ_NUM) Is Null));t_SmartInstall is a local table in MS Accessq_SmartInstallNew is a passthrough query to an SQL Server tablet_SmartInstallAppendStep2 is a local table in MS AccessNo 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 |
|
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 |
 |
|
|
|
|