Working out the size of objects

<< Click to Display Table of Contents >>

Navigation:  Design and Tables  >

Working out the size of objects

If a database is getting large, you will start to wonder about what is taking up all the space. Sure, you can look at the tables, find the one with the most records, and then see if you can reduce its size. But this is a hit-or-miss approach, and you'll miss many good opportunities to decrease your database size. If you think that missing these opportunities is not all that important, ponder this disaster.

User Story

One database I was called in to fix was 100MB large and was performing very poorly. Naturally, I assumed that no one had ever compacted it. Unfortunately, it was in such a mess, it wouldn't compact. After importing all the objects into another database, I managed to compact the database and save 20MB. I then hunted through the system and found a few table changes that saved another 5MB. Next, I tried for another favorite space saver of mine: embedded graphs that have too many rows of data stored directly in the graph object. This repair saved a couple more megabytes. Finally, I came across an innocuous small company logo in the corner of every one of the 100 reports. I took a copy of the database and removed this picture from 20 reports. Bingo—10MB saved. I then discovered that the logo was actually a large picture that had been shrunk to a small size. I asked for permission from the manager to remove the logo from the reports, and the database shrunk to only 20MB. I then split the database, and the front-end database reduced to only 4MB. If I had known the relative size of all the objects in the first place, however, this process would have been so much easier.

It's at this point where text backups are so useful because the size of the text files for both the tables and the objects provides a relative indicator of the size of the objects inside the database. Why don't you try the two links below and see what I mean

The other easy win for both performance and speed is looking at table indexes.  Whilst this may only recover a few percentage points in the database, it will make things run faster if you select your indexes wisely and remove the junk indexes that you will find discussed in this article


Articles linked above

Exporting and Recovering Programming Objects
Exportiing all tables to text
Access Traps for the Naïve Developer