Of late, I have been using Twitter quite frequently, and I am gradually discovering its usefulness. I received a Direct Message (or DM in terms of twitter) asking if I can comment on the effect of normalization on Index and its performance in one twit! Now honestly speaking, this was new for me. I never expected to be quizzed like this. If you are using Twitter, then you must be aware that one twit contains only 140 characters. I was supposed to give answer on such a big subject in just 140 letters. An interesting fact is that normalization and Index are not really closely related. The right question should have been – what is the effect of normalization on performance?
In any case, let us go over my twit answer.
“Normalization increases count of table along with clustered index which makes every single entity with its independent index improving perf.”
Within seconds of my posting the above twit I received a reply from Mladen Prajdic – “which you loose on joins :)”
This whole scenario is very intriguing. I will try to give a comprehensive explanation. Prior to normalization, usually data is in a large table. As it gets normalized, all the redundant information is removed and data is categorized and placed in their respective tables. It is a common practice to have PK in tables that leads to additional clustered indexes on the database. This means normalized database has more clustered indexes. As there is no redundancy, each table contains a unique entry for data. Because of this all the clustered indexes have fewer rows, which effectively increase the speed of database operations. Besides, as there is no redundancy, normalized database requires less storage and maintenance. Now when query is intricate and involves numerous tables, lots of joins are introduced. These joins effectively diminish performance. However, this loss of performance is less compared to the gain which was a result of normalization.
In case you have skipped the above paragraph and straightaway reading this sentence, I urge you to go back and pay attention to the previous paragraph. This paragraph provides a good explanation to help you understand the logical backstage theory of normalization and performance relations.
I requested Mladen Prajdic for his opinion in one twit and given below is his response. You can follow Mladen Prajdic on Twitter here.
Let me know what you think about this subject. If possible, participate here by giving your explanation within 140 characters. Let us see what you all can come up with. Follow me on Twitter.
Reference : Pinal Dave (http://blog.sqlauthority.com)