[AccessD] notes on mdb bloat

Hi Seth:

Just a personal observation.

When working with some of the older databases, like FoxBASE, the programmer
could decide to append a record or recall a record when adding it to the
data file. The append was always faster and recall was always slower because
it had to find a deleted record position in the data file and then insert
it. This process worked great if the data file was configured with
fixed-length records. In fact the recall method was not allowed on a data
file that had been created to allow variable length records.

My understanding of the MDB files are that:
1. They are designed as variable length record files.
2. All the objects are stuffed in the MDB blob, first come first serve.
3. When storing records/objects the system first attempts to 'recall' a
existing 'empty' same or larger space or gives up and appends the
record/object to the end of the file, what ever is faster.
4. There is no definitive file rebuilding routine even though the compact
process is supposed to do that. The compact process just removes the spaces
where a complete object has been deleted and is still completely intact.
There is still little spaces left where smaller records/objects were stuffed
into holes left when larger objected were modified and shuffled to other
positions or simply deleted. These small cushion spaces between objects are
left and not tracked. Eventually they build up in the MDB and we have a
'Bloated' file.

This is simply my theory but it makes sense when relating these issues to
Access's predecessors.

Jim

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Seth Galitzer
Sent: Wednesday, March 12, 2003 9:12 AM
To: accessd
Subject: [AccessD] notes on mdb bloat


Greetings,

Just wanted to share an observation with y'all.  We have one app which
needs both A2K and A97 versions.  It's not a big deal.   It just means
that every time we make any changes to the FE, wee need to back-port it
to A97 using the built-in tool.  Before I do this, I always run a
compact-and-repair first.  Having done this several times now, I have
noticed two things.

1) The conversion process seems to take an inordinately long time.  I
haven't timed it, but it seems to take at least 5 minutes, if not
longer.  This is not a large or complex app.  It's a bit sloppy (I
inherited it from a previous, inexperienced Access user), but I've fixed
a lot of that.  It's not a big deal, I just start it and then check on
it until it's done.  Not a big deal, but I'm wondering what the hangup
is.

2) The A2K FE mdb is currently right around 2MB.  The A97 FE mdb is just
under 600KB.  What the heck does A2K require in the file that adds that
much overhead?  Like I said, this is not a terribly complex or large
app.  This is also not a big deal, it just takes longer to download
updates to the users' computers.

I'm not really looking for answers here, just wanted to share what I've
observed.  If anyone can corroborate this experience and has some ideas,
I'd be interested from an academic standpoint to see what you think.

Share and enjoy!

Seth


--
Seth Galitzer			sgsax at ksu.edu
Computing Specialist		http://puma.agron.ksu.edu/~sgsax
Dept. of Plant Pathology
Kansas State University

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com