Maximum record size and maximum number of records

[Tom_T]

Recently in this group I read an interesting explanation of the relationship
between maximum file size, maximum record size and data page size. If I
understand correctly, a Jet 4 db can hold up to 2GB of data, meaning 524,288
data pages of 4,096 bytes each. In the example given recently, the maximum
possible record size was 1,177 bytes. Consequently each data page could
hold no more than 3 such records, implying a maximum number of records per
table of 1,572,864 (minus room needed for overhead) in that file.

I have an mdb file with one large table that currently contains about
340,000 records. The file is currently about 135 MB in size. If I am
figuring correctly, the maximum possible record size is 12,608 (though no
record comes anywhere near being that fully populated.) The file is working
fine for the moment but it will likely grow to contain about 550,000
records. This leads to my questions:

- Am I calculating my maximum possible record size correctly? I did so by
running the Documenter on the large table and summing the value of the
"Size" column. (One of the fields is a Memo field but it is rarely
populated.)

- Assuming 12,608 bytes is about right, does that mean each record at
maximum size would require a little over 3 data pages? Meaning the maximum
number of fully populated records I could store would be 524,288 / 4 =
131,072 records? (Which of course is much less than are actually stored
now.)

- If any given record in my file actually contains fewer than 4096 bytes of
data, will that record only require a single data page to store it, even
though its maximum possible size would require 4 pages?

- Is there a straightforward way to estimate the actual "average" size of my
records? Would it be reasonably accurate to simply export it all as text
and divide by the number of records? I'd like to determine if I am likely
to run into any limits (vis-a-vis the data page limits) as the size of my
mdb file grows.

I am running Access 2003 and my Jet version is 4.0.9511.0.

I know that I could forestall such problems by using a different back end
but for a variety of reasons I'd like to stick with my current setup until
this particular project ends.

Thanks!
Tom

 

[John W. Vinson]

Recently in this group I read an interesting explanation of the relationship
between maximum file size, maximum record size and data page size. If I
understand correctly, a Jet 4 db can hold up to 2GB of data, meaning 524,288
data pages of 4,096 bytes each. In the example given recently, the maximum
possible record size was 1,177 bytes. Consequently each data page could
hold no more than 3 such records, implying a maximum number of records per
table of 1,572,864 (minus room needed for overhead) in that file.

That is incorrect. The *MAXIMUM* record size was 1,177 bytes. Your calculation
assumes that every single record in the table is that maximum size.

If there are records with less data, you can get more of them. Access does not
store trailing blanks, so Text fields will only occupy as much room as there
is actual text to be stored.

I have an mdb file with one large table that currently contains about
340,000 records. The file is currently about 135 MB in size. If I am
figuring correctly, the maximum possible record size is 12,608 (though no
record comes anywhere near being that fully populated.)

With the exception of Memo fields, the maximum possible record size of text
actually used is 4096 bytes. You can have a table with (say) 20 255-byte Text
fields, and even put 340,000 records into it - but as soon as you try to
actually *use* all (or even half) of these 20 fields, you'll get a rather
cryptic error message on inserting data.

The file is working
fine for the moment but it will likely grow to contain about 550,000
records. This leads to my questions:

- Am I calculating my maximum possible record size correctly? I did so by
running the Documenter on the large table and summing the value of the
"Size" column. (One of the fields is a Memo field but it is rarely
populated.)

Treat the Memo field as having 18 bytes toward the 4096 byte limit.

- Assuming 12,608 bytes is about right, does that mean each record at
maximum size would require a little over 3 data pages? Meaning the maximum
number of fully populated records I could store would be 524,288 / 4 =
131,072 records? (Which of course is much less than are actually stored
now.)

Don't get hung up on pages. Again, Access only stores *what you actually use*.
The 4096 byte limit is because a single record can occupy only ONE page
(exclusive of memo fields); but a page might have two, or three, or eight
records, if they're small. You can't really count pages.

- If any given record in my file actually contains fewer than 4096 bytes of
data, will that record only require a single data page to store it, even
though its maximum possible size would require 4 pages?

See above. One page is all you get, but it might be enough for several
records.

- Is there a straightforward way to estimate the actual "average" size of my
records? Would it be reasonably accurate to simply export it all as text
and divide by the number of records? I'd like to determine if I am likely
to run into any limits (vis-a-vis the data page limits) as the size of my
mdb file grows.

Compact and Repair. Check the size of the database. Add a few thousand typical
records; compact and repair and recheck. I'm pretty sure databases grow in
64KByte chunks so it won't be possible to get this to fine granularity.

I am running Access 2003 and my Jet version is 4.0.9511.0.

I know that I could forestall such problems by using a different back end
but for a variety of reasons I'd like to stick with my current setup until
this particular project ends.

SQL Express is cheap (free) and it's not all that hard to migrate your JET
application to it. Same 2Gbyte total database limit, though!

 

[Tom_T]

That is incorrect. The *MAXIMUM* record size was 1,177 bytes. Your
calculation
assumes that every single record in the table is that maximum size.

Yes, I understood that my calculation implied every single record was "maxed
out"; sorry if I didn't make that clear.

If there are records with less data, you can get more of them. Access does
not
store trailing blanks, so Text fields will only occupy as much room as
there
is actual text to be stored.


With the exception of Memo fields, the maximum possible record size of
text
actually used is 4096 bytes. You can have a table with (say) 20 255-byte
Text
fields, and even put 340,000 records into it - but as soon as you try to
actually *use* all (or even half) of these 20 fields, you'll get a rather
cryptic error message on inserting data.

I tried just that on a test db and got "Record too large." Interesting. It
did let me save the record when I deleted the contents of one field, i.e.,
19 fields containing 255 bytes each.

Given that the maximum possible size of a record is 4,096 bytes, is it
therefore considered "bad design" ever to construct a table whose field
sizes add up to more than 4K? Or is this considered "acceptable" depending
on the situation (for example if it is highly unlikely the total will ever
reach 4K)?

Treat the Memo field as having 18 bytes toward the 4096 byte limit.


Don't get hung up on pages. Again, Access only stores *what you actually
use*.
The 4096 byte limit is because a single record can occupy only ONE page
(exclusive of memo fields); but a page might have two, or three, or eight
records, if they're small. You can't really count pages.



See above. One page is all you get, but it might be enough for several
records.


Compact and Repair. Check the size of the database. Add a few thousand
typical
records; compact and repair and recheck. I'm pretty sure databases grow in
64KByte chunks so it won't be possible to get this to fine granularity.


SQL Express is cheap (free) and it's not all that hard to migrate your JET
application to it. Same 2Gbyte total database limit, though!

Thanks. I'll look into it, though I am hoping I can continue as is. We've
been using this db for seven years now without any problems, and the
projecting requiring its existence should only last another two years or so.
Still, better to be prepared.

Tom

 

[John W. Vinson]

On Tue, 10 Feb 2009 13:09:53 -0500, "Tom_T" <trombone_at_amnh_dot_org> wrote:


Given the fact that Access now uses Unicode, it's possible - depending on the
settings of unicode compression, which I'll confess I don't fully understand -
this can correspond to only 2048 *characters*.

I tried just that on a test db and got "Record too large." Interesting. It
did let me save the record when I deleted the contents of one field, i.e.,
19 fields containing 255 bytes each.

Yep. That's the error you'll get.

Given that the maximum possible size of a record is 4,096 bytes, is it
therefore considered "bad design" ever to construct a table whose field
sizes add up to more than 4K? Or is this considered "acceptable" depending
on the situation (for example if it is highly unlikely the total will ever
reach 4K)?

Well, how much do you like gambling, and how tolerant are your users of
cryptic (and sometime hard to diagnose) error messages? I'd tend to avoid
risking it.

Thanks. I'll look into it, though I am hoping I can continue as is. We've
been using this db for seven years now without any problems, and the
projecting requiring its existence should only last another two years or so.
Still, better to be prepared.

The biggest concern I'd have is that it's quite possible that a table with
(potentially) 12000 bytes in each record - in a large number of fields,
apparently - may not be completely normalized, and that each record may
contain a hidden one-to-many relationship (or several of them). Sure,
normalization can be carried to extremes, but I'm always a bit suspicious of
wide tables, even if I designed them myself!