MS Access97 Procedure running S L O W

Dont know of any fix. But if its over a network I whould start by copying all the databases to the local computer and test it if the speed didnt improve much then the problem is what you say. But if it did improve then something is wrong with your netwrok. Try upgrading your network.

Your equipment sounds fine - and I "ASSUME" (big assumption) your data is normalized.

I often find, after normalizing my data warehouse, it is necessary to construct a seperate "look-up" table to store relationship values that cascade through a number of relationships to find the "link".

Also everything I build is based on a query that constructs the specific data I need based on query specified relationships that eliminate a lot of unnecessary iterations by using filters, etc.

Your explanation does not address how many relationships, and records, each search must iterate through to produce what you need so I really can't help you other tan to say I would look for data normalization problems first; and see if they can't be worked around via a query that might serve as your record source?

Yes you are correct my data is normalized. Now about the data. We are
looking at about 72000 records in the
main customer base with an average of
1.6 entires per person in the related
subtable. And yet another 310 entries
in the table that my code 'pops' into
that subtable. I have thought of doing
yet another seperate lookup table but
that is an area of last resort as I
do not desire any more relational
objects either in or linked to my
master container. I have also thought
of coding it all in but that is tedious
to amend in anyway for all of those
possible entries. I am under the belief
that the problem lies in the actual
Jet of MSAccess97. I have the customer
database on non-optimistic locking so
as to not have anyonealtering a record
while someone else is in it. And with
the 97 version of Access (not so in
the 2000 edition if you set it up right) jet places a copy of the mdb on
the local drive. When a change occurs
in that local copy it sends it to the
file server copy. It in turn has to
validate the changes with all other
copies opened so as not to violate the
locking. This is probably what takes it
so long rather than in only one copy
being open. So If that makes any sense
whatsoever perhaps you can think of a way around it or my thought just go
to Access2000 as a client and SQL2000
as the server.
Thanks

If you say its over a network and thats where its slow, then try this.
Make sure the server its on, is set to maximize thruput for file sharing. Make sure its not doing any thing other than file sahring (like being a DC/WINS/DHCP server).
Also youdid not mention how many machines you have on your network. In me experience the sullotion was not to upgrade to a gignetwork but rather the following. If you use netbeiu upgrade to TCP/IP, and try braking up the network into segments.
Upgrade to switches rather than hubs. Make sure all machines are set to use full duplex 100mbs. Make sure the wiring is done and tested correctly.
Also if its possible to put the database into a SQL server instead of Access (assuming you have wrintin your application in such a way you can easilly modify the location of the database).
Also make sure, when you look up a recordset anywhere in your program it only looks up the minimum (snapshot when possible upgradable only when necessery). For example in anorder form where the database pulls automaticlly up all the info for that customer (first last name address tel...) there is no need to use a query - to fetch that record - that is upgradeable, instead give the option to edit the customer info thru a button and only when the button is pressed does the user have the option to edit the record (by calling a new query thats upgradeable) and make sure the query only returns the record that has to be updated.

This question was closed by the author