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 |
|
kbearhome
Starting Member
36 Posts |
 Posted - 2003-08-22 : 11:47:39
|
| We have some users who run queries on SQL server through Access with linked tables. Sometimes it slows down our CRM so that people can't save records. We are a small company (probably 20-25 SQL users at any one time) with small databases so we're processing about a few hundred, maybe a few thousand, records in a query. What would cause this to slow the server using Access when the results set has already been returned? This morning a user opened her Access form that has linked tables and it was the culprit. I generate reports with Excel (and MS Query) all the time and it doesn't slow down the server.Thanks for any input. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-22 : 12:33:23
|
| You should run SQL Profiler to determine where the slowness is (which queries). SQL Profiler will show what the culprit is and then you can determine if a rewrite is needed, additional indexes are needed, or maybe even a schema change.Tara |
 |
|
|
kbearhome
Starting Member
36 Posts |
Posted - 2003-08-22 : 13:02:08
|
| This is a third party product so I cannot make a schema change and I'm not sure how much I can re-write the queries. They link to the tables and then create queries or forms in Access. Looking at it again, it seems that it is actually locking the recordset even after the results are returned to Access. The fact that the query is still open on their computer is locking the recordset?? Does this make sense? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-22 : 13:11:34
|
| Well it depends on what the application is doing. The application could be processing more stuff and hasn't yet committed anything.Since this is a third party product, you will most likely only gain some performance by examining indexes and also looking into upgrading the hardware if it is a big problem. You can add additional indexes to third party products, but just be careful when you do so because it could negatively affect inserts. SQL Profiler will help you out.Tara |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-22 : 20:39:27
|
| You're probably suffering from ODBC overhead and improper locks used by MS Access forms (if you're using forms to access the data)Which version of Access are you using? If it's earlier than 2000, you are probably stuck with the performance you have now. If newer, you can try upgrading the MDAC components and see if they improve performance. You can get them here:http://www.microsoft.com/data/download.htmGet both the MDAC RTM or latest SP for version 2.6 or 2.7, and also download and install the Jet database drivers too. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-23 : 22:26:28
|
| How many controls on a form?I've seen a form open as many as 12 connections per user...if Updateable, how many locks is that?How complex are the queries?Do you have nested queries after nested queries?You are only as efficient as you're front end...."all logic in the backend"the rally cry...Brett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
|
|
|
|
|
|
|
|
Access queries slow server