Database Performance
When you design a database, you must ensure that the database performs all the important functions correctly and quickly. Some performance issues can be resolved after the database is in production, but other performance issues may be the result of a poor database design and can be addressed only by changing the structure and design of the database.
When you design and implement a database, you should identify the large tables in the database and the more complex processes that the database will perform, and give special consideration to performance when designing these tables. Also consider the effect on performance of increasing the number of users who can access the database.
Examples of design changes that improve performance include:
- If a table containing hundreds of thousands of rows must be summarized for a daily report, you can add a column or columns to the table that contains preaggregated data to be used only for the report.
- Databases can be overnormalized, which means the database is defined with numerous, small, interrelated tables. When the database is processing the data in these tables, it has to perform a great deal of extra work to combine the related data. This extra processing can reduce the performance of the database. In these situations, denormalizing the database slightly to simplify complex processes can improve performance.
In conjunction with correct database design, correct use of indexes, RAID (redundant array of independent disks), and filegroups is important for achieving good performance.
Hardware Considerations
Generally, the larger the database, the greater the hardware requirements. But there are other determining factors: the number of concurrent users/sessions, transaction throughput, and the types of operations within the database. For example, a database containing infrequently updated data for a school library would generally have lower hardware requirements than a 1-terabyte (TB) data warehouse containing frequently analyzed sales, product, and customer information of a large corporation. Aside from the disk storage requirements, more memory and faster processors would be needed for the data warehouse to enable more of the data to be cached in memory and queries referencing large amounts of data to be processed quickly.