ASA SQL User's Guide
Query Optimization and Execution
Physical data organization and access
The page size you choose for your database can affect the performance of your database. In general, smaller page sizes are likely to benefit operations that retrieve a relatively small number of rows from random locations. By contrast, larger pages tend to benefit queries that perform sequential table scans, particularly when the rows are stored on pages in the order the rows are retrieved via an index. In this situation, reading one page into memory to obtain the values of one row may have the side effect of loading the contents of the next few rows into memory. Often, the physical design of disks permits them to retrieve fewer large blocks more efficiently than many small ones.
Adaptive Server Anywhere creates a bitmap for sufficiently large tables within databases that have at least a 2K page size. Each table's bitmap reflects the position of each table page in the entire dbspace file. For databases of 2K, 4K, or 8K pages, the server utilizes the bitmap to read large blocks (64K) of table pages instead of single pages at a time, reducing the total number of I/O operations to disk and hence improving performance. Users cannot control the server's criteria for bitmap creation or usage.
Note that bitmaps, also called page maps, are only available for databases created in version 8.0 and higher. If a database is upgraded from an older version, the server will not create a bitmap for database tables, even if they meet its criteria. Bitmaps are not created for work tables or system tables.
Should you choose a larger page size, such as 4 kb, you may wish to increase the size of the cache. Fewer large pages can fit into the same space. For example, 1 Mb of memory can hold 1000 pages that are each 1 kb in size, but only 250 pages that are 4 kb in size. How many pages is enough depends entirely on your database and the nature of the queries your application performs. You can conduct performance tests with various cache sizes. If your cache cannot hold enough pages, performance suffers as Adaptive Server Anywhere begins swapping frequently-used pages to disk.
Page sizes also affect indexes. By default, index pages have a hash size of 10 bytes: they store approximately the first 10 bytes of data for each index entry. This allows for a fan-out of roughly 200 using 4K pages, meaning that each index page holds 200 rows, or 40 000 rows with a two-level index. Each new level of an index allows for a table 200 times larger. Page size can significantly affect fan-out, in turn affecting the depth of index required for a table. Large databases should have 4K pages.
Adaptive Server Anywhere attempts to fill pages as much as possible. Empty space accumulates only when new objects are too large to fit empty space on existing pages. Consequently, adjusting the page size may not significantly affect the overall size of your database.