Access Performance Problems

[Scott]

I have a customer that had developed an Access97 application to track
their business information. The application grew significantly and
they used the Upsizing Wizard to move the tables to SQL 2000. Of
course there were no modifications made to the queries and they
noticed significant performance issues. They recently upgraded the
application to Access XP expecting the newer version to provide
performance benefits and now queries take even longer. We are in the
process of recommending that they rewrite the application using
pass-through queries and triggers. In the mean time does anyone know
specific reasons why the Access XP application would be significantly
slower in queries? The main customer table that they are querying is
big. About 75K records with 122 fields. Does anyone know of an
application or a process that can monitor the queries and look for
bottlenecks? Thanks for any help.

[Albert D. Kallal]

A few things:

having a table with 75k records is quite small. Lets assume you have 12
users. With a just a 100% file base system (jet), then the performance of
that system should really have screamed.

Before Microsoft started "really" selling sql server, they rated JET could
handle easily 50 users.

I have some applications out there with 50, or 60 HIGHLY related tables.
With 5 to 10 users on a network, response time is instant. I don't think any
form load takes more then one second. Many of those 60+ tables are highly
relational..and in the 50 to 75k records range.

If the application did not perform with such small tables of only 75k
records..then upsizing to sql server will do absolute nothing to fix
performance issues. In fact, in the sql server newsgroups you see weekly
posts by people who find that upgrading to sql actually slowed things down.
I even seem some very cool numbers showing that some queries where actually
MORE EFFICIENT in terms of network use by JET then sql server.

My point here is that technology will NOT solve performance problems.
However, good designs that make careful use of limited bandwidth resources
is the key here. So, if the application was not written with good
performance in mind..then you kind are stuck with a poor design!

I mean, when using a JET file share, you grab a invoice from the 75k record
table..only the one record is transferred down the network with a file share
(and, sql server will also only transfer one record). So, at this point, you
really will NOT notice any performance difference by upgrading to sql
server. There is no magic here.

Sql server is a robust and more scalable product then is JET. And, security,
backup and host of other reasons are why you switch. However, sql server
will NOT solve a performance problem with dealing with such small tables as
75k records (and, you seem to concur that many things actually slowed
down!). Of course, when efforts are made to utilize sql server, then
significant advances in performance can be realized. But, as mentioned, you
then wind up using those advantages to try and fix something that should not
have performance poorly in the first place! The size, and data sets you are
talking about should have run without problems BEFORE sql server. So, even
as you start to optimize your application..you are starting out with
something that is not very good.

I will how ever give a few tips:

** Ask the user what they need before you load a form!
The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instantly teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When don editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server appcltions):

http://www.attcanada.net/~kallal.msn/Search/index.html

My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records don't apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Don't
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...better and faster, and less network
traffic....what more do we want!).

** Don't use quires that require more then one linked table. When you use
ODBC, one table could be on the corporate server, and the other ODBC might
be a FoxPro table link 3 computers from the left of you. As a result..JET
has a real difficult time joining these tables together..and JET can not
assume that the two tables are on the same box..and thus have the "box" join
the tables. Thus,while jet does it best..these types of joins can often be
real slow. The simple solution in these cases is to change the query to
view..and link to that. This is the least amount of work, and means the
joins occur on the server side. This also applies to combo boxes. Most
combos boxes has sql embedded in them. That sql has to be processed, and
then thrown to a linked odbc table. This is a bit sluggish. (a form can have
maybe one, or two combos..but after that ..it will start to load slow). So,
remove the sql from the combo box, build a view..and link the combo box
direct to that view (JUST USE the view name...the sort, and any sql need to
be in the view). The result is quite good combo box load performance. (and
again, not very much work. There are other approaches that can even speed
this up more..but we have to balanced the benefits VS. the amount of work
and coding. I don't think once should re-code all combo boxes to a call back
with a pass-through reocrdset..but that can be a solution also).

** Of course, if you do have sql with more then one table..then pass-though
is the best.

** You can continue to use bound forms..but as mentioned..restrict the form
to the one record you need. You can safely open up to a single invoice,a nd
even continue to use the "where" clause of the openform. Bound forms are way
less work then un-bound forms...and performance is generally just is good
anyway when done right.

You also did not mention how many users the system starts to slow down
after. I mean, does the system run ok with 1 users, or 2..and then slow down
after 15 users for example? As mentioned, if performance is slow with one
user...what are you going to do when you have 10 users (that is 10 times the
amount of resources).

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
pl******************@msn.com
http://www.attcanada.net/~kallal.msn

[Eric Ellsworth]

Hi Albert,
I've done a couple optimizations in dealing with slow Access
applications, and I've haven't been able to make Jet perform like this
with only 10K records, much less 75K. Can you point me to the resources
that show how people did this?

If the application did not perform with such small tables of only 75k
records..then upsizing to sql server will do absolute nothing to fix
performance issues. In fact, in the sql server newsgroups you see weekly
posts by people who find that upgrading to sql actually slowed things down.
I even seem some very cool numbers showing that some queries where actually
MORE EFFICIENT in terms of network use by JET then sql server. My point here is that technology will NOT solve performance problems.
The other critical reason I went through an upgrade was stability. Jet
has acknowledged problems with stability with multiple users. I believe
Microsoft does not recommend use by more than 8-10 people concurrently.
I mean, when using a JET file share, you grab a invoice from the 75k record
table..only the one record is transferred down the network with a file share
(and, sql server will also only transfer one record). So, at this point, you
really will NOT notice any performance difference by upgrading to sql
server. There is no magic here.

This is another one that I'd really like to understand better.
According to my understanding, Jet is an ISAM database engine, so an
entire table has to be read to have query any of its records. With an
Access .mdb on a file share, the engine is entirely on the client, so it
has to fetch all of the table and then run the query on the client side.
Can someone correct me where I've gone wrong?

That questions asked, let me strongly second what you've said about
combo boxes. In my experience indiscriminate use of combo boxes is one
the biggest drags on Access performance there is. And as you say,
streamlining forms so that only on record is opened at a time does
indeed improve usability - it makes sure that the user knows what
(s)he's trying to accomplish when (s)he opens a form.

Cheers,

Eric

[Albert D. Kallal]

"Eric Ellsworth" <ez@e> wrote in message
news:P6********************@speakeasy.net...

Hi Albert,
I've done a couple optimizations in dealing with slow Access
applications, and I've haven't been able to make Jet perform like this
with only 10K records, much less 75K. Can you point me to the resources
that show how people did this?

Actually, the optimizations tips I gave in the post is about all I can give
you.

I mean, a table of 10,000 records is absolute nothing for ms-access.

I sure the basic stuff like making sure a index is on the fields you search.
I mean, that is about the only real "tip" I can give you!

For example, I make comments about loading a invoice form. If you are using
network..and prompt the user for the invoice..if that invoice field is not
indexed..then you drag the whole table down the wire. If you index..then
only the one record gets sent down across the network. So, I have to assume
at the basic optimizing level people know about things called a indexes.

I mean, lets write a small piece of code to generate 75,000 records in a
blank database. Lets call the table test1. Lets keep the index autonumber
default as "ID"(let ms-access create that)..and add another field of a
long number called Counter..and index that field

Lets write out 75,000 records...

dim rstMake as dao.recordSet
dim i as long

debug.Print "running..."
set rstMake = CurrentDb.OpenRecordSet("test1")

for i = 1 to 75000
rstMake.AddNew
rstMake!Counter = i
rstupdate
next i
rstMake.Close
set rstMake = nothing
debug.Print "done"

Just type the above into a module..and from the command prompt (the debug
window..run the above code....).

Create a cute little form that is bound to the above table. Place the
autonumber field..and the Counter field on that form (use the wizard to
build this form!).

Now create a blank un-bound form..with a textbox that asks for a counter
number, or even the autonumberID field.
Then, have a button to launch our bound form to the table.

After you finish all of the above...place it on the shared drive on the
server.

That form will load instant. If you have 5, 500, 5000, 50,000 records..you
WILL NOT be able to notice the difference in load time...even if the
application is being run across a typical office network. Heck, for
fun..generate a table with 500,000 records.

(if you are using the same database..then either modify the loop code to add
more records..or empty the table..and then do a compact before you try and
run the code to add records).

I challenge you to notice the difference is load time of that form with all
of
the above record sizes. So, if the form load is instant..they why all the
complaints about performance problems? You just hit a table with 500,000
records..and the form load was absolute instant!

As mentioned,
the real key to optimizing a application is to simply limit the number of
records you work with. Open a form to ONE record.

Same goes for searching for records. I am always absolute stunned when I
see something like a combo box being used to search for a database with
50,000
records in it? I mean, really...a combo box???? A combo box is nice UI
feature..and I would NEVER throw a combo box at a user with more then
say..50..or perhaps tops 100 choices. So, good user interface means we
never HAVE to load up a combo box with huge numbers of records. I mean, good
UI designs will also as a general rule be more bandwidth friendly anyway!. I
mean, it is REALLY common sense here to not load up a combo box with a huge
number of records. I not sure I can find a book that can get the really
terrible drivers off the road...nor can I find a book that will tell me
that loading up a combo box with 1000's of records is stupid. I mean, try
a combo box with 1000's of records..and see how much of a torture it is for
the users EVEN WHEN you have sufficed performance!

So, for example...to search a database for names...read the following of
mine:

http://www.attcanada.net/~kallal.msn/Search/index.html

There is a white paper on tuning JET for performance. But the answers and
solutions in that document will NOT fix generally poor designs. I mean, ok,
you read the document and squeeze out a 30% increase in a query. Well, in
the
computing business a 30% increase in performance can be worth billions.
However, if a query, or some poor design takes 17 seconds..and you get a 30%
increase in performance..then that query now takes 13 seconds. It is very
possible that a 13 second report, or form load is still WAY TOO long. So,
take the following document with a grain of salt..but this kind of salt will
NOT fix poor designs. As I mentioned..it really is a waste of time to try
and
optimize a poor design. However, here is the document for you:

http://support.microsoft.com/default...01&Product=acc

I can't really tell you how to get good at driving car, or playing baseball.
There is some theory here..but the rest comes down to good sound designs
that limit the bandwidth. Move less data down the wire. What more does one
need to know?

I mean, I see a form with 10 tabs, and thus 10 sub-forms. Why load all those
sub-forms? Why not load the sub-forms ONLY when the tab is hit? That means
my tabbed form with 15 sub-forms loads as fast as a form with one sub-form
because I only load the sub-form WHEN the user clicks on the tab. What was
the magic suggestion here? Well, same as my original solution:

Limit the number of records you work with, make sure those records can be
retrieved by some indexed field.

I don't think anything more need be said on optimizing applications.

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
pl******************@msn.com
http://www.attcanada.net/~kallal.msn

[John Baker]

Albert D. Kallal wrote:

For example, I make comments about loading a invoice form. If you are using
network..and prompt the user for the invoice..if that invoice field is not
indexed..then you drag the whole table down the wire. If you index..then
only the one record gets sent down across the network.

The indices still need to get pulled over, don't they? I mean it's
still the client that has to do the processing. And then a small block
of records, right?

[Albert D. Kallal]

"John Baker" <ba*****@ix.netcom.com> wrote in message
news:Fs***************@fe2.columbus.rr.com...

Albert D. Kallal wrote:

For example, I make comments about loading a invoice form. If you are using network..and prompt the user for the invoice..if that invoice field is not indexed..then you drag the whole table down the wire. If you index..then
only the one record gets sent down across the network.

The indices still need to get pulled over, don't they? I mean it's
still the client that has to do the processing. And then a small block
of records, right?

You are 100% correct. There is some overhead...and those index pages are
traversed until a match is found. However, even with 1 million records...we
are talking..what 20 nodes max in the balanced be-tree? And, that many notes
is probably only a few pages of data to get sent down the wire.

It would be most un-fair of me hint, or suggest that ONLY the one record
gets set down the wire.

However, the amount of traffic is not that much. Certainly on a standard
office LAN...that table of 100,000 records is nothing..and no delay will be
perceived when loading that form to one record. If you just had the one
table..then 30 users updating that table would barely notice any performance
problems...but if you got that many users.....it will have been quite a long
time ago in my opinion that sql server should being used in place of JET.

To me using sql server in place of JET is a issue of reliability,
security...AND also that of improved performance. However, note how I put
improved performance 3rd in my list of reasons for sql server. In other
words..you can easily run a complex access system with 15 users and
performance will be first rate. However, damage to the back end file can
result in the loss of work of 15 people now....and that is the real issue.

You can always keep the ms-access as a front end to sql server anyway. If
the application was written with reduced bandwidth in mind..then conversion
to sql server is MUCH more easy when migrating the back end datafile from a
mdb to sql server.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl******************@msn.com
http://www.attcanada.net/~kallal.msn