A2K under Access 2010

#11

"David-W-Fenton" <NoEmail (AT) SeeSignature (DOT) invalid> wrote

Quote:

While I would agree that a well indexed database increases
performance, and I'd also agree that archiving is the last resort
after following all the hints in Tony Toews' performance FAQ. If
extremely poor performance is still a problem and you can reduce
by half (or more) the records you must search and act upon, you
can always improve performance by archiving that many records,
performance will be improved by approximately the percentage of
the number of records that you've reduced. It doesn't make much
sense on a 5 second query done 10 times a day, or even a 1 minute
query done once per day. But it sure makes a lot of sense if a 15
second query is run 30 times a day and the archive may not be
needed more than once or twice a year (or less).

But it all depends on the trade-offs.

A month ago I restored an archive in a client's app because the ADD
NEW PERSON procedure had to check the archive before it would allow
you to add a new person (it would pull the data back in from the
archive if it already existed there), and this was REALLY SLOW (I
optimized the process as much as possible, and there was no more
speed to be wrung out of it). The trade-off was that now regular
data retrieval takes longer, because it's working with 100s of
thousands of records instead of just 10s of thousands.

Archiving must be done intelligently. Not all data gets archived or should
be. For instance, I built a homebuilders application that tracked the
purchase contract, through the building and closing of the home, and for 1
year thereafter (the warranty period). We archived the records 3 years after
closing (2 more than necessary) What was archived was the contract,
building, closing, and service records. We did not archive any of the people
involved, nor did we archive any of the subdivision or model information.
Those were set to inactive, and avoided in all searches except for new home
sales.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access
Co-author: "Access Solutions", published by Wiley

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

A2K under Access 2010

comp.databases.ms-access comp.databases.ms-access

Re: A2K under Access 2010 - 11-12-2010 , 10:26 AM