Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 474 / 536

Managing database size and structure
454
Managing database size and structure
This section offers ideas on improving your database design and managing
your data.
Managing the size of your database
The size of your database depends largely on the indexes you create, and the
quantity of data you maintain. You achieve faster query processing by creating
all of the indexes you need for the types of queries your users issue. However,
if you find that some tables or indexes are not needed, you can drop them. By
doing so, you free up disk space, increase the speed of backups, and reduce the
amount of archive storage you need for backups.
To control the quantity of data stored in a given table, consider how best to
eliminate data rows you no longer need. If your IQ database contains data that
originated in an Adaptive Server 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 Adaptive Server IQ provides Transact-SQL
compatibility.
Denormalizing for performance
Once you have created your database in normalized form, you may perform
benchmarks and decide to intentionally back away from normalization to
improve performance. Denormalizing:
• Can be done with tables or columns
• Assumes prior normalization
• Requires a knowledge of how the data is being used
Good reasons to denormalize are:
• All queries require access to the “full” set of joined data
• Computational complexity of derived columns require storage for
selects