Database size depends largely on indexing and data quantity. Create indexes for faster queries. Drop unnecessary objects to free disk space and shorten load times.
To control the quantity of data stored in a given table, eliminate data rows you no longer need. If your database contains data that originated in a SQL Anywhere database, you may be able to eradicate unneeded data by simply replaying Anywhere deletions; command syntax is compatible. You can do the same with data from an Adaptive Server Enterprise database, because Sybase IQ provides Transact-SQL compatibility
Internal index fragmentation occurs when index pages are not being used to their maximum volume.
Row fragmentation occurs when rows are deleted. Deleting an entire page of rows frees the page, but if some rows on a page are unused, the unused space remains on the disk.
DML operations (INSERT, UPDATE, DELETE) on tables can cause index fragmentation.
sp_iqrowdensity reports row fragmentation at the default index level. See “sp_iqrowdensity procedure,” in “System Procedures,” in Reference: Building Blocks, Tables, and Procedures.
sp_iqindexfragmentation reports internal fragmentation within supplemental indexes. See “sp_iqindexfragmentation procedure,” in Reference: Building Blocks, Tables, and Procedures > System Procedures.
Review the output and decide whether you want to recreate, reorganize, or rebuild the indexes. You can create other indexes to supplement the default column index. These indexes can, however, use more space than needed when rows are deleted from a table.
Growth of the catalog files is normal and varies depending on the application and catalog content. The size of the .db file does not affect performance, and free pages within the .db file are reused as needed.
Avoid using IN SYSTEM on CREATE TABLE statements
Issue COMMIT statements after running system stored procedures
Issue COMMIT statements during long-running transactions