Effects of Denormalization on DB Performance

Karen Lopez - listmistress@Infoadvisors.com — Fri, 07 Dec 2007 21:03:16 GMT

An anonymous blogger has started writinga series of posts of his experiments with database design and performance. His profile describes him as "A Seattle database guy who works at start ups."

In the last article the performance impact of joins was shown. This one will demonstrate cases where denormalized joins are a bit faster, as will the third article with larger data volumes. The fourth article, the most interesting one, will show where a denormalized data model can be 50 times faster than a normalized data model.

Here are the tables that will be involved in the sql. The normalized ProductSmall table has a 100 million rows and is about 0.67 gig.

What I appreciate about his posts is the fact that he is supporting his positions with actual tests. So far his two blog posts have focused on very large tables (more than a million rows) and the impact of memory usage.

I'd also like to see him post about working with smaller data volumes. For instance, I work at times with new developers who tell me that our database or table is "very large" at 4,000 rows and needs a great deal of denormalization for performance reasons. I usually ask them to run tests similar to what the DBScience guy is doing to show me all the great benefits of combining 6 tables into one table with a total of 10,000 rows.

Check out his blog as he adds articles. http://dbscience.blogspot.com/

Karen Lopez: Musings on Data, Process, and Architecture

Effects of Denormalization on DB Performance