| | |
|
|
|
space/storage |
|---|
| message from =?Utf-8?B?Sm9obiBL?= on 27 May 2004 |
have a person who has told me that given a table with say 200 columns in it... if I create a bunch of records in that table such that only ID field contain a value, while the rest of the fields are null (as in vbnull) that the amount of storage is significantly less compared to if the fields contain actual data. In otherwords, null fields take up no storage (or at leat minimal).... I just tried the experiement and it did not seem to behave that way ... even with compacted DB.... So is what I was told true?
|
| Douglas J. Steele replied to =?Utf-8?B?Sm9obiBL?= on 27 May 2004 |
What you were told is true, sort of.
If your fields are numeric, then they're going to take a specific number of
bytes whether they have a value or are Null. Text fields, however, only take
as many bytes of storage as are required. A text field holding "This is my
data" will only require 14 bytes of storage, even if you've defined it to be
capable of holding 255 bytes (the maximum size for a text field)
Something else to be aware of, though, is that Access works with pages (2K
for Access 97 and earlier, 4K for Access 2000 and newer), and that a row
must fit in a page. It's possible that you didn't test with sufficient rows
to alter the number of pages required with and without data.
|
| anonymous replied to Douglas J. Steele on 27 May 2004 |
so having nulls in numeric fields does not save storage.
Meaning if deafult value is null, a record of straight
numbers columns take up the same amount of space as if
the record contained 0
|
| Douglas J. Steele replied to anonymous on 27 May 2004 |
That's correct. An integer field takes 2 bytes by definition, a Long Integer
field takes 4 bytes and so on. There's also 1 byte of overhead per field.
|
| Albert D. Kallal replied to anonymous on 27 May 2004 |
correct
|
| Jim/Chris replied to =?Utf-8?B?Sm9obiBL?= on 27 May 2004 |
A table with 200 fields seems to be excessive and will
cause performance problems down the road. I suggest
breaking the table into 2 or 3 tables.
Jim
200 columns in it... if I create a bunch of records in that
table such that only ID field contain a value, while the
rest of the fields are null (as in vbnull) that the amount
of storage is significantly less compared to if the fields
contain actual data. In otherwords, null fields take up
no storage (or at leat minimal).... I just tried the
experiement and it did not seem to behave that way ... even
with compacted DB.... So is what I was told true?
|
| Tony Toews replied to Jim/Chris on 28 May 2004 |
Assuming that it should indeed be normalized, which would be likely,
I'd agree with you. If however it is normalized then I'd be more
concerned about hitting the 2K if Jet 3.5 or 4K if Jet 4.0 maximum
record size.
I'm not at all sure this would make much of a performance difference.
Tony
|
|
Archived message: space/storage (Microsoft Access Database)
