SQL Profiler and Access bound forms -- sql server odbc

RLoski
10/6/2006 9:22:02 AM

We have noticed for a long time queries originating from Access 97 that had
very long durations when traced using SQL Server profiler (2000) . Queries
that would complete in less than a four seconds in Query Analyzer showed
durations of twenty minutes and in one case two and a half hours. In spite
of the duration, the CPU times and Reads are reasonable. We are certain that
the users would complain immediately if they had had to wait that long, thus
we suspect that they have not been inconvenienced.

We have tables linked to SQL Server and I believe that the queries are
running as snapshot.

I tried an experiment. I set profiler to display the two profiler events
for batches starting. I opened a bound form in Access. The starting event
displayed, but not the completed event. Also, the form came back immediately
with data. The completion event wouldn't fire until I scrolled to the last
record. If the form stayed open ten minutes, then duration would ten minutes.
Is this the normal behavior?

My broader questions is this: how do we identify potential contention
issues using Profiler if we cannot rely on the duration to indicate that a
query is being delaye?

Sue Hoegemeier
10/6/2006 7:38:28 PM

What you are seeing in Profiler is correct. With a large
number of records, Jet will retrieve the records in batches
until the entire recordset is retrieved. You will see the
first set of data displayed in the form but Jet is still
retrieving batches when the form is first opened. If you
monitor your process in SQL Server using sp_who and sp_lock,
you can see more of what it's doing and you can see that
when the form is opened, it's still grabbing data. The whole
grab it in batches is how Jet handles the queries on the
linked tables.
So...it's Access not SQL Server. If you want to change the
behavior, you need to change it on the Access side.

-Sue

On Fri, 6 Oct 2006 09:22:02 -0700, RLoski
[quoted text, click to view]

RLoski
10/8/2006 6:04:01 PM

We have a circular problem. We are trying to make the case that using Access
rather than a .Net application (or even a VB 6 application) is going to hurt
the company. Thus we are looking for statistics to show that Access is bad.
If duration is long but user perceived duration is short, then is Access all
that bad (for this point only)?

I guess one argument we can make is that using Access is going to make it
hard to identify proper indexes.

Russ

[quoted text, click to view]

Sue Hoegemeier
10/8/2006 7:24:01 PM

Well....you don't have the form coded to work well with the
backend and that's more the problem. Can't see why you'd
really want to use a bound form if it's just a read only
display.
The index identification is not really an issue either. I
can write something real poorly in .Net or VB 6 and that
doesn't mean the application tool is the problem. It's a
matter of how you code the front end that makes a
difference.

-Sue

On Sun, 8 Oct 2006 18:04:01 -0700, RLoski
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server odbc

sql server odbc : SQL Profiler and Access bound forms