General discussion

Locked

MS Access97 Procedure running S L O W

By mrdbcooper ·
OK Here is the situation I have two forms
one pulling from a table with scheduling
information. The other is a sub-table off
of the main database that keeps a record
of scheduling per client. These tables all
have their relationships set through various
fields (Main Table has a Unique Client# that
is related to its sub-table and the sub-table
has a number that is related to the schedule
table ID) I have the Schedule form enabled
to bring in that ID and its accompaning
information into the sub-table just fine.
But its incredibly slow even on a Pentium III
running 128mb of ram with NT 4.0 SP 5 as the
OS. If you think you might know of a fix
without the tradition answers of upgrading,
or getting a software package that does these
things let me know and I will add the coding
for this event-procedure if you need it.
The max length prevents me from giving it
right now.

Thanx

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

MS Access97 Procedure running S L O W

by shmaltz In reply to MS Access97 Procedure run ...

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.

Collapse -

MS Access97 Procedure running S L O W

by mrdbcooper In reply to MS Access97 Procedure run ...

My test use of this code was on a
Pentium II 350 with 256 mb ram on a
non-production machine and copy of the
mdb. It ran very fast as was expected.
The slow down over the network is
partially its fault yes. But unless I
am going to ask management to upgrade
the network to a gigabit network the
fast ethernet is only way out cost-wise.

Collapse -

MS Access97 Procedure running S L O W

by mrdbcooper In reply to MS Access97 Procedure run ...

My test use of this code was on a
Pentium II 350 with 256 mb ram on a
non-production machine and copy of the
mdb. It ran very fast as was expected.
The slow down over the network is
partially its fault yes. But unless I
am going to ask management to upgrade
the network to a gigabit network the
fast ethernet is only way out cost-wise.

Collapse -

MS Access97 Procedure running S L O W

by donq In reply to MS Access97 Procedure run ...

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?

Collapse -

MS Access97 Procedure running S L O W

by donq In reply to MS Access97 Procedure run ...

72,000 records and growing sounds like an excellant candidate for an upsize to SQL-Server. It painless and automated from Access to SQL if you own the software. Do you have access toa multi-processing server for your back-end?

Collapse -

MS Access97 Procedure running S L O W

by mrdbcooper In reply to MS Access97 Procedure run ...

Poster rated this answer

Collapse -

MS Access97 Procedure running S L O W

by mrdbcooper In reply to MS Access97 Procedure run ...

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

Collapse -

MS Access97 Procedure running S L O W

by shmaltz In reply to MS Access97 Procedure run ...

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.

Collapse -

MS Access97 Procedure running S L O W

by mrdbcooper In reply to MS Access97 Procedure run ...

Poster rated this answer

Collapse -

MS Access97 Procedure running S L O W

by mrdbcooper In reply to MS Access97 Procedure run ...

This question was closed by the author

Back to Community Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums