DWUTKA at marlow.com
DWUTKA at marlow.com
Thu May 20 10:39:53 CDT 2004
Yep, Access will not 'reuse' space until it is compacted. So if you are using a massive amount of temp tables, that is going to bloat your database really fast. a temp table consisting of a few k just won't be as noticable, unless done continuously around the clock. 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 5:30 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Managing DB Bloat So, you're saying that when I import 10 megs and purge it and then import another 10 megs the result is as if I had imported 20 megs? Yikes! That would certainly explain things. I suspected that the temp tables were the culprit and was considering something like the approach you suggest. Thanks! Don -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com Sent: Wednesday, May 19, 2004 2:48 PM To: accessd at databaseadvisors.com Subject: RE: [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 -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com