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.
| 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 LarssonHelsingborg, Sweden |
 |
|
|
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 AthalyeIndia."Nothing is Impossible" |
|
|
|
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? |
|
|
|
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 |
|
|
|
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 placeb) The original design isn't properly indexedand/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 |
|
|
|
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 |
|
|
|
|
|
|
|
|
How/what is Denormalisation or Denormalization