March 17, 2004 at 4:12 pm
Hi,
I have a SQL Server holding <100k records in two tables. 8 people onsite are querying it though Access 97, 4 onsite, 4 offsite accessing it through the web via a DSN. There is no application. They are looking at it though the regular Access front-end.
The problem comes in when one of the off-site users times out. Access will have already locked the first page of records that the user was wanting to look at. Since those records are locked, then all of the other users will start to time out as well.
When Access times out it the process is still left hanging out there and cannot be killed. With the 8 people banging on these tables with the records locked, everything grinds to a halt and you have to bounce the entire box. This begins to even affect the performance on other databases as well.
Any help is appreciated,
Richard Binnington
March 18, 2004 at 2:10 am
If Access isn't releasing its connection then there is a problem with Access.
Many issues to look at here (hopefully some may be of help):
1. If you don't care how long a query takes, set the timeout to 0 in Access
2. If all queries through access don't do update, then set database
read-only. This will take care of locking
3. Ensure that the users are returning only the records they actually need.
Many people ask for everything and then filter down. This will kill
bandwidth and performance.
4. Access has a habit of pulling large amounts of data down, then performing
the joins itself. This will give the impression that sql is performing
badly, when in fact it's only doing what it's been told
5. With point 4 in mind, get people to access the data through stored
procedures. This will ensure that sql server is in control and you
can keep better control of it this way.
Hope some of the above helps.
---------------------------------------------
If data can screw you, it will; never assume the data are correct.
March 18, 2004 at 7:24 am
Along the same lines as Kevin suggested, use pass-through queries in Access so that the query is executed on SQL Server.
March 18, 2004 at 8:34 am
Thanks for your responses,
We are looking into the stored procedure angle. They are actually using a form to filter the data and they don't return any records when the form initially loads.
I have found out that the timeout is happening in ODBC not in Access. They have upped their ODBC timeouts to a minute and a half, and that has helped some.
On the SQL Server side, when ODBC disconnects it leaves process that can't be killed through Enterprise Manager (or at least so it says it can't kill them). Is there any way to kill a process no matter what?
Richard
March 18, 2004 at 9:36 am
Something that I just noticed in Access queries, if you go into the properties of the query, you can see properties named "Record Locks" and "Recordset Type". What are these set to? Also, if you default the timeout to 0 (unlimited), what happens to the locks?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply