Large table causes drastic network slowdown

[pemigh]

My client and I were vexed by speed issues on a database that behaved well on my laptop, but was terribly slow on their network. Also puzzling was the fact that their database speed was good with one user, but slowed down by roughly a factor of six as soon as a second user started entering data.

It turns out that the nub of the problem is very simple: One big (critical) table. And with the temporary remedy of moving older records to an archive table, the problem disappears.

Have you seen this problem? Is there a better solution than offloading records in the big table?

More info:

Once I had narrowed down the problem to a single big table, I set up a test database and an awkward query that took a couple of seconds to run. I was able to demonstrate that there was a tipping point for the table size. Before reaching that point multiple users caused no significant speed difference. After growing past a certain point, however, one user could get good performance, but a second user hitting the table with a query would immediately cause the 6x or so speed penalty for the original user.

In my client's table, the problem appeared when the table grew to roughly 110,000 records, taking up about 18-20 MB. The amount of data in the table is the significant issue rather than the number of records or the number of fields: If I remove data from most of the fields, I can have more records before the problem appears.

I have been able to replicate this behavior with several different versions of Access, including both ACCDBs and MDBs, and on several different server setups ranging from legit to a glorified workstation "server."

Surely, given that this problem is easy to replicate, this issue has been previously described, if not solved. I've tried a variety of search terms and looked through dozens and dozens of articles, and have not found any reference to this specific problem. Please do provide links if you know of articles here, in the Microsoft Knowledge Base, or elsewhere that directly address this problem.

One unintended *good* consequence of all the aggravation caused by this problem: Working desperately to gain as much speed as possible in the face of the overall performance drag, I greatly expanded my expertise in building quick queries and utilizing temporary tables to increase speed. My other clients have benefited quite a lot from that.

Looking forward to reading your insights,

pemigh

[David Hare-Scott]

pemigh wrote:
> My client and I were vexed by speed issues on a database that behaved
> well on my laptop, but was terribly slow on their network. Also
> puzzling was the fact that their database speed was good with one
> user, but slowed down by roughly a factor of six as soon as a second
> user started entering data.
>
> It turns out that the nub of the problem is very simple: One big
> (critical) table. And with the temporary remedy of moving older
> records to an archive table, the problem disappears.
>
> Have you seen this problem?

No

Is there a better solution than
> offloading records in the big table?
>

Possibly

> More info:
>
> Once I had narrowed down the problem to a single big table, I set up
> a test database and an awkward query that took a couple of seconds to
> run. I was able to demonstrate that there was a tipping point for the
> table size. Before reaching that point multiple users caused no
> significant speed difference. After growing past a certain point,
> however, one user could get good performance, but a second user
> hitting the table with a query would immediately cause the 6x or so
> speed penalty for the original user.
>
> In my client's table, the problem appeared when the table grew to
> roughly 110,000 records, taking up about 18-20 MB. The amount of data
> in the table is the significant issue rather than the number of
> records or the number of fields: If I remove data from most of the
> fields, I can have more records before the problem appears.
>

This sounds like you are pulling too much data over the wire.

> I have been able to replicate this behavior with several different
> versions of Access, including both ACCDBs and MDBs, and on several
> different server setups ranging from legit to a glorified workstation
> "server."
>
> Surely, given that this problem is easy to replicate, this issue has
> been previously described, if not solved. I've tried a variety of
> search terms and looked through dozens and dozens of articles, and
> have not found any reference to this specific problem. Please do
> provide links if you know of articles here, in the Microsoft
> Knowledge Base, or elsewhere that directly address this problem.
>

I don't know how easy it is to replicate, I have not seen it and I did
Access full time starting with ver2 and spent much time with other
developers during that time.

> One unintended *good* consequence of all the aggravation caused by
> this problem: Working desperately to gain as much speed as possible
> in the face of the overall performance drag, I greatly expanded my
> expertise in building quick queries and utilizing temporary tables to
> increase speed. My other clients have benefited quite a lot from
> that.
>
> Looking forward to reading your insights,
>
> pemigh

I haven't seen your exact situation but every time I have seen really bad
performance with Access it was due to careless design, essentially assuming
that techniques that work with one user on 1000 records will be OK with 20
users on 100,000 records. As you have no doubt discovered there are many
ways to reduce network traffic by limiting both rows and columns that are
presented to the user to only the data they need to see right now, so I am
not going re-visit all that. I can't see any way to find out what is going
other than a fresh pair of eyes looking at what you are doing.

David

[Albert D. Kallal]

"pemigh" wrote in message
news:b76d891e-9982-4407...@googlegroups.com...

>It turns out that the nub of the problem is very simple: One big (critical)
>table. And with the temporary remedy of moving older records to an archive
>table, the problem disappears.
>
>Have you seen this problem? Is there a better solution than offloading
>records in the big table?

No, actually I have not. You are saying that EVEN with this large table, the
speed is GOOD with one user.

>More info:

>
>but a second user hitting the table with a query would immediately cause
>the 6x or so speed penalty for the original user.

Have you ensured you ALWAYS have a persistent connection?

>Surely, given that this problem is easy to replicate, this issue has been
>previously described, if not solved.

Since Access 2000 came out (last 13 years), it is been VERY common to note
that a "persistent" connection does restore speed BACK TO single user speed
over that same network.

>Please do provide links if you know of articles here, in the Microsoft
>Knowledge Base, or elsewhere that directly address this problem.

>Looking forward to reading your insights,
>
>pemigh

The best list on performance issues to check is here:


http://www.granite.ab.ca/access/performancefaq.htm

I would first check/try the persistent trick. The KEY concept here is that
this runs ok with one user, but is slow with two. In BOTH cases you talking
about a network setup that runs well with one user and 9 out of 10 times
this "speed" issue is fixed by the persistent connection trick. As noted,
this is very common solution that you should have come across in regards to
this slow down.

It possible you left out you tried this #1 suggestion but it would be the
first thing to try.

The VERY common factor here is that

one user = fast

Next user (2 or more) = slow.

This persistent connection may or may not fix your problem, but it you
leaving this out of your post suggests that somehow you missed trying this
during your testing you done so far.

I would also consider turning off row locking - this can cause a real slow
down in some cases also.

Best regards,
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
PleaseNoS...@msn.com

[pemigh]

Further clarification:

Speed is good with one user, and good when one user has opened multiple instances of the database. The slowdown happens after a different user opens the database and taps the table with a query.

Persistent connection is only an issue for split databases, right? It is a non-issue in my test database since it is a simple, single-file database. (And I do have that persistent connection in my genuine application.)

Row-level locking is off. For the record, I spotted the "open databases by using record-level locking" setting (ver 2007), and determined that switching this setting made no difference.

I've been through several lists including http://www.granite.ab.ca/access/performancefaq.htm and haven't found anything that applies. I am very open to the possibility that I missed something, but it seems that the very specific nature of this problem would have been addressed explicitly.

Grateful for any insights,

pemigh

[Albert D. Kallal]

"pemigh" wrote in message
news:9407e365-c04e-46d3...@googlegroups.com...

>Speed is good with one user, and good when one user has opened multiple
>instances of the database. The slowdown happens after a different user
>opens the database and taps the table with a query.

Is this query a select query, or some kind of update query?

>Persistent connection is only an issue for split databases, right? It is a
>non-issue in my test database since it is a simple, single-file database.
>(And I do have that persistent connection in my genuine application.)

The above is safe to say. However due to network security settings, there is
still the possibility of additional delays being introduced due to the OS
messing with file rights.

And it is still possible that the second user is opening the database with
different settings. I mean for sure they not using the same network settings
since it is a different computer. Even a identical computer could have a
different network card driver and thus you see different performance arise
even from two identical hardware boxes but at different driver or even
office service pack levels.

>Row-level locking is off. For the record, I spotted the "open databases by
>using record-level locking" setting (ver 2007), and determined that
>switching this setting made no difference.

It certainly should not make a difference for a select query.

However for update types of queries (not append) this row setting can make
rather significant differences in both performance and also back end file
growth (bloat). So depending on what you are doing, then yes this setting
can become VERY significant.

>I've been through several lists including
>http://www.granite.ab.ca/access/performancefaq.htm and haven't found
>anything that applies. I am very open to the possibility that I missed
> >something, but it seems that the very specific nature of this problem
>would have been addressed explicitly.
>
>Grateful for any insights,
>
>pemigh

As long as the basic things have been covered, then I think you down to
things like the particular computer, the particular network and perhaps even
something like a different work station running on a different service pack
level of the OS or even of ACE or Office.

And it is simply possible that after a certain size, then a slowdown will
occur with the given configuration.

I would still check + ensure that row locking and basic settings (and
service packs) are the same on all machines. Since one user opening that
file with row locking will cause all editing records in a frame to EXPANDED
to the full size of the frame. This is the significant reason why row lock
setting can cause performance issues. So you can have two users open a
database with different settings and in this case EACH user will be
operating in a different mode! While both will be forced to use the file in
multi-user mode, they can both open with different row locking settings and
this does work and is allowed.

As noted, I not seen any signification difference in performance when one or
two users have the database open for select queues.

However, if the slow down is due to a updating records? Then this would
certainly change things here.

Remember that the windows file system has two modes and the SWITCHING to
multi-user mode and the op-locks setting has to be changed on that file
(this is done at the OS level). In other words the file mode IS and DOES
CHANGE for multi-user file manipulation as compared to single user mode.

When in single user mode then additional speed enhancements such as disk
caching etc. can be used. With multiple users then "less tricks" such as
disk caching cannot fully be used since OTHER users may now need to see the
file updates. So the file system DOES work different when in multi-user mode
and there are known slow downs.

For the most part this "switching" to a slower mode should suggest thus some
effects. However, those effects should be the same for all users.

In other words, all users are hitting the same server and shared folder (and
the folder not "local" on one machine that also running Access an that is
the one that performs well - as that would be a useless test).

So feel free to follow up on the above questions:

All users same hard ware/same service pack levels

All users are hitting the same folder on some server - that server folder is
shared the same to all users with the SAME permissions.

>but a second user hitting the table with a query would immediately cause
>the 6x or so speed penalty for the original user.

I assume all users thus slow down at this point?

Like I say, I not really seen this type of slow down documented much
anywhere, but you certainly could try a few more tests to ensure that all
users have the same permissions and are all hitting that shared folder the
same way.

It even possible that how your computer + laptop is given temporality
permissions to work with the client and use of that "shared folder" is the
reason for your additional performance.

So testing should be limited to a consistent setup to ensure that some
computers don't have some "advantage" such as the folder being shared + run
from the same computer as compared to others having to hit the file though a
mapped drive or some kind of different folder sharing or even path.

[Patrick Finucane]

Here's a possible optiom to check out. Make a backup first, Create a new db. Call it BigTable. Import the big table to it. Remove the big table from it's current db. Link the table in the new db.

[pemigh]

Albert, my test query is a select query, and I've seen the same issue on three very different networks.

I will run this by the network guy, since most of this is over my head.

pemigh