Results 1 to 1 of 1
Thread: Tip - Manage your data types
2001-03-30, 05:04 #1
- Join Date
- Feb 2001
- Youngstown, Ohio, USA
- Thanked 0 Times in 0 Posts
Tip - Manage your data types
Here is a pointer that may help in developing your databases; something I wish I had understood better when I first started tinkering with Access. (Note to self: RTFM) <img src=/S/rtfm.gif border=0 alt=rtfm width=24 height=23>
<font color=red>Field data settings are very important in fighting unwanted bloat.</font color=red> I never paid too much attention to these settings when creating new tables, other than setting number formats to "Standard" to make them easier to read, or to flag the field for indexing. All text fields were left at a default of 20, unless I needed them bigger. All number fields were being left as "Double".
I figured that "it's only going to be a small table", and I had gigabytes to spare on my drive, so managing the size of the database wasn't really important. This mindset eventually became habitual, and I continued to let Access dictate the structure of my data. This worked for me for quite some time, until I started working with databases that held hundreds of thousands of records, millions of individual, space-sucking fields. My files were quickly ballooning to hundreds of megabytes! <img src=/S/sick.gif border=0 alt=sick width=15 height=15>
I finally decided to take a longer, harder look at the different field sizes that were available. In many instances, "Double"-sized numbers that were taking up 8 bytes per field really only needed to be "Single", "Integer", or even "Byte"-sized, for 4, 2, or even 1 byte per field. I also managed to squeeze down my text fields to the absolute minimum required, which quite often was less than the 20 previously allocated. Imagine my surprise when I was able to cut a database down to ~160MB from ~300MB! <img src=/S/joy.gif border=0 alt=joy width=23 height=23>
Nearly half of my database was tied up in useless bloat. Worse than useless actually, since the needlessly-large size also hindered the database's performance. It was only yesterday that I noticed the following, carefully hidden in plain view on the help page:<hr>"You should use the smallest possible FieldSize property setting because smaller data sizes can be processed faster and require less memory."<hr>
Hope this can help some of the newer Access users keep a good handle on their databases.
<img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>