VistaDB 5
Pagesize performance implications

Pagesize impacts performance

As an example of why limits are needed, lets say you have a database with an average row length of 500 bytes, but your pagesize is set to 1k. More than 2 of these rows will usually fit in a page (resulting in very poor performance and fragmentation of indexes, etc). Pack will now increase the pagesize to attempt to fit 3-5 rows in a page.  This can have some consequences on filesize as well.

Other things like the maximum number of columns in a table were all dynamically calculated based upon the data in the schema. This is great from a technical standpoint as it provides the most flexibility, but unless you love databases and schema the way we do you probably don't ever check this stuff and just expect it to handle it.  Now we are enforcing limits based upon the pagesize that make sense in what we see from user databases.

When you add in some of the overhead per page or index there are numbers that don't appear to line up evenly.  The numbers have been rounded to make more sense and are now enforced at creation and update. For example if you have a 1k database and attempt to add a new column to that database you may get an error that the max number of columns have been exceeded for your pagesize. Packing the database may modify the pagesize for you, or you may need to go do it by hand.

In most cases limits are modeled on SQL Server limits so users will have no problem upsizing later if they need to do so.  SQL Server always has an 8 kb page size.  While VistaDB supports up to 16kb it isn't recommended since this exceeds SQL Server's limit.

Self Imposed Limits

VistaDB was designed using 32-bit and 64-bit memory addressing in most situations that required large upper limits. The effect of supporting large upper limits are ranges that exceed 4 billion or 4GB for 32-bit values and provides massive Exabytes limits for 64-bit values.

Because VistaDB is focused on supporting the creation of small to mid size applications, supporting such enormous upper limits is not feasible for testing. Therefore it has some self-imposed limits when it seemed appropriate.

See Also