"How come I have to
compact/repair the db when it's been used a lot but I don't have to do the
same thing for an Excel spreadsheet?"
I suspect the reason is, among other issues, that access relates data but
is
there a more substantive explanation?
For one thing, a spreadsheet is a single document, and when you load the
spreadsheet, the WHOLE FILE is loaded into memory. And, when you save the
spreadsheet, you are SAVING THE WHOLE spreadsheet. So, if you delete some
data, it is your saving that actually does a compact and repair in Excel. In
other words, each time you load Excel, and save it, you are in fact making
complete new copy of the spreadsheet, and any deletions of data will be
reflected in this new data file.
Contrast the above to a database system where 100,000 records is not that
much. In a database, if we retrieve ONE record, the WHOLE FILE is NOT loaded
into memory. This is done for reasons of performance (I mean, to edit one
record, why load a 100,000 records?..that is just dumb. By the way, Excel
ALWAYS loads ALL rows of the sheet...you can't just pluck one row out).
Further, you can't have one user load the whole file into memory, as then it
would not be multi-user. (ms-success is designed to mutil-user).
So, in the case of ms-access, when you edit that one record, if after
editing it can't fit in the SAME spot on the file on disk drive, then it is
written out to a new spot on the drive (hence the file size increases). We
can't re-claim that hole, since this means two things
1) if the file is 100's of megs in size, the we would have to move 100's
of megs of data simply to "fill" in that small hole that was just created.
It is goes without question that you would rather write out the record to a
new spot on the disk drive, then try to fill, or expand that WHOLE FILE JUST
to edit ONE record. Thus, for reasons of performance, we don't load the
WHOLE FILE into memory, but ONLY the one record we want to edit. When done,
we also don't WRITE OUT THE WHOLE file each time we edit a record. We simply
pull in the ONE records...edit it...and write it back out. This process over
time means that all of the old "holes" created need to be eliminated with a
compact and repair.
2) If we did in fact try to remove that small hole created by the record
EACH TIME, then not only would we be moving huge amounts data as we try and
move each record down to fill in this hole, but with multiple users editing
the file at the same time, you CAN NOT MOVE the location of the data with
more then one user!! This is also why you can't compact and repair when MORE
THEN ONE user is in the data file at the same time.
So, the main difference here is that a database is designed to work with
LARGE AMOUNTS of records, and thus ONLY ONE RECORD at a time is loaded. This
means that to load one record out of 100,000 in ms-access is instant, and in
with excel sheets that have 100,000 rows, you MUST ALWAYS load the WHOLE
sheet into memory. As mentioned, after editing the one record, ms-access
WILL OFTEN write out that record to a new spot on disk -- especially if you
add a few characters to the record as then that record can NOT FIT in its
current location on the disk drive. The record now can't fit in the hole
where it used to be. So, it is written out to a new spot..and the file
expands in size.
With excel, you CAN NOT load one row of a sheet, but ALWAYS load the whole
document (file) into memory. It needs pointing out that a compact and
repair reads the WHOLE file into memory (it may not do this all at
once..but, by the time the compact and repair is done, all of the data did
get copied..and did make a trip to memory. So, compacting simply copies all
of the data, but leaves the holes behind.
So, in effect, when you save a Excel sheet, a whole copy of the file is
made (with holes remove), and the same thing occurs with you compact/repair
a ms-access file..the whole file is copied.
However, during regular use of the file, we can't afford that huge amount of
time to copy/save the whole file, so, for performance reasons, database
systems ONLY load pieces of the file. The "copy" of the file to remove
those holes is not done while the file is in use like it is with Excel.
