Weird Access 2003 multi-user performance problem

[habidat]

(I'm sorry this post is so long, but I wanted to be sure to mention
the sorts of things that get asked in follow-up posts, so as not to
waste anyone's time)

I have a perplexing performance problem in a multi-user Access
application that has me stumped. I have done exhaustive research on
this topic, and I believe all the usual "best practices" from the many
previous posts on this topic (especially Tony's tips at granite.ab.ca)
were already implemented and have been double-checked recently. I've
been developing and supporting multi-user Access applications for many
years, and I have never seen anything like this.

Here's the setup:

An unsecured Access 2000 MDE (split FE/BE), developed in Access 2000
and deployed as an Access 2000 MDE on each client PC; it is
decompiled, compacted and repaired prior to each release. The app
maintains a persistent connection the BE. Name autocorrect is off,
record-level locking is off, default open mode is shared. In modules
and classes, ADO is used extensively, DAO 3.6 only rarely. All ADO
connections use the CurrentProject.Connection object. The data
structure is highly normalized with approximately 65 tables all
participating in RI. Subdatasheets are off in the BE. The
application is well-indexed, and is not a heavy data entry application
(probably less than 100 records a day) - mostly users browse data,
make periodic edits and inserts, and run reports. All forms are
bound, but none are linked directly to the full table - every form
opens only after the user has requested a specific record or small set
of records.

This app is currently installed at 13 different organizations, most of
whom use XP Pro and Access 2000 or Access 2003, with the BE on a LAN
shared drive. Most sites have between 5 and 15 simultaneous users.
All sites except ONE have perfectly acceptable performance running the
app all day long, and have had for years.

The problem is that at only one client organization, the application
launches and runs with acceptable performance in a multi-user setting
for between 10 and 30 minutes, after which point the performance
becomes "unacceptable" for at least one user, and eventually for all
users, until they exit and re-launch the app. ("Fast" is seeing the
primary forms opening in 1-3 seconds, and "slow" is between 6 and 10
seconds.)

The "problem" site has 4 users with new PCs with 1 gig RAM, all
running Windows XP Pro SP2 with Office 2003 11.6566.8122 SP 2,
msjet40.dll version 4.0.8618.0. The data file is on a shared drive on
a DL 370 server with 2 gig of RAM. The data file is named in 8.3
format (QB891176) and is located just one level down the directory
tree (e.g. R\AppName\AppData.mdb). All users have R/W/D permission on
that folder. The IT staff at the client site has added the registry
key for "shared file access delay" (QB150384). We are not seeing any
data corruption, nor are we getting any locking conflicts. IT staff
has said that no other major processes are running on this server.

To check for any hidden corruption in the data file, we created a new
empty database, imported all the tables, and re-linked. The BE data
file is approximately 280 meg and is regularly compacted and
repaired. This client's data file is larger than any other
organization using the app, but not by that much.

When multiple user are running the app and one has slow performance,
the other users are not instantly affected; it may be another 10 or 15
minutes before any of the other users experience the slowdown. When
users exit Access and restart, they have acceptable performance again,
until the next slowdown occurs. We saw a user regain performance by
just exiting the MDE and staying in Access, then re-opening the MDE,
but we have not extensively tested this scenario. We have not asked
users to stay in the "slow" mode indefinitely to see if they
spontaneously regain performance.

This organization has a large physical plant with an extensive
network. In order to rule out network infrastructure as a factor, the
IT staff created a discrete LAN by moving the current server to the
office where the users are located and hooking them up to the server
directly with a switch, but we still saw the same performance drag
after 10-15 minutes.

I am completely stumped - if there is a serious problem in how my
application is designed or coded , I know I would be hearing
complaints from other organizations, but we've checked, and the other
12 sites are seeing acceptable performance, so logically, it seems
like it must be something site-specific, or a problem in my app that
is only apparent here because of some site-specific setting or
condition.

The fact that performance goes back up (temporarily) after users exit
makes me suspect some sort of resource is being used up and then
released, but how can I determine what it would be specific to this
site? Access memory usage in Task Manager starts out at about 30,000k
and is at only about 50,000k when the user experiences the slowdown,
so it doesn't seem like it is specifically Access that is using up
resources. I saw some posts in 2002 about a memory leak with Jet, but
it looked like it had been fixed in recent SPs.

I believe that every ADO and DAO object that I open is closed and set
to nothing, of course it is possible that somewhere in the many, many
lines of code I've missed a few, but wouldn't I be seeing the same
performance problem at other sites?

I don't think it is related the absolute size of the BE because when
the app is fast, it's very fast. We also transferred a scrambled
version to another client site and ran it there and performance for
two users was fine (although we couldn't really take up their time to
do extensive testing). I'm going to take it to a colleague's office
and try testing on his server (I get very good performance using their
data on my peer-to-peer setup).

We are not seeing any corruption, so I haven't asked the IT people to
mess around with OpsLocks, but maybe I should try that next?

This client is running enterprise-wide Symantec real-time virus scan -
could this be a factor? Does anyone know what sort of settings I
should be looking at or directing the IT people to look at?

I have been collecting user-specific data for 2 days (my central error
handler has been writing time in and out of every function) and I will
be receiving those files Monday for analysis, but I'm not sure what I
should be looking for.

A million thanks in advance for any advice.

- Ann

 

[storrboy]

Hello.
You haven't left too many stones unturned. I agree that if only one
site experiences this problem it's more apt to be their network than
the program.

I see that you checked Access mem usage, but did you monitor other
processes occuring during the slowdown? While waiting for a form to
open is Access using most of the CPU resource, or is the client
machine mainly idle while waiting for a server response?

The real-time scanning could very well be affecting it, although I'm
not experienced enough to know what settings to look at.

 

[John W. Vinson]

The fact that performance goes back up (temporarily) after users exit
makes me suspect some sort of resource is being used up and then
released, but how can I determine what it would be specific to this
site?

Purely speculative suggestion: can you check to see if there is bloating in
the *frontend* of the affected machines?

Perhaps this client is running more large queries, or is running queries which
create temporary tables in the frontend, and the frontend databases are
becoming bloated and inefficient. How and when do you update the user's
frontend mde file?

John W. Vinson [MVP]

 

['69 Camaro]

Hi, Ann.

It sounds like you've been very thorough in the database application's
design and in checking every possible avenue that may be affecting the one
client's setup. When something like this happens to only one client, look
for the differences in the query data sets being used, then the differences
in that client's network configuration, and then the differences its
computer confiturations. As for the queries, if Cartesian Joins are being
used, huge system resources may be required to run the queries, but not
always. If there are only a few records in the Cartesian Join, the speed
will be fast enough that the users won't notice a slowdown. However, if a
large number of records are involved in the Cartesian Join, then the users
will notice a significant slowdown, often to the point of being unusable.
Here is an example of a Cartesian Join:

SELECT FirstName, LastName, Dept.DeptName
FROM Personnel, Depts;

.. . . versus an ANSI SQL Join to produce the same data set:

SELECT FirstName, LastName, Dept.DeptName
FROM Personnel INNER JOIN Depts ON Personnel.DeptID = Depts.DeptID;

This client is running enterprise-wide Symantec real-time virus scan -
could this be a factor?
Yes.

Does anyone know what sort of settings I
should be looking at or directing the IT people to look at?

Don't allow it to scan MDB/MDA/MDE and ADP/ADE files on the network. This
can be a severe performance drag on the database file. There may be an
additional setting for Access 2007 files, which would use extensions ACCDB,
ACCDE, ACCDT, ACCDR, et cetera.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Tony Toews [MVP]]

(I'm sorry this post is so long, but I wanted to be sure to mention
the sorts of things that get asked in follow-up posts, so as not to
waste anyone's time)

Thank you. You did an excellent job of trouble shooting and a lot of
work trying to figure things out.

(especially Tony's tips at granite.ab.ca)

I just added a section on SMB Signing and some links however these
sure don't sound like your problem.

When multiple user are running the app and one has slow performance,
the other users are not instantly affected; it may be another 10 or 15
minutes before any of the other users experience the slowdown. When
users exit Access and restart, they have acceptable performance again,
until the next slowdown occurs. We saw a user regain performance by
just exiting the MDE and staying in Access, then re-opening the MDE,
but we have not extensively tested this scenario. We have not asked
users to stay in the "slow" mode indefinitely to see if they
spontaneously regain performance.

Is there a form the users are closing and reopening many, many times?
I had a situation where the systems eventually ran out of resources as
they were closing and opening the form every ten seconds. I made the
form hidden and visible as required. But this didn't slow things
down. Just chewed up resources.

This organization has a large physical plant with an extensive
network. In order to rule out network infrastructure as a factor, the
IT staff created a discrete LAN by moving the current server to the
office where the users are located and hooking them up to the server
directly with a switch, but we still saw the same performance drag
after 10-15 minutes.

What about, temporarily, hooking them up to a different server.

Also what about their printer driver? Try, just for grins, a
different default printer.

I believe that every ADO and DAO object that I open is closed and set
to nothing, of course it is possible that somewhere in the many, many
lines of code I've missed a few, but wouldn't I be seeing the same
performance problem at other sites?

Yes. Unless, of course, that site is doing something unique. But
that doesn't make a lot of sense.

I don't think it is related the absolute size of the BE because when
the app is fast, it's very fast.
Agreed.

We are not seeing any corruption, so I haven't asked the IT people to
mess around with OpsLocks, but maybe I should try that next?

No, that was not a performance fix but a corruption fix.

This client is running enterprise-wide Symantec real-time virus scan -
could this be a factor? Does anyone know what sort of settings I
should be looking at or directing the IT people to look at?

That is certainly a possibility. Are they ignoring MDB, MDE and LDW
files?

You've done an excellent job of trouble shooting and explaining what
you've all tried.

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

 

[habidat]

Thanks for all your comments Tony - my remarks follow.

I just added a section on SMB Signing and some links however these sure don't sound like your problem.

I'll take a look. I'm not ruling anything out now!

Is there a form the users are closing and reopening many, many times?
I had a situation where the systems eventually ran out of resources as
they were closing and opening the form every ten seconds. I made the
form hidden and visible as required. But this didn't slow things
down. Just chewed up resources.

The app has about 4 forms that they use frequently, and I am not
making them invisible, just opeing them up each time. I haven't seen
a resources issue yet. The main forms are all tabbed with subforms on
the tabs, but the subforms aren't loaded until the user clicks the
tab, so the form is pretty quick to load (except the first time - the
first load is always slower the first timeit opens in a session while
the pointers get set). The first-time slow down doesn;t boteh the
users -they expect it an dare used to it. But when the app "gets
slow", it takes longer to open the form than it ever did the first
time.

What about, temporarily, hooking them up to a different server.

This is actually their second server - ths first one was even slower,
overall.

Also what about their printer driver? Try, just for grins, a different default printer.

Good idea - I hadn't though of this. I'll check and see if they can
set a different default printer. Maybe even Acrobat, if they don' have
a second physical printer available.

 

[habidat]

Thanks storrboy - I have added more logging capability to the app this
client is using - I am logging each user in and out of the app
centrally, and then on each client machine I am now tracking all the
functions they go in and out of, with API ticks and the Now()
function, and on every form_open event, I am logging the following
memory stats: AvailablePageFile, AvailablePhysical ,
AvailableVirtual, MemoryLoad, TotalPageFile, TotalPhysical and
TotalVirtual. I also added a hot-key for them to press when they
experience the slowdown which grabs all the above-mentioned memory
stats and writes it to a separate log. Then they can either keep
working in slow mode or exit and restart. I think these logs will
help - it's a little much to ask the users to be continually checking
Task Manager - all they want to do it get their work done! I'll be
collecting those logs sometime tomorrow.

 

[habidat]

Purely speculative suggestion: can you check to see if there is bloating in
the *frontend* of the affected machines?

Perhaps this client is running more large queries, or is running queries which
create temporary tables in the frontend, and the frontend databases are
becoming bloated and inefficient. How and when do you update the user's
frontend mde file?

John W. Vinson [MVP]

Thank for your comments, John.

The FE does not show bloating. They update their FE any time they
want to from a shortcut on the desktop, and whenever we deliver a
revised application a shared folder. With all the testing we have
been doing, they have been running clean FEs every day.

I have expanded the logging that I am doing - I hope that I will have
enough data to track down when and where the slowdowns occur and see
if I can isolate specific processes that are contributing to the
problem.

 

[habidat]

As for the queries, if Cartesian Joins are being used, huge system resources may be required to run the queries, but not always.

Gunny Thanks - there are no cartesian joins in the DB, so I doubt that
is it. But it could be that some queries are really taxing their
resources. I have enhaced the logging in the applicaiton and I hope
to have more data tomorrow and Wed.

Don't allow it to scan MDB/MDA/MDE and ADP/ADE files on the network. This
can be a severe performance drag on the database file. There may be an
additional setting for Access 2007 files, which would use extensions ACCDB,
ACCDE, ACCDT, ACCDR, et cetera.

My contact in IT is investigating whether he can configure the real-
time virus scan to skip our local MDE and shared MDB files. I have
high hopes for this.

Thanks for your suggestions!

 

[habidat]

The problem continues - here's a bief update:

- turning off Symantec real-time virus scanning of MDB files on the
server made no difference;

- turning off Symantec real-time virus scanning of the local PCs also
made no difference (according to the users - I am looking into this
assertion using my logs but it takes a while to go through them).

- I've been logging all kids of stats - memory and page file useage,
and ISAM stas, and now I have a ton of data to wade through. I'm not
really sure what I am looking for, so right now I am just looking for
patterns.

- from what I can see so far from the logs, the slowdowns seem not to
occur inside many of my procedures, but rather between them. I'm not
sure what to make of this - either it is Access form and control
processing that is bogging down, or something totally outside of
Access. I can only log Access actions - I have no way of knowing or
tracking what else is going on.

- I have sped up the opening and loading of some app forms by using
subselects in some places instead of custom functions in queries, and
other tweaks. The users are running this version today. This will be
good in the long run, but the issue is not that the app IS too slow,
it's that it BECOMES too slow.

- when these users start my app, they are already running with a
memory load of 50% or more. This seems high - my PCs all run with a
load of about 30% when starting Access. I am going to run a
controlled experiment today with some colleagues to see what our
memory usage is like compared to these users.

What I need is a hypothesis to test - but I don't know much, if
anything, abut memory management, pages files and caching, etc.

What can cause multiple PCs sharing an Access BE database to become
slower over time? (When other PCs using the same app in other
locations o not experience the slowdown?)

Any ideas? We're ready to bring in anyone who feels like that have
some ideas of how to identify the problem.

Here's what I'm planning today:

1. Use a recipe of actions and have multiple users here in my office
simultaneously run through the steps (e.g. start at time marker 1:
open form A, at time marker 2: find record, At time marker 3: do
x,y,z, at time marker 4: go to form B, etc.) - log files will be
generated.

2. Have the users in the problem site follow the same script

3. Ask some of our other nice clients at another site to do the same
exercise.

Compare all the logs.

I'll report back.

- Ann

 

[habidat]

Problem solved!

One the IT staff at the site uninstalled the desktop AV software and
resintalled a later version. Since then, (after an inexplicable brief
delay?) all the users have been in the application for a week now with
uniformly and consistently fast (e.g. normal) performance.

For the record, the version of Symantec AV was 9.0.3.1000 (according
to the version for the "running modules" from msinfo on a user PC)
before the reinstall and now it is 10.1.5.5000. There are other
versions for other Symantec modules, but these are the main ones, as
far as I can tell, and every AV component's version number went up.

None of the changes I made to the application to improve performance
specific activities made any noticable difference whatsoever, and in
fact, they were using the same release of my app before the AV was
reinstalled and afterwards, when they finally experienced acceptable
performance.

In retropsect, I wish I had pushed a little harder to get the
environmental issues addresses first, instead of digging around so
much (and ultimately, with so little gain) in my app. However, I did
learn a great deal about Jet performance on large indexed tables (it's
fast!), and I have much more confidence in the solidity and
performance of my app. I also now have a set of nifty memory and ISAM
logging functions, in case I ever need to go through this again, so
that's good.

Thanks everyone for your help!

 

['69 Camaro]

Hi, Ann.

Problem solved!

Congratulations on your success in getting to the bottom of this! And
thanks for the follow-up. Now anyone else experiencing the same problem can
research the archives and follow your very thorough steps for the solution.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Tom Wickerath]

I also now have a set of nifty memory and ISAM

logging functions, in case I ever need to go through this again, so
that's good.

Any chance of talking you into posting these (or perhaps making a sample DB
available for download that includes these logging functions)? I'd love to
see these and experiment with some of my own applications.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________