Denormalizing for Performance

Although denormalizing your database can improve performance, there are risks and disadvantages.

Risks

Denormalization can be successfully performed only with thorough knowledge of the application and should be performed only if performance issues indicate that it is needed. Consider the effort required to keep your data up-to-date.

This is a good example of the differences between decision support applications, which frequently need summaries of large amounts of data, and transaction processing needs, which perform discrete data modifications. Denormalization usually favors some processing, at a cost to others.

Denormalization has the potential for data integrity problems, which must be carefully documented and addressed in application design.

Deciding to Denormalize

Analyze the data access requirements of the applications in your environment and their actual performance characteristics, including:
  • What are the critical queries, and what is the expected response time?

  • What tables or columns do they use? How many rows per access?

  • What is the usual sort order?

  • What are concurrency expectations?

  • How big are the most frequently accessed tables?

  • Do any processes compute summaries?

  • Should you create join indexes to gain performance?