Help Access 2003 Performance Improvement

[Henry Smith]

Using AC2003 with Win XP.
Have a AC DB with a typical import of 30000 financial records each month.
Have developed a routine/query to extract data to produce a Profit and Loss
report. Because of the large number of records, and maybe the process I am
using, the generation of the report takes too long (10 to 60 seconds).
Looking for performance improvement ideas and techniques for processes that
perform a lot of data gathering and mathematics. Please send ideas and URLs
to any place that has performance improvement ideas and example code.
Thank you for any assistance you can provide.
Cheers,
Henry

 

[G. Vaught]

First of all check to see if it is memory on the machine you are using. XP
really needs 256 of Ram to operate at a decent speed. More is better. If you
only have 128, then you need to upgrade. What is the speed of your machine?
Mhz? GHz?

The time factor for that many records really doesn't seem out of place. The
60 second could also be contributed to any programs that may be running in
the background, programs open other than Access, or network traffic. 10
seconds is not out of place, in fact it is pretty good.

You mention routine/query, I am assuming that code is used that calls a
particular query. Does the query have too many indexes? Too many indexes
will slow down a query. Bad coding will slow down a process.

 

[Albert D. Kallal]

The JET engine that is used with ms-access is quite fast.

The trick is getting the most out of the system.

For example, how long would it take ms-access to process 125,000 invoice
detail records against 2500 inventory items?

In other words, I want ms-access to go through all records, and take all
inventory additions (from those 2500 inventory addition records), add up
EACH individual stock (product number) to a table of 500 products. I then
want ms-access to go through all 125,000 invoice detail records, and look
for each product, and then subtract the amount from for EACH stock item
sold.

The end result of adding up those records,a nd matching them to all the
125,000 invoice records will result in a quantity on hand.

The reason why we do all this huge amount of adding up, and subtracting is
that I do NOT store the actually quantity on hand for this inventory system,
but calculate everything on the fly! This makes it very easy to build a
application, as then I can add records, delete records, and build all kinds
of cool invoice forms..but NEVER have to write a bunch code to update some
quantity on hand value. Once I build something that gives me a total of
quantity on hand by processing the existing data..then I never have to store
this value...I just calculate when I need, and thus am free add, or delete
records, and not worry about updating values.

Well, ms-access can process all of the above information in WELL under one
second!!! In other words, processing 125,000 records against a bunch more
inventory records that are now matched again more parts numbers can be done
in less then one second.

I have a working example with those 125,000 records you can download an try
here (the file is 2 megs in size).

http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html

So, a few quick tips:

1) Opening a reocrdset, or executing sql is like starting up a helicopter.

If you think about the time it takes to start a helicopter to fly on to the
roof of your house..it is takes a VERY very LONG TIME. All kinds check
lists, buttons and things have to be setup and then the motor starts to
turn. The motor startup time is VERY long. However, once you got everything
going, then the time for the helicopter to fly to the top of the house is
very small. The same applies to opening a dataset in ms-access. The setup
and OPEN time is huge. Hence, if you can avoid any opening of your data..you
make huge gains.

So, if you can grab a whole bunch of records when you open a reordset...you
save time. Opening, and setting up a reocrdset takes about the same time it
takes to grab about 10,000 or more records. Hence, running a loop in which
sql statements or openreocrdsets are done really tend to run slow. If you
can re-design the loop to grab all the records, or at least eliminate the
need to open a reocrdset inside a processing loop, then you get huge gains.

2) if you don't need a sort order on a table..then don't sort it.

There is also some base setup tips you need to check here: (especially the
one about a persistent connecting...you REALLY want to check that suggestion
out here.

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

 

[Henry Smith]

Thank you!
Your information and links were a big help. I have a little bit of work to
do to improve my performance based on the tips I have read.