Re: SQL Server backend poor performance
- From: "Rick Brandt" <rickbrandt2@xxxxxxxxxxx>
- Date: Mon, 14 Aug 2006 14:06:17 GMT
Jim Devenish wrote:
I have converted an Access back-end to SQL Server back-end but am
having some problems. The Access to Access application has been
running well for some years.
I have successfully copied all the data to SQL Server and linked the
tables to the front end .mdb (I am not using .adp). Some queries were
performing poorly so I have converted these to Views and linked to
them.
Everything works well with good response but when about 8-10 users are
in the whole thing slows down to an unbearable extent. Using the
profiler I can see that there are between 20 - 30 connections during
the time that response is good, but suddenly with no apparent increase
in the number of connections the whole system grinds to a (near) halt.
This morning response was terrible with only about 3 users and fewer
than 10 connections.
My first guess would be to see if the performance issues are being caused by
locks being held on the tables/views. That is an area that can make a big
difference with just a few processes beign blocked.
Using SQL Server links to populate ComboBox and ListBox RowSources is a good
way to get a lock applied to that table. Even though that is just a READ
operation if the list is long enough Access will cache in a few pages of
rows and then hold a lock on the table until the entire list is pulled. If
the user never goes to the end of the list and keeps that form open the lock
can persist indefinitely.
I doubt that the DAO/ADO thing would make more than an incremental
difference.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
.
- Follow-Ups:
- Re: SQL Server backend poor performance
- From: Jim Devenish
- Re: SQL Server backend poor performance
- References:
- SQL Server backend poor performance
- From: Jim Devenish
- SQL Server backend poor performance
- Prev by Date: SQL Server backend poor performance
- Next by Date: Add Many Records
- Previous by thread: SQL Server backend poor performance
- Next by thread: Re: SQL Server backend poor performance
- Index(es):
Relevant Pages
|
