Sybase 12.4.2 manual Managing database size and structure, Managing the size of your database, 454

Managing database size and structure

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

454