Performance of Access App is Slow

[Christina Gonzalez]

I have an Access database that contains linked Oracle
tables from our primary system. Also, I have linked SQL
server tables. When I launch the application it is
extremely slow. My users complain that it is slow when
opening the app and closing the app. I have office Xp
developer and I am experiencing the same issues. My
users have Access Runtime. Is there any solution or
suggestions to this problem...

 

[Jim Jawn]

Christina:

I found this posting in the "multiuser" newsgroup yesterday... I did not
write a lick of this, but I found extraordinarily helpful....

From: Don Skolnik
Subject: Startup Performance Improvement with Persistently Open Recordsets

Recently I tried one of the recommendations found at
http://www.granite.ab.ca/access/performancefaq.htm to
improve the startup performance of one of my Access 2002
applications. I was pleasantly surprised by the results,
which I would like to share with others.

The application is fairly large and complex. It is split
into a front-end MDB of 300 megabytes and 7 back-end MDBs
varying between 200 and 500 megabytes each. The front-end
contains forms, reports, queries, code modules, temp
tables as needed, and links to the back-end MDBs.

The front-end MDB runs on mostly high-end Windows XP or XP
Pro clients in a LAN consisting of 15 to 20 concurrent
users. The back-end MDBs reside on a fairly high-end
Windows 2000 Server with Service Pack 3 installed.

The MDBs are compacted nightly by an automated system.

User-level security is implemented in the front-end and
back-end. Users join a central Workgroup Information File
(WIF) on the server.

The application is built around a conventional Switchboard
form that is always open. Startup code in the Switchboard
performs various functions, including the following:
Ensures that the user is joined to the proper WIF; ensures
that the user has an employee record; ensures that the
front-end clients are properly linked to the back-end
MDBs; ensures that the back-end MDBs are properly matched
to one another.

The problem: When more than one user was using the
application, the startup code took forever to execute.

Here are the time trials: Startup for the first user took
about 30 to 40 seconds. Startup for each additional user
took about 10 MINUTES!

Performance was good once the startup code had been
executed, but the startup time was a very cold wet blanket.

I recently read the advice provided by Tony Toews here and
at http://www.granite.ab.ca/access/performancefaq.htm and
decided to try maintaining a persistently-open recordset
from each of the clients to each of the back-end MDBs.

I added a table with one field and no records to each of
the 7 back-end MDBs. In the front-end MDB, I added a link
to each of these tables. I added 7 hidden subforms to the
Switchboard. Each of these subforms has as its
RecordSource a link to one of the 7 new tables. When the
7 Switchboard subforms are loaded, a persistently open
recordset is maintained from each of the clients to each
of the 7 back-end MDBs throughout a client session.

The results are impressive: Startup for the first user is
now about 15 seconds. Startup for each successive user is
now about 34 seconds, nearly 18 TIMES FASTER than before.

I wish to thank Tony Toews and Granite Consulting, plus
others who have generously shared their knowledge about
this issue.

Don Skolnik
Don Skolnik Consulting Services
San Diego, CA

 

[Albert D. Kallal]

A few things:

We can assume that each user gets their own copy of the front end, and you
don't share it on a file share.

The load time of the application should be very short. I would assume that
the application loads, and then the users get some type of menu to select
the next step. Up to this point, no data has been pulled from the Oracle
database.

If the above is slow loading, then there must be some settings in the
database you need to check. I would make sure things like track-name
autocorrect, and sub-datasheets are turned off.

Since you are using the runtime, then likely each user is getting their own
copy of the mde, and it is run on each pc (this is a good set-up so far). As
mentioned, if each workstation does not have a copy of the mde, then you
need to fix this.

That should take care of your application slow load. If some forms are slow
loading, then that is complete different description then what you have
given so far.

If some forms are slow loading, that simply means you are likely not
limiting the amount of records when you open a form. Generally, you should
open a form TO ONLY ONE RECORD, and you can use the where clause to
accomplish this. Thus, your performance will be the same with 1 record in
the database, or 1 million records. After all, Oracle, and sql-server are
not exactly slow and limited database systems.