[AccessD] Managing DB Bloat

Well, from what you describe, your main bloat factor are those temp tables.
When you delete data within a database, the space is not reused.  So if you
import 10 megs, then work with it, then delete it, and import another 10
megs, the first 10 megs is still there.

Another issue of bloat is modifying data.  It can have a similar affect to
deleting data, but probably not as bad.

The first thing I would look into doing, to make an immediate change to the
size increase, is to not put the temp tables in the 'live' database.
Instead, put the temp table in a blank .mdb.  You can link the temp table to
the blank db, and then when you're done with the data in that table, delete
the .mdb, and copy a 'new' blank version into it's place.  If the 'live' db
isn't using it, it should care if the 'temp' database is replaced.  

You could also get away with not even linking the 'temp' database, by just
writing you're queries to use tables in another db (part of the FROM
statement).

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mcgillivray,
Donald [ITS]
Sent: Wednesday, May 19, 2004 4:06 PM
To: AccessD
Subject: [AccessD] Managing DB Bloat


Hello, All

I'm working on a system that monitors a set of folders for the
appearance of text files matching specified profiles.  When files are
detected, they are moved via FTP to the db's environment and ingested -
either the entire file or just the first record, depending on the type
of file - into a temp table in the db.  Once the data is in the temp
table, it is processed by performing a variety of summary queries and
appending the results to a series of permanent tables.  The temp table
is then purged and the cycle is repeated.

It had been my intention to leave this db running round the clock
capturing and summarizing data, but I was alarmed after allowing it to
run all night to find that the db had bloated to about a gigabyte by
this morning.  After halting the capture process, I compacted the db and
its size came down to about 55 meg.

I'm not real hip to all the factors that contribute to bloat, so I'm
interested to learn which practices exacerbate the problem and which
ones mitigate it.  I'd also be interested in techniques to get rid of it
via automation once it appears in my db.

Running Access XP on Win2k.

Thanks!

Don McGillivray
Sprint Mailing Services
Rancho Cordova, CA
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com