Empty fields make MDB grow HUGE--tips needed

[BoulderRidge]

My original MDB was 220 MB with 1 table. Table had 26 fields: 1 DATE, 2 LONG, rest CHAR of varying size.
New MDB with 1 table is 1.9 GB!!! Table has 155 fields: 24 DATE, 15 LONG, 9 SINGLE, rest CHAR of varying size (sum of all char sizes = 1307)
All of the "extra" fields introduced in the new table are currently empty--only data is in fields that were present in the original data. Both tables have 450,000 records. What is causing the bloat??

My process was:
Create new empty MDB.
LINK in the original 220 MB table.
Turn off AutoIndex.
Import empty target table and append query.
Run append query that copies data from my original linked table into my new target table with all the empty fields.
Now my new MDB is 1.9 GB and compacting does not shrink it at all.

What can I do (field properties, etc???) to reduce the size of my new table/MDB? I need all the new fields for a standard layout for future data.

Note: the new table was originally created from a script that didn't specify anything but fieldname and data type, so it would have taken all the default properties. Excerpt below:
CREATE TABLE Consultations (
MY_VERSION CHAR(255),
MY_DEFINED_RID CHAR(35),
SPELL_ID LONG,
GENERATED_RECORD_ID LONG,
MY_RECORD_TYPE CHAR(3),
REASON_ACCESS_PROVIDED CHAR(20),
PSEUDONYMISATION_STATUS LONG,
LOCAL_PATIENT_ID CHAR(10),
etc.....

Thanks for any help!!

--BoulderRidge B-)

 

[genomon]

Have you tried this?

http://www.granite.ab.ca/access/decompile.htm

Beir bua agus beannacht!

 

[BoulderRidge]

Thanks for the reply genomon. Haven't tried decompile on this scenario but what I need is to see if I can prevent some of the bloat, not remove it later.
More details:
This is not an app, this is a manual data processing task.
The MDB I'm using contains only 3 objects: the linked source table, the append query and the target table which starts empty.
My first dataset grew the MDB to 1.9 GB but the append completed OK. I exported the new contents of the target table to a delimited text file which was 172 MB. My 2nd dataset is bigger (starting from a new MDB shell) and fails on the 2 GB limit before completing. If I can't reduce the bloat somewhat I will have to start breaking apart my source files....sigh.

I have tried the following changes:
Add Autonumber field to target table
No indexes or subdatasheet on target table
Isolate source table in new clean MDB from which to link
Drop source subdatasheet
Drop source indexes
Change append query locking to "all" (won't let me try None)

Working with the cumulative effect of these edits (minus Autonumber) I have started dropping groups of fields in my target table to see if any data type has major impact:
Drop all target Single fields
Drop all target Long fields except 1
dates next....so far nothing more than a few KBs of impact.

This is just really strange.......



-- BoulderRidge B-)

 

[genomon]

Here's a shot in the dark - if "Track Name Autocorrect" is turned on, turn it off (it's in Tools|Options|General in pre 2007 versions. It's in the Office Button somewhere in 2007.
Also, what ODBC datasource are you linked to?

Change append query locking to "all" (won't let me try None)

Have you tried coding the SQL and using Do.Cmd RunSQL or an ADO command object instead of a canned Access append query?

Beir bua agus beannacht!

 

[BoulderRidge]

HI genomon,

Nice shot in the dark, but I had tried that once too....just checked and it hadn't stuck so retried it but no impact.

All the tables are in Access 2003, no external ODBC connection.

Have not tried coding it, I think splitting my tables is easier for now but will keep that in mind.

Having started caving and going with the split method, I have a new observation that further implicates my target table structure as the culprit:
1. Created new empty MDB1 and imported (not linked) a copy of my 2nd Source table (550,000 records). MDB size was 155 MB at this point.
2. Made two empty copies of this Source table structure within MDB1, call them SourcePart1 and SourcePart2.
3. Created two append queries using date criteria to put half the Source data into SourcePart1 and the other half into SourcePart2.
4. Execute both append queries, so now I have twice the original data all in the same physical MDB and have run two append queries within it. The MDB only grew from 155 MB to 350 MB and shrunk slightly when compacted.
---This implies that generically the append query idea is not the problem---
THEN:
5. Created new empty MDB and linked SourcePart1 into it.
6. Imported my problematic old empty target table structure (with the extra fields) and the matching append query.
7. Ran this append from SourcePart1 into old Target so my new MDB contains physically only SourcePart1 records and it is again OVER 1 GB.
---This implies that something about my old Target table structure as described in my original post is at the root of the problem---

However will still look at any and every suggestion...while in the meantime I pursue the split file solution in the interest of getting the data processing done.

Thanks! (I like your signature phrase)




-- BoulderRidge B-)

 

[genomon]

One more thought: right-click the table and open properties.
If row level tracking is turned on, try turning it off.

Beir bua agus beannacht!

 

[BoulderRidge]

Hmmm....how many little properties are hiding out there anyway?

It was not turned on for my Target table nor for my source tables when checked from inside their native MDB. It was turned on for the linked source table from within my working MDB but would not let me turn it off there.

So, not able to change anything here but thanks again!


-- BoulderRidge B-)

 

[danvlas]

Any indexes on the table?



Daniel Vlas
Systems Consultant

 

[BoulderRidge]

I dropped all the indexes on the source and target tables as one of the first things to try to no avail. I later tried adding just an Autonumber index to the source table but that didn't have any significant impact either.


-- BoulderRidge B-)

 

[dRahme]

We had an issue similar to that once where we were importing data from massive spreadsheets. Couldn't get rid of the bloat through compacting and the other usual means.

What did work as first appending the Autonumber to the table and then running an update query based on the autonumber as the PK to load up the fields and trimming the imported fields while doing so - Trim(theField).

I wouldn't logically think that would work but it did. Reduced the db size by about 40%.

Or, maybe the gremlins decided that it wasn't a particularly good day to play.

 

[BoulderRidge]

Huh. I can't get rid of the bloat by compacting either...which makes no sense (but would not have really helped anyways).

If I just append my 26 populated fields from Source table to a modified Target table that only has the 26 fields, the MDB stays small. But if I append my 26 populated fields from Source table to my "complete" Target table that has 155 fields, not actually specifying the extra 129 fields anywhere in my append query...just letting them stay empty...THAT is where I get the bloat. None of the extra fields are "Required" and they do "allow zero length" so I just can't figure why they are making it grow.

I think it is gremlins.

FYI I have nearly completed the data processing job by splitting out my biggest files into halves...but additional theories are always welcome if you want to try and disprove gremlins.

Thanks!


-- BoulderRidge B-)

 

[Ray1127]

In Access the space is saved regardless of the data in the table. for example you stated that for each record there is an additional 1307 bytes for new Char Fields with 24 DATE 4 or 8 bytes per date If the dateformat includes the time it's 8 bytes otherwise 4, 15 LONG 4 bytes, 9 SINGLE 1 byte so that's a total of 1307 + (96 for date 192 for datetime) 60 for the long's and 9 for singles. a total of 1471 or 1557 bytes per record. Even though the fields are empty Access still reserves that space. So that's a total of 700 MB for datetime and 661 MB for date. Of added space the DB uses that has no data in it.

 

[BoulderRidge]

Well that might explain it then. I was under the assumption that since these fields were not required, had no default values, and the text ones did allow zero length, that the table would not take up all the allotted space. In fact, one of the things I had played with was dropping all the date (or single, or long in turn) fields from the Target table and then running the query specifically to see if the bloat was substantially reduced without the missing set of fields. In each case, the bloat was only affected by 10-20 MB rather than the calculated 700+ so that had confirmed my original assumption.

I might go back and play with this again, see if I can repeat the results or not given your assertion.

Thanks!


-- BoulderRidge B-)