What effect will denormalization have on queries, joins and response time?

Question

Before denormalizing, I'm wondering what effect this is going to have on the following:

• Query response time
• Width of rows in the database
• Joins necessary for a result
• Number of queries necessary for requests to complete

It seems, if I am not mistaken, that all of these will be reduced?

Answer 1

If you happen to work in Microsoft SQL Server, I highly recommend keeping your tables normalized and using so called Indexed Views for denormalizations. These are semi-permanent data structures that are updated whenever the underlying tables are updated. This way you keep the best of both worlds -- normalized schema AND fast denormalized data!

Something similar may also exist for Oracle, not sure.

Answer 2

Your assumptions are correct. Denormalizing will increase performance, but the downside is that it reduces correctness.

This topic has been discussed at length in this previous stackoverflow question