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 |
|
Jaypoc
Yak Posting Veteran
58 Posts |
 Posted - 2008-09-03 : 17:39:50
|
| This is kind of a general question about normalization, and perhaps someone could answer, or point me to a good reference on the subject.I'm curious what kind of impact normalization has on performance. Can you over-normalize causing SQL to take longer to cross-reference data from multiple tables?I work on websites that are accessed by an internal department of about 300 users at a time, some of the sites being hit several times a minute by each user. A decent load I would imagine.I'm curious if I should be breaking the databases down so every element has it's own table, or if it would be more efficient to have data duplicate itself across multiple tables on inserting. (since few people insert data, but everyone access pages that select data)Some queries I run have to pull data from 3 or more tables (for instance, I pull usernames/passwords from one table, position titles from another, payroll data from another, and other information from various other tables for display on a summary page... At what point is this no longer efficient and should I consider building a single table containing all this information (updated periodically via manual or scheduled update)? |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-09-03 : 18:27:05
|
quote:
Originally posted by Jaypoc
Some queries I run have to pull data from 3 or more tables (for instance, I pull usernames/passwords from one table, position titles from another, payroll data from another, and other information from various other tables for display on a summary page... At what point is this no longer efficient and should I consider building a single table containing all this information (updated periodically via manual or scheduled update)?
~6.42Boycotted Beijing Olympics 2008 |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-09-03 : 18:35:46
|
| Just build your database with a normalized schema.One of the worst things you can do is to demormalize a transactional database, expecially when you have no reason to.People who say they denormalized a database for performance usually had no idea how to create a normalized database in the first place, and use denormalization to explain away their bad design.CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-03 : 18:41:45
|
quote:
Originally posted by Jaypoc
I work on websites that are accessed by an internal department of about 300 users at a time, some of the sites being hit several times a minute by each user. A decent load I would imagine.
This is small potatoes. Imagine administering a system with thousands of transactions per second. No denormalization necessary to support it. At some point though, partitioning needs to be considered.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-09-03 : 18:59:59
|
one fact in one place please. :)
elsasoft.org |
|
|
|
|
|
|
|
|
Normalization vs Performance