Weird Access Performance (Reproducible with Northwind)

[MBursill]

I’m having a problem with Access not using all the available
bandwidth, and transferring upwards of 8x the full database size for a
simple operation. The problem is fully reproducible using the
Northwind database.

First, our company has a database which since upgrading to A2007 has
been showing very poor performance. It runs fine when the linked
database is local. All systems are XP, and I have tried the program on
our 2003 servers as well as on a smaller test LAN made up of three XP
systems.

Refreshing the links to the database from a test client, I found it
using 2-5% of a 100Mbps link, and taking about 2-3 minutes.

Through a lot of trial and error I’ve determined that refreshing the
links is slow once any single client has a lock on the file.

I wasn’t so sure the problem was limited to just our database. I fired
up Northwind, saved a copy of the DB to a share folder. On two
different clients, I created new accdb files linking to the same
Northwind backend. If I open the orders table on one client to
establish a lock (this can be seen by the creation of the laccdb file)
and then use the second client to relink the Northwind database, it
takes about two full minutes to do the refreshing. It’s only using
about 2-5% of the network and is pulling about 24MB. That’s incredible
considering the full database is only 3MB!

Things I have tried:
- Turning off OpLocks
- Toff file caching
- Disabling virus scanning
- Disable SharingViolationDelay and SharingViolationRetries
- Different combinations of hardware (systems, switches, cables, etc.)

Is this normal? If it’s normal, why?

Thanks.

 

[Ed Metcalfe]

You might want to take a look here for starters:

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

One other thing I found when we had an OS upgrade was that they had
implemented a DFS to provide a common mapped drive letter for all users
across all sites. Each site has its own file server (located on site),
however the DFS server is in a central data centre. Changing paths to the
backend database(s) to use the UNC path to the file server made a
*massive* difference to performance.

Ed Metcalfe.

 

[Albert D. Kallal]

As suggested, check out:

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


Also, are you saying the network performs ok with a2003, but not with a2007?

The one thing and post and answer we see in the newsgroups posted daily is
to try a persistent connection...and see if that works (this is ESPECIALLY
the case, since you are hinting that it works ok with ONE user, but when
flipped into multi-user mode, it slows down...

the reason for this slow with more then one user is the LARGE amount of time
that access waits to get a lock on the back end.

So, try a persistent connection trick...and see if that helps.....

 

[Tony Toews [MVP]]

First, our company has a database which since upgrading to A2007 has
been showing very poor performance. It runs fine when the linked
database is local. All systems are XP, and I have tried the program on
our 2003 servers as well as on a smaller test LAN made up of three XP
systems.

Refreshing the links to the database from a test client, I found it
using 2-5% of a 100Mbps link, and taking about 2-3 minutes.

Through a lot of trial and error I’ve determined that refreshing the
links is slow once any single client has a lock on the file.

This was a problem in A2003 and earlier as well although it's quite
possible that A2007 is worse in this respect.

Once you've relinked the first table the open a recordset based on
that table. Then continue relinking all the other tables.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[a a r o n . k e m p f]

yes, it's normal.

no, it's not acceptable.

Access is the worst database in the world-- it is bloated-- it doesnt'
work over 80% of the networks in the real world.

It's time to upsize to SQL Server-- do the same test there!!!

SQL Server is an efficient client-server architecture.

SQL Server is 10 times more efficient (less chatty on the network)

I moved to SQL Server a decade ago-- I love Access- but JET is just
plain stupid

 

[a a r o n . k e m p f]

persistent connection would mean that he couldn't change any tables in
the back end.

what a stupid reccomendation.

What _IS_ reccomended on a daily basis-- is to move to SQL Server

 

[Jerry Whittle]

No it doesn't. I, and most other experienced Access people, can make changes
to the design of back end tables.

I think that you owe Albert an apology. Don't worry - I'm not holding my
breath for that to happen.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

persistent connection would mean that he couldn't change any tables in
the back end.

what a stupid reccomendation.

What _IS_ reccomended on a daily basis-- is to move to SQL Server

 

[aaron.kempf is a robot]

CAUTION!
It has been determined that the message below was generated
programmatically, by a program robot ("bot") which also allows manual
intervention. This robot was created by a person or persons deliberately
trying to interfere with users of the Microsoft Access database software, and
uses the name of an actual person. As it is not possible to block posting by
this "bot", we post these cautions lest you be misled into taking the posts
as authentic and serious.

persistent connection would mean that he couldn't change any tables in
the back end.

what a stupid reccomendation.

What _IS_ reccomended on a daily basis-- is to move to SQL Server

 

[aaron.kempf is a robot]

CAUTION!
It has been determined that the message below was generated
programmatically, by a program robot ("bot") which also allows manual
intervention. This robot was created by a person or persons deliberately
trying to interfere with users of the Microsoft Access database software, and
uses the name of an actual person. As it is not possible to block posting by
this "bot", we post these cautions lest you be misled into taking the posts
as authentic and serious.

 

[a a r o n . k e m p f]

so.. he has a persistent connection to the database backend.. and they
can _STILL_ change tables in it?

whatever dude