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]

Re: Virtual Primary Key = Slow

-----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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQkEp8YechKq OuFEgEQITFACfZM me4xVtnnvkn3UkG WnlcpquO1gAoO9d
msYkreO8/Wl5cW2+pJpYkQw9
=vcvE
-----END PGP SIGNATURE-----


Neil wrote:[color=blue]
> 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.[/color]

[Steve Jorgensen]

Re: Virtual Primary Key = Slow

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" <njones@pxdy.co m> wrote:
[color=blue]
>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
>
>[/color]

[Steve Jorgensen]

Re: Virtual Primary Key = Slow

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 <nospam@nospam. nospam>
wrote:
[color=blue]
>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" <njones@pxdy.co m> wrote:
>[color=green]
>>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
>>
>>[/color][/color]

[Neil]

Re: Virtual Primary Key = Slow

No.


"MGFoster" <me@privacy.com > wrote in message
news:YR90e.2340 $H06.2091@newsr ead3.news.pas.e arthlink.net...[color=blue]
> -----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:::mgf0 0 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQkEp8YechKq OuFEgEQITFACfZM me4xVtnnvkn3UkG WnlcpquO1gAoO9d
> msYkreO8/Wl5cW2+pJpYkQw9
> =vcvE
> -----END PGP SIGNATURE-----
>
>
> Neil wrote:[color=green]
>> 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.[/color][/color]

[Neil]

Re: Virtual Primary Key = Slow

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" <nospam@nospam. nospam> wrote in message
news:1kb2419cj8 ionjokv3dm5qohr 9v9hlf7qt@4ax.c om...[color=blue]
> 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" <njones@pxdy.co m> wrote:
>[color=green]
>>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
>>
>>[/color]
>[/color]

[Albert D. Kallal]

Re: Virtual Primary Key = Slow

>[color=blue]
> 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.[/color]

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..th en 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.
[color=blue]
>
> 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.[/color]

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....an d NO single answer
exists...



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

http://www.members.shaw.ca/AlbertKallal

[Steve Jorgensen]

Re: Virtual Primary Key = Slow

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" <njones@pxdy.co m> wrote:
[color=blue]
>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" <nospam@nospam. nospam> wrote in message
>news:1kb2419cj 8ionjokv3dm5qoh r9v9hlf7qt@4ax. com...[color=green]
>> 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" <njones@pxdy.co m> wrote:
>>[color=darkred]
>>>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
>>>performanc e. However, since different methodologies for obtaining data are
>>>used with and without the virtual primary key, it seems that it does
>>>affect
>>>performanc e, and certainly does in this situation.
>>>
>>>Thanks,
>>>
>>>Neil
>>>
>>>[/color]
>>[/color]
>[/color]

[Neil]

Re: Virtual Primary Key = Slow

> Well, you mileage is going to vary on the above conclusion. For a lot of[color=blue]
> 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.[/color]

Good points.
[color=blue]
> 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,[/color]

Don't use virtual PK? So you update using pass-throughs and stored
procedures, rather than through bound forms?
[color=blue]
> 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?[/color]

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.
[color=blue]
> I don't think anyone here is saying that ADP's are problem matic.[/color]

Yes, many are.
[color=blue]
> 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!
>[/color]

Indeed.

Thanks,

Neil

[Neil]

Re: Virtual Primary Key = Slow

"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" <nospam@nospam. nospam> wrote in message
news:p0t441hktq d2frjtghudhktid 9v00lekpg@4ax.c om...[color=blue]
> 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" <njones@pxdy.co m> wrote:
>[color=green]
>>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" <nospam@nospam. nospam> wrote in message
>>news:1kb2419c j8ionjokv3dm5qo hr9v9hlf7qt@4ax .com...[color=darkred]
>>> 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" <njones@pxdy.co m> 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
>>>>
>>>>
>>>[/color]
>>[/color]
>[/color]