Tools to use are profiler and query analyser; profiler shows you what is
happening, query analyser allows you to look at query plans so you can see
where bottlenecks can occur.
Look closely at your indexing. You can inspect the relative effect of
adding/removing indexes using query analyser. Sometimes an index actually
decreases performance so each time you tweak check out the performance hit.
Check locking, you can do this either using Enterprise Manager or in query
analyser (look at sp_lock in BOL).
Views, personally I hate views, I have seen them abused so often and if a
system is performing poorly it is the first thing I look at. A chum of mine
ended up writing an analysis tool for views because we had so much trouble
with people writing something which worked ok when the system was installed
but then ground to a horrendous halt when the system was put under load.
If you are using linked tables then look at using pass-through queries for
large data updates or for pulling lists you could also look at using local
tables for static lists.
ADO can give a significant performance boost over DAO but not in every case
and is not a cure all option by any stretch of the imagination, it is
something you should look at in order to tweak performance not to try and
rescue a sick dog.
--
Terry Kreft
"Jim Devenish" <internet.shopping@foobox.comwrote in message
news:1155563712.300903.285080@b28g2000cwb.googlegr oups.com...
Quote:
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.
>
I am at a loss as to how to analyse the problem. I cannot expect
others to solve the problem but I would welcome guidance on how to
explore the poor performance.
>
I am using DAO but wonder whether I should move over to ADO. I have
studied the book by Chipman and Baron and could convert the VBA code to
use ADO. What I cannot see how to do is specifically use ADO for such
things as bound forms or combo-boxes. If a form is bound to a query or
to an SQL statement how does one choose whether this uses DAO or ADO?
>
I am seeking help on three topics:
1 what tools or techniques are available to track down the causes of
poor performance?
2 should I change to ADO?
3 how do I make bound forms use ADO?
>
