Sum of data types X number of records = Data pages?

[BobEulberg]

Maybe I'm taking crazy pills, but I could swear I've seen references before to something called "data pages." I believe it was in response to people asking how many fields is "too many" in a table. I'm looking for specifics on them but haven't had any luck, but I believe they might help with performance.

I understand them to work this way: Basically if you add up all the data types of a table, multiply that times the the number of records, you get the number of bytes that information uses. And Access retrieves data in chunks called "pages." So the smaller your data types and/or records, the less data pages required for access to bring a table into memory and use it.

For example, if I have a table with 20 Double fields (8 bytes ea) and 40 Date fields (8 bytes ea), then each record would take up 480 bytes. If I have 100,000 records, the table uses 48,000,000 bytes. If Access pulls 500 bytes per data page, that's 96,000 data pages it has to pull. But if it really pulls 479 bytes (for sake of argument), then it has to pull 100,209 data pages.

Anyhoo, does anyone know how big a data page is in Access? Or if it's a setting that can be modified? I'd like to keep that in mind with my table design for a new project.

 

[dhookom]

I have not heard of anyone that is too concerned about these numbers. I believe the maximum number of characters in a row is about 2,000 making pages equal to 2k. Check

http://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine

for more information.

I would find it hard to understand a normalized table design that contained 20 numeric and 40 date fields. Also, if you are concerned about performance and have many records, you might consider moving to SQL Server or the free Express version.

Duane
Hook'D on Access
MS Access MVP

 

[BobEulberg]

I believe the maximum number of characters in a row is about 2,000 making pages equal to 2k.

When you say characters, does that mean bytes? So if I have 3 Double fields in a table (24 bytes), then one page of data would consist of 83 (2,000/24) records?

Maybe the fact I had such a hard time finding out about this online should tell me going down this road won't help me much...

 

[dhookom]

Did you check the link I sent? I have never been concerned about the size of pages or much else regarding the internals of JET.

If you build your tables and interface correctly, you generally don't have to worry about any of technical stuff.

Duane
Hook'D on Access
MS Access MVP