July 23, 2005 at 10:58 pm
Does anyone have any guidelines on when to Normalize or When to Denormalize a table ? I've seen a rule of thumb that if the required structures result in queries with more than 7 joins...that is an indication that the underlying tables should be denormalized. How much of a gain are we looking at in terms of performance when it comes to denormalizing a table ? What if ram could be increased on the server or processor speed could be increased. Is the latent cost of normalization due to lack of primary memory or increased process cycles ?
Thanks in Advance,
Matt
Mathew J Kulangara
sqladventures.blogspot.com
July 23, 2005 at 11:30 pm
7 is just a number. If you have 2 tables with 1 B rows each and you need to get fast running queries, you might need to use a few technics to achieve that (denormalization being one of them). But you can also have a 50 tables join run in 1 ms if the query is correctly written and the clustered index well chosen. This must be studied case by case and tested against the normalised version. You must also keep in mind that there can be a very high cost in maintaining the denormalized version which can outweigh the speed gain of the select(s).
July 25, 2005 at 3:21 am
Remi is completely correct. I have been working with relational databases for over 20 years and my advise would be the same as Remi's.
All that I would add is if you are still designing the tables, your logical design should be normalised. If you do not understand your data enough to get to 3-rd form normalisation for the logical design, you do not understand your data enough to build a useful application.
If you identify potential performance issues in translating your logical design to a physical design, you can look at various ways to resolve this. As Remi says, this should include index design and not just denormalisation. Denormalisation can be a way to improve query performance, but it often harms input and update performance. If you applications are not properly designed or coded, you can get inconsistant data in your denormalised rows.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply