Denormalize for Performance?

Paul Nielsen — Fri, 03 Oct 2008 12:00:00 GMT

Conventional wisdom says to “normalize to 3rd normal form then denormalize for performance.” Poppycock! I say for an OLTP operational database (not a reporting or BI database), a well normalized design will outperform a denormalized design for three good reasons:

1) If the denormalization duplicates data, then the DML operations have to write to multiple tables, this means extra code and extra work.

2) If the denormalization duplicates data, these will typically be keys or at least candidate keys, so they’ll be indexed which contributes to the index bloat problem.

3) If the denormalization repeats columns (item1, item2, item3, etc), then the data has to massaged before being inserted or updated, and when reading the data it typically needs to be unpivoted for set-based code, and these operations mean extra work.

Those who promote denormalization would say argue that denormalization is trading write performance for read performance, and this is true for reporting database, but in issue number 3 above, even read performance suffers.

Since denormalization tends to go with SQL code in the app layer (no database abstraction in the form of stored procedures), the denormalization also impacts extensibility, since it’s even more expensive now to modify the database schema.

In production databases I’ve built, I’ve tested a pure normalized design vs. the best denormalized design I could come up with. Having a clearly defined database abstraction layer meant that I could run one script to modify the table structure, correct the data, and alter the stored procedure, and the app could continue to make the same stored procedure calls without breaking. In every case, the normalized design outperformed the denormalized design by about 15% due the normalized design’s more efficient code.

Another Reason to Normalize

Paul Nielsen — Tue, 16 Oct 2007 18:48:00 GMT

Database can be measured by six properties – four determined by the design; Usability, Integrity, Performance/Scalability, and Extensibility; and two being more a function of implementation than design; Availability and Security.

Of these six factors, I’d argue that in the long run, extensibility is the one that is more expensive to repair. All six are necessary, but a database that has become brittle (touch one thing, everything else breaks) is either impossible or extremely expensive to correct. Database extensibility is bought with three techniques; a strongly enforced data abstraction layer, data driven design, and normalization. Normalization is typically credited with the integrity of the database, and this is true. But normalization also influences the extensibility of the database. Here’s why,

A poorly designed, anti-normalized database is typically made to work using multiple layers of convoluted code. It takes a lot of extra code to keep anti-normalized data consistent. All this code is more expensive to maintain, and makes it very difficult to implement changes and new features.

It’s not even a factor of pay-me-now or pay-me-later. An anti-normalized database doesn’t just cost a bit more to fix later; it costs so much to fix later that companies, or careers, fail due to the poor design. The bottom line is still that a man-day spent on database design saves, at least, a man-month of extra work later.

Paul Nielsen : Normalization

Denormalize for Performance?

Another Reason to Normalize