Overflow and Performance
Overflow chains slow concurrent performance. Overflow pages are attached to the main data page if a record must be added to a full main page. The query that touches one main data page must now touch that page plus each associated overflow page. This increases I/O, cause concurrency problems, and uses up locking system resources.
Here are suggestions:
• Monitor overflow chains.
Check the number of overflow pages for your tables and secondary indexes. In Actian Director, right-click a table or secondary index, and then select Properties. In VDBA, select a table or secondary index in the Database Object Manager window, and click the Pages tab. Use the legend to interpret the information displayed.
If the number of overflow pages is greater than 10-15% of the number of data pages, expect performance degradation.
• Check for duplicate keys. Overflow problems are often caused by them.
• Consider trying a different storage structure. Some table structures create long overflow chains when much new data is added. For details, see
Storage Structure and Overflow.
• Decrease overflow
Here are ways to decrease overflow and improve concurrency:
– Use unique keys.
– Modify the table to reorganize it; with a B-tree structure, simply specify the Shrink B-tree Index option.
– Consider tailoring the table’s fill factor.
For additional information, see the sections on overflow and fill factor in the chapters “Choosing Storage Structures and Secondary Indexes” and “Maintaining Storage Structures.”