Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How/what is Denormalisation or Denormalization

Author  Topic 

abuhassan

105 Posts

Posted - 2006-09-01 : 07:52:06
Hi

I came accross recently the term denormalisation basically the article that i read in a magazine said after normalising a database you should follow "by tactical denormalisation to improve database performance."

My questions are:

- What is denormali(z/s)ation? (my understanding is after you normalise you go back in reverse using some sort of technique?)?

- Does it increase performance?

- can any one give example of how it can be used... where the performance is greater than the normalised way?

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-01 : 07:58:52
Denormalization can be faster. This technique is often used in DATAWAREHOUSES and DATAMARTS.
But the general rule is to keep the database as normalized as possible for easier maintenance.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-01 : 08:16:02
Denormalisation is basically applied in the case the database is over-normalised and no. of joins required to fetch the data are too much. It is a purposeful redundancy of some of the data to avoid the overhead of joins.

For more information check these links:

[url]http://en.wikipedia.org/wiki/Denormalization[/url]
[url]http://www.ixora.com.au/tips/design/redundancy.htm[/url]

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

abuhassan

105 Posts

Posted - 2006-09-01 : 08:21:34
Hi

can any one give a practical example for example a table or a small database where it will be useful?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-01 : 10:47:14
quote:
Originally posted by abuhassan

Hi

can any one give a practical example for example a table or a small database where it will be useful?




There are two main ways that de-normalization is used.

1. De-normalization is a term that is used by poor database designers to explain why their design is a huge stinking pile of crap. Most often, they don’t understand how to create a normalized design, so when someone objects that a design is not normalized, they explain it away by claiming they de-normalized it for performance reasons. That is by far the most practical and common use for de-normalization.

2. There are also a small number of expert designers who understand that a de-normalized design can increase performance in one part of a system while imposing a substantial penalty in performance and maintainability in other parts of the system. On rare occasions and after proper analysis of the relative costs and benefits, they may decide on a de-normalized design. However, this is very rare.




CODO ERGO SUM
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-01 : 11:08:53
9 times out of 10, when denormalizating is suggested, it is because:

a) The original design isn't properly normalized in the first place

b) The original design isn't properly indexed

and/or

c) The SQL used to access/manipulate the original design is poorly written (i.e., lots cursors, inefficient critieria and join expressions, FULL OUTER JOINS, lots of temp tables, programmers who don't know the basics of left joins, derived tables, etc)

Only when all 3 of the above have been completly eliminated as the reasons you are experiences performance issues should you even begin to consider denormalizing.


- Jeff
Go to Top of Page

cecil
Starting Member

1 Post

Posted - 2008-06-10 : 18:24:17
quote:
Originally posted by abuhassan

Hi

I came accross recently the term denormalisation basically the article that i read in a magazine said after normalising a database you should follow "by tactical denormalisation to improve database performance."

My questions are:

- What is denormali(z/s)ation? (my understanding is after you normalise you go back in reverse using some sort of technique?)?

- Does it increase performance?

- can any one give example of how it can be used... where the performance is greater than the normalised way?

thanks



Hi -

this might seem a bit off-message, but it looks like there's some sort of meaning-drift involving the word "denormalisation". If you look at the last 3 paragraphs in this blog-post, you'll see what i mean:

[link removed as it has nothing to do with sql]

Cecil
Go to Top of Page
   

- Advertisement -