MDB unnecessarily big?

[PerFnurt]

Hiya,

Ive got an Access2000 MDB file that's grown quite a lot and Ive got the feeling its bigger than it has to.

I've located the table requiring almost all space, some ~49 MB. It has some 6700 rows and has fields of types Text, Number, Date/Time and Memo.

When I export it to an excel sheet the .xsl is ~23MB (ie less than half the size!)

When I export it to another, otherwise empty MDB, the new MDB is rougly ~49MB.

The questions are:
1)
What is causing the MDB to be so much bigger?
Is it the Memo datatype (isn't it clever enough to just allocate the required amount of space)?
2)
Will the bloating (if it is such a thing) of the MDB affect performance or is this just a normal situation?

(It's the DB for an OpenWiki,

http://www.openwiki.com,

application. The table in question is 'openwiki_revisions')

Thank you

/Per

_{"It was a work of art, flawless, sublime. A triumph equaled only by its monumental failure."}

 

[KenReay]

Have you compacted/repaired the database (see tools menu)

In Access when data is deleted (and sometime when it is updated), the area wheer the 'old' data was is no longer used, but it does not get reused by inserted data, it just lies there as "dead" space until you do a compact

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PerFnurt]

Yes I have. ~49MB is after compacting.

/Per

_{"It was a work of art, flawless, sublime. A triumph equaled only by its monumental failure."}

 

[KenReay]

Hi

OK so when compacted it is 49MB, and if you create a new Database and import all objects from the original, it is 49Mb, so that is how much space is required. Is this a FrontEnd/BackEnd set up or do you haev forms, queries etc all in same mdb, if yes they of course take space

Also you are hardly making a fair comparison by comparing to Excel sheet. Access also stores indices and relationships which will require some space, Excel has no such overheads (but cannot enforce refrential integrity do indexed searches etc)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PerFnurt]

Thanks for your reply.

>if you create a new Database and import all objects from the original

No front end/forms/report/queries stuff. No field of the table is indexed, and it has no explicitly defined relationships.

I just exported the table in question - nothing else.

>Also you are hardly making a fair comparison by comparing to Excel sheet

Well, I didn't expect a perfect 1 to 1 relationship for Excel vs. Access, but I was somewhat suprised to see such a big difference where the meta data (or whatever) is infact larger than the table's contents.

~23MB for 6700 rows gives ~3.5kb data per row, which is reasonable as the table holds contents of webpages - a lot of text (in a MEMO field).





/Per

_{"It was a work of art, flawless, sublime. A triumph equaled only by its monumental failure."}

 

[KenReay]

Hi

Memo impose an immediate overhead, since in teh table what is stored is a pointer, to the actual memo date, so there is a (I think) 10 byte per row overhead.

You mention contents of web pages, do they include images?, images are often responsible for causing "bloat" in mdb's, usual technique is to store them externally as files and save the path to the file in the mdb

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[kkitt]

You may also want to look at the datatypes for wasted space. If importing a text field it may be defaulting to say 50 characters, if all that it is to hold per say is that State "TX", then 48 bytes are wasted times 6700 records is 321,600 bytes. Now if this is the same for other columns this can add up quickly.

For the memo field, is there enough data the it needs to be defined as such? more than 255 characters?

 

[PerFnurt]

KenReay: Images are stored externally. The table in question only holds text.

kkitt:
>You may also want to look at the datatypes for wasted space

Hmmmm...except for the memo there are 4 text(128) fields and 1 text(255) field (and some number & date/time fields) in the table. The texts are a bit wasteful, they dont need to be that big really. That'd give a worst case waste of some ~5MB. That leaves a bloating of some ~21MB I still haven't figured out.

And yes the memo may be >255 chars. The contents of the memo is the editable text of the wiki. Like if you go to

http://www.openwiki.com

and double click on the page. (its not THAT wiki but another 'private' one).

/Per

_{"It was a work of art, flawless, sublime. A triumph equaled only by its monumental failure."}

 

[Ed2020]

One thing to bear in mind is that some of your database space will be field indexes - these won't form part of the exported Excel file, so I would expect the Excel file to be a fair bit smaller than the source database.

50MB is not at all big for an Access database. Don't worry about it.....

Ed Metcalfe.

Please do not feed the trolls.....

 

[KenReay]

Hi

This will not account for 21MB, but there is clearly various overhead associated with an mdb file. So for example if you create an empty mdb (no tables or anything else), it is 96KB in size, this is presumably System Tables etc. As soon as you create a table structure (even with no data) this will grow since each table has an entry in a System Table, and each column within the table also has an entry in a system table.

It is my understanding that each text column, occupies only as much space as it needs that is just because a column is defined as text 255, if it only has 50 characters of data in it, then it occupies only 50 characters, ie each record is variable length. This of course implies a further overhead since it means that each column of data must also carry additional data to mark its size.

None of this I suspect comes near explaining your 21Mb of "wasted" space


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PerFnurt]

Ok, thanks for all input.

I'll just file the situation under "normal" and focus my worries elsewhere...

/Per

_{"It was a work of art, flawless, sublime. A triumph equaled only by its monumental failure."}