MS SQL Server ODBC - SQL Profiler and Access bound forms

Author

SQL Profiler and Access bound forms

RLoski

2006-10-24, 6:40 pm

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?

Russ

Sue Hoegemeier

2006-10-24, 6:40 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
<RLoski@discussions.microsoft.com> wrote:

>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?
>
>Russ

RLoski

2006-10-24, 6:40 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

"Sue Hoegemeier" wrote:

> 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

Sue Hoegemeier

2006-10-24, 6:40 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
<RLoski@discussions.microsoft.com> wrote:
[color=darkred]
>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
>
>"Sue Hoegemeier" wrote:
>

Sponsored Links