Virtual Primary Key = Slow

[Neil]

I've been discussing here a SQL 7 view which scrolls slowly when linked to
an Access 2000 MDB. After trying various things, I've distilled it down to
the following: when the linked view has a virtual primary key, it is slow;
when there is no virtual primary key, it scrolls quickly.

Looking at Profiler, I see that when there is no virtual primary key, Access
sends a simple select command to SQL Server. However, when there is a
virtual primary key, Access gets a list of primary key values, and then uses
those values to get records from SQL Server in groups of 10.

This may account for why the scrolling is slow when there is a virtual
primary key. However, I wonder if there's any way to improve performance or
otherwise work around this apparent bottleneck.

The MS literature says that creating a virtual primary key will not affect
performance. However, since different methodologies for obtaining data are
used with and without the virtual primary key, it seems that it does affect
performance, and certainly does in this situation.

Thanks,

Neil

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You say its returning groups of 10 rows. The MaxRecords property in
..adp Views indicates how many records to return. Setting it to zero
will return all rows. Perhaps on your view it is set to 10.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkEp8YechKqOuFEgEQITFACfZMme4xVtnnvkn3UkGWnlcp quO1gAoO9d
msYkreO8/Wl5cW2+pJpYkQw9
=vcvE
-----END PGP SIGNATURE-----
Neil wrote:

I've been discussing here a SQL 7 view which scrolls slowly when linked to
an Access 2000 MDB. After trying various things, I've distilled it down to
the following: when the linked view has a virtual primary key, it is slow;
when there is no virtual primary key, it scrolls quickly.

Looking at Profiler, I see that when there is no virtual primary key, Access
sends a simple select command to SQL Server. However, when there is a
virtual primary key, Access gets a list of primary key values, and then uses
those values to get records from SQL Server in groups of 10.

This may account for why the scrolling is slow when there is a virtual
primary key. However, I wonder if there's any way to improve performance or
otherwise work around this apparent bottleneck.

The MS literature says that creating a virtual primary key will not affect
performance. However, since different methodologies for obtaining data are
used with and without the virtual primary key, it seems that it does affect
performance, and certainly does in this situation.

[Steve Jorgensen]

What you are seeing is the result of how Access handles Dynasets. When you
don't specify a virtual primary key, Access can't use a dynaset which is why
the simple select, but you can't edit the values in the recordset either.

If you don't need to edit the data, and you want the speed, you can just open
the view through a form that is set to use a snapshot instead of a dynaset.
This is preferable to not specifying the key because the key can also help
Access optimize generation of SQL to be processed server-side.

On Wed, 23 Mar 2005 08:07:09 GMT, "Neil" <nj****@pxdy.com> wrote:

I've been discussing here a SQL 7 view which scrolls slowly when linked to
an Access 2000 MDB. After trying various things, I've distilled it down to
the following: when the linked view has a virtual primary key, it is slow;
when there is no virtual primary key, it scrolls quickly.

Looking at Profiler, I see that when there is no virtual primary key, Access
sends a simple select command to SQL Server. However, when there is a
virtual primary key, Access gets a list of primary key values, and then uses
those values to get records from SQL Server in groups of 10.

This may account for why the scrolling is slow when there is a virtual
primary key. However, I wonder if there's any way to improve performance or
otherwise work around this apparent bottleneck.

The MS literature says that creating a virtual primary key will not affect
performance. However, since different methodologies for obtaining data are
used with and without the virtual primary key, it seems that it does affect
performance, and certainly does in this situation.

Thanks,

Neil

[Steve Jorgensen]

By the way, the Dynaset can be faster or slower depending on the amount of
data per column, the number of rows, the speed and latency of the network
connection, and how fast it is to do that particular key lookup through the
view query.

On Wed, 23 Mar 2005 00:56:11 -0800, Steve Jorgensen <no****@nospam.nospam>
wrote:

What you are seeing is the result of how Access handles Dynasets. When you
don't specify a virtual primary key, Access can't use a dynaset which is why
the simple select, but you can't edit the values in the recordset either.

If you don't need to edit the data, and you want the speed, you can just open
the view through a form that is set to use a snapshot instead of a dynaset.
This is preferable to not specifying the key because the key can also help
Access optimize generation of SQL to be processed server-side.

On Wed, 23 Mar 2005 08:07:09 GMT, "Neil" <nj****@pxdy.com> wrote:

I've been discussing here a SQL 7 view which scrolls slowly when linked to
an Access 2000 MDB. After trying various things, I've distilled it down to
the following: when the linked view has a virtual primary key, it is slow;
when there is no virtual primary key, it scrolls quickly.

Looking at Profiler, I see that when there is no virtual primary key, Access
sends a simple select command to SQL Server. However, when there is a
virtual primary key, Access gets a list of primary key values, and then uses
those values to get records from SQL Server in groups of 10.

This may account for why the scrolling is slow when there is a virtual
primary key. However, I wonder if there's any way to improve performance or
otherwise work around this apparent bottleneck.

The MS literature says that creating a virtual primary key will not affect
performance. However, since different methodologies for obtaining data are
used with and without the virtual primary key, it seems that it does affect
performance, and certainly does in this situation.

Thanks,

Neil

[Neil]

No.
"MGFoster" <[e-mail]> wrote in message
news:YR*****************@newsread3.news.pas.earthl ink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You say its returning groups of 10 rows. The MaxRecords property in
.adp Views indicates how many records to return. Setting it to zero
will return all rows. Perhaps on your view it is set to 10.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkEp8YechKqOuFEgEQITFACfZMme4xVtnnvkn3UkGWnlcp quO1gAoO9d
msYkreO8/Wl5cW2+pJpYkQw9
=vcvE
-----END PGP SIGNATURE-----
Neil wrote:

I've been discussing here a SQL 7 view which scrolls slowly when linked
to an Access 2000 MDB. After trying various things, I've distilled it
down to the following: when the linked view has a virtual primary key, it
is slow; when there is no virtual primary key, it scrolls quickly.

Looking at Profiler, I see that when there is no virtual primary key,
Access sends a simple select command to SQL Server. However, when there
is a virtual primary key, Access gets a list of primary key values, and
then uses those values to get records from SQL Server in groups of 10.

This may account for why the scrolling is slow when there is a virtual
primary key. However, I wonder if there's any way to improve performance
or otherwise work around this apparent bottleneck.

The MS literature says that creating a virtual primary key will not
affect performance. However, since different methodologies for obtaining
data are used with and without the virtual primary key, it seems that it
does affect performance, and certainly does in this situation.

[Neil]

No, I need to edit data, which is why I'm using a linked view in the first
place (otherwise, I'd just use a pass-through).

Anyway, re. this, it seems that the MDB should be able to do what the ADP
does: use a simple select to get a recordset for editing. But that's a whole
other discussion......

So, back to this situation, it seems there's no way around this. Which gets
back to the whole ADP vs. MDB thing: the MDB opens the view for editing in a
slow, chugging sort of way; the ADP opens the view for editing with fast
performance. One would say that the ADP is superior. Except you and others
say that the ADP has other problems, and that, even if one has to do a
workaround in an MDB to get the same performance, the MDB is superior as
it's less problematic.

At this point, if I can't resolve the speed issue, I'm going to have to
write a list of PK values to a temporary table, and join the temporary table
to a linked table to get an editable recordset that scrolls easily and
doesn't take several seconds to refresh. Seems that it would be a lot easier
to just use an ADP file and get good performance right out of the box. But
since you and others say that ADPs are problematic, I'm hesitant to do that.
But the case for MDBs seems a bit weak at this point, IMO.

Neil
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:1k********************************@4ax.com...

What you are seeing is the result of how Access handles Dynasets. When
you
don't specify a virtual primary key, Access can't use a dynaset which is
why
the simple select, but you can't edit the values in the recordset either.

If you don't need to edit the data, and you want the speed, you can just
open
the view through a form that is set to use a snapshot instead of a
dynaset.
This is preferable to not specifying the key because the key can also help
Access optimize generation of SQL to be processed server-side.

On Wed, 23 Mar 2005 08:07:09 GMT, "Neil" <nj****@pxdy.com> wrote:

I've been discussing here a SQL 7 view which scrolls slowly when linked to
an Access 2000 MDB. After trying various things, I've distilled it down to
the following: when the linked view has a virtual primary key, it is slow;
when there is no virtual primary key, it scrolls quickly.

Looking at Profiler, I see that when there is no virtual primary key,
Access
sends a simple select command to SQL Server. However, when there is a
virtual primary key, Access gets a list of primary key values, and then
uses
those values to get records from SQL Server in groups of 10.

This may account for why the scrolling is slow when there is a virtual
primary key. However, I wonder if there's any way to improve performance
or
otherwise work around this apparent bottleneck.

The MS literature says that creating a virtual primary key will not affect
performance. However, since different methodologies for obtaining data are
used with and without the virtual primary key, it seems that it does
affect
performance, and certainly does in this situation.

Thanks,

Neil

[Albert D. Kallal]

>

So, back to this situation, it seems there's no way around this. Which
gets back to the whole ADP vs. MDB thing: the MDB opens the view for
editing in a slow, chugging sort of way; the ADP opens the view for
editing with fast performance. One would say that the ADP is superior.
Except you and others say that the ADP has other problems, and that, even
if one has to do a workaround in an MDB to get the same performance, the
MDB is superior as it's less problematic.
Well, you mileage is going to vary on the above conclusion. For a lot of us
developers, the fact of moving to ADP means that we give up using all kinds
of our code libraries. We can't use DAO anymore for example. And, a good
many
of a design approaches might use local/temp tables..and again you can't do
that with ADP'S. For the most part, with some minor efforts,and some
experience, the performance we get with a mdb linked to sql server is as
good as
a ADP. This is NOT to say that this is true in EVERY SINGLE CASE. So,
frankly, when I design systems, I don't use virtual PK, and ALL tables
also expose the timestamp to ms-access (you need to be exposing the time
stamp fields to a mdb/odbc application for it to work correctly by the way).

So, am I saying that there is 'several" things that you must learn, and
several things that you must avoid, and several things that you will do to
make a linked mdb file work well with sql server? Yes, of course you do!!
The question then is the efforts to learn a few things on how to work with
sql server with a mdb file BETTER then you using a ADP project? Well, for
me...I prefer the mdb/odbc approach. however, in your case, if you like
the ADP approach, and don't miss local tables..and don't have a lot of DAO
code routines, or equivalents..then by all means use a ADP. The crossover
point
for some to use a ADP, and for some to use linked mdb file is going to be
close.

If you got a existing large application already written as a JET/mdb, and
you
need to migrate it to sql server, then without question, linking tables is
the way to go. Further, much less of your programming skill set will have to
be
re-learned.

However, if you are starting a project from scratch, and don't have a lot of
sql experience (or, perhaps you have a LOT, and little ms-access
experience),
then choosing a ADP may very well be a better choice for you.

This is not a thing that we are going to shoot you over here!. You got an
example where the ADP (with less effort) performance betters then the
mdb/odbc linked example. So, what!. If we find one bad cop, does that mean
we through out the whole legal system?

There is no hard and fast rule here as to which one is gong to be better.
Only YOU can decide which approach will work better based on your
skill set, your needs, and how you approach software design. I
have personally meet some MVP's that will NOT touch a mdb, and
ALWAYS use ADP projects!. So, there is certainly differing of
opinions here.

Seems that it would be a lot easier to just use an ADP file and get good
performance right out of the box. But since you and others say that ADPs
are problematic, I'm hesitant to do that. But the case for MDBs seems a
bit weak at this point, IMO.

Yes, if you are happy with the ADP..then I would certainly consider
going that way for you needs.

I don't think anyone here is saying that ADP's are problem matic. I
certainly will admit a good many say that ADP's are not worth the trouble
for the gains that you get in some areas...and losses you have to put up in
other areas.

So, there certainly is a trade off here..and which side you settle on is
going
to be much reflected on YOUR set of particular circumstances...not mine!

For example, when should one migrate a back end from a mdb file share to sql
server? Well, each case is going to be different....and NO single answer
exists...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Steve Jorgensen]

ADO does have this advantage over MDBs when the network is high latency, high
speed. You can read all the data at once, then send back updates as you go
rather than reading incrementally. Unfortunately, ADPs are famous for failing
random things at random times, and blocking the paths to fix it because of how
it tries to think for you.

If, for instance, you think you're going to have luck editing via a view that
joins 2 or more tables, it might work, or it might not. It also might work
today, and break the next time you install an IE service pack that updates
MDAC. There are many more scenarios like that.

The good thing about DAO obstacles is that theyr'e fairly predictable, and a
sensible work-around can pretty much be counted on to work. In fact, in your
case, I'd go with the remote batch tables.

On Wed, 23 Mar 2005 20:03:09 GMT, "Neil" <nj****@pxdy.com> wrote:

No, I need to edit data, which is why I'm using a linked view in the first
place (otherwise, I'd just use a pass-through).

Anyway, re. this, it seems that the MDB should be able to do what the ADP
does: use a simple select to get a recordset for editing. But that's a whole
other discussion......

So, back to this situation, it seems there's no way around this. Which gets
back to the whole ADP vs. MDB thing: the MDB opens the view for editing in a
slow, chugging sort of way; the ADP opens the view for editing with fast
performance. One would say that the ADP is superior. Except you and others
say that the ADP has other problems, and that, even if one has to do a
workaround in an MDB to get the same performance, the MDB is superior as
it's less problematic.

At this point, if I can't resolve the speed issue, I'm going to have to
write a list of PK values to a temporary table, and join the temporary table
to a linked table to get an editable recordset that scrolls easily and
doesn't take several seconds to refresh. Seems that it would be a lot easier
to just use an ADP file and get good performance right out of the box. But
since you and others say that ADPs are problematic, I'm hesitant to do that.
But the case for MDBs seems a bit weak at this point, IMO.

Neil
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:1k********************************@4ax.com.. .

What you are seeing is the result of how Access handles Dynasets. When
you
don't specify a virtual primary key, Access can't use a dynaset which is
why
the simple select, but you can't edit the values in the recordset either.

If you don't need to edit the data, and you want the speed, you can just
open
the view through a form that is set to use a snapshot instead of a
dynaset.
This is preferable to not specifying the key because the key can also help
Access optimize generation of SQL to be processed server-side.

On Wed, 23 Mar 2005 08:07:09 GMT, "Neil" <nj****@pxdy.com> wrote:

I've been discussing here a SQL 7 view which scrolls slowly when linked to
an Access 2000 MDB. After trying various things, I've distilled it down to
the following: when the linked view has a virtual primary key, it is slow;
when there is no virtual primary key, it scrolls quickly.

Looking at Profiler, I see that when there is no virtual primary key,
Access
sends a simple select command to SQL Server. However, when there is a
virtual primary key, Access gets a list of primary key values, and then
uses
those values to get records from SQL Server in groups of 10.

This may account for why the scrolling is slow when there is a virtual
primary key. However, I wonder if there's any way to improve performance
or
otherwise work around this apparent bottleneck.

The MS literature says that creating a virtual primary key will not affect
performance. However, since different methodologies for obtaining data are
used with and without the virtual primary key, it seems that it does
affect
performance, and certainly does in this situation.

Thanks,

Neil

[Neil]

> Well, you mileage is going to vary on the above conclusion. For a lot of

us
developers, the fact of moving to ADP means that we give up using all
kinds
of our code libraries. We can't use DAO anymore for example. And, a good
many
of a design approaches might use local/temp tables..and again you can't do
that with ADP'S.
Good points.
For the most part, with some minor efforts,and some
experience, the performance we get with a mdb linked to sql server is as
good as
a ADP. This is NOT to say that this is true in EVERY SINGLE CASE. So,
frankly, when I design systems, I don't use virtual PK,
Don't use virtual PK? So you update using pass-throughs and stored
procedures, rather than through bound forms?
This is not a thing that we are going to shoot you over here!. You got an
example where the ADP (with less effort) performance betters then the
mdb/odbc linked example. So, what!. If we find one bad cop, does that mean
we through out the whole legal system?
Well, what I meant was that if the linked view performed so much better with
the ADP than with the MDB, then that might be true across the board, as
well. When I first converted this application to a SQL Server back end some
years back, the main form bound to a link to the main table would hang when
scrolling through records. Eventually it cleared up -- probably from SQL
Server optimizing the index or from something else. But, for a long time, it
was impossible to scroll through the form.

So things like that, along with this situation, where the MDB exhibited
slowness or hanging in several cases, but the ADP was quick, got me to think
that perhaps the ADP would be better. But, as Steve points out in the next
message in this thread, the ADP comes with its own set of problems.
I don't think anyone here is saying that ADP's are problem matic.
Yes, many are.
So, there certainly is a trade off here..and which side you settle on is
going
to be much reflected on YOUR set of particular circumstances...not mine!

Indeed.

Thanks,

Neil

[Neil]

"The good thing about DAO obstacles is that theyr'e fairly predictable, and
a
sensible work-around can pretty much be counted on to work."

Yes, a good point. Thanks,

Neil
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:p0********************************@4ax.com...

ADO does have this advantage over MDBs when the network is high latency,
high
speed. You can read all the data at once, then send back updates as you
go
rather than reading incrementally. Unfortunately, ADPs are famous for
failing
random things at random times, and blocking the paths to fix it because of
how
it tries to think for you.

If, for instance, you think you're going to have luck editing via a view
that
joins 2 or more tables, it might work, or it might not. It also might
work
today, and break the next time you install an IE service pack that updates
MDAC. There are many more scenarios like that.

The good thing about DAO obstacles is that theyr'e fairly predictable, and
a
sensible work-around can pretty much be counted on to work. In fact, in
your
case, I'd go with the remote batch tables.

On Wed, 23 Mar 2005 20:03:09 GMT, "Neil" <nj****@pxdy.com> wrote:

No, I need to edit data, which is why I'm using a linked view in the first
place (otherwise, I'd just use a pass-through).

Anyway, re. this, it seems that the MDB should be able to do what the ADP
does: use a simple select to get a recordset for editing. But that's a
whole
other discussion......

So, back to this situation, it seems there's no way around this. Which
gets
back to the whole ADP vs. MDB thing: the MDB opens the view for editing in
a
slow, chugging sort of way; the ADP opens the view for editing with fast
performance. One would say that the ADP is superior. Except you and others
say that the ADP has other problems, and that, even if one has to do a
workaround in an MDB to get the same performance, the MDB is superior as
it's less problematic.

At this point, if I can't resolve the speed issue, I'm going to have to
write a list of PK values to a temporary table, and join the temporary
table
to a linked table to get an editable recordset that scrolls easily and
doesn't take several seconds to refresh. Seems that it would be a lot
easier
to just use an ADP file and get good performance right out of the box. But
since you and others say that ADPs are problematic, I'm hesitant to do
that.
But the case for MDBs seems a bit weak at this point, IMO.

Neil
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:1k********************************@4ax.com. ..

What you are seeing is the result of how Access handles Dynasets. When
you
don't specify a virtual primary key, Access can't use a dynaset which is
why
the simple select, but you can't edit the values in the recordset
either.

If you don't need to edit the data, and you want the speed, you can just
open
the view through a form that is set to use a snapshot instead of a
dynaset.
This is preferable to not specifying the key because the key can also
help
Access optimize generation of SQL to be processed server-side.

On Wed, 23 Mar 2005 08:07:09 GMT, "Neil" <nj****@pxdy.com> wrote:

I've been discussing here a SQL 7 view which scrolls slowly when linked
to
an Access 2000 MDB. After trying various things, I've distilled it down
to
the following: when the linked view has a virtual primary key, it is
slow;
when there is no virtual primary key, it scrolls quickly.

Looking at Profiler, I see that when there is no virtual primary key,
Access
sends a simple select command to SQL Server. However, when there is a
virtual primary key, Access gets a list of primary key values, and then
uses
those values to get records from SQL Server in groups of 10.

This may account for why the scrolling is slow when there is a virtual
primary key. However, I wonder if there's any way to improve performance
or
otherwise work around this apparent bottleneck.

The MS literature says that creating a virtual primary key will not
affect
performance. However, since different methodologies for obtaining data
are
used with and without the virtual primary key, it seems that it does
affect
performance, and certainly does in this situation.

Thanks,

Neil