April 30, 2002 at 2:29 pm
A coworker asked me the other day when is a good time to use database de-normalization, and I couldn't give a good answer. I know there are situations that call for it; but I can't think of any?
Anyone?
April 30, 2002 at 4:59 pm
Typically it's when you just can't get the performance any other way. Even then I'd look at using an indexed view or triggers to maintain it rather than relying on the application. I try to tune first, if that fails look for an alternative approach, only in last resort denormalize.
Andy
April 30, 2002 at 6:15 pm
I think Andy is speaking of typical OLTP environments. Barring an indexed view, if we find we're having to make a large number of JOINs to return data, we may decide to denormalize in order to improve performance. However, we have to be careful we don't tamper with our data integrity. Another use is on warehousing solutions. The case of the star schema is a denormalized structure which is optimal for a lot of OLAP scenarios.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
April 30, 2002 at 6:42 pm
That makes sense. For data warehousing.
I do have a lot of joins thta I have to do, because of how well normalized my databases are. I am writing a program to archive old data, and it promises to be a real project, with all of the data being everywhere (in several tables). This might be a good time to assimilate the data into a view, and then export it denormalized to the archive database.
April 30, 2002 at 8:05 pm
Even with a large number of joins it may not be needed to de-normalize. Test somewhere to see if you get any bennefits de-normalizing by having fewer joins. Generally does but not always. But it does boil down to access times with large number of joins.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 30, 2002 at 10:20 pm
Hi there
As an example, ive seen it used to supplement a complex tax calculation, where its calculated once so subsequent queries just use the value rather than applying the function. I have also used it to simplify lookups, where a complex join N deep is summerised up to the parent table. In the end, its all good and well but relies on rock solid developer commitment to maintain it.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply