more useful options | |
PerlMonks |
Re: Migrating a 1NF table to multiple 2NF tablesby dws (Chancellor) |
on Dec 05, 2001 at 02:06 UTC ( [id://129458]=note: print w/replies, xml ) | Need Help?? |
To this otherwise informative post, I take one exception:
Normaliztion forces JOIN's to be used in order to retrieve the equivalent row of the orginal table, and JOIN's can be quite expensive. In the real world, you will find many databases that have been DE-normalized in order to speed up certain critical queries. To tell the truth, most databases i have seen in the real world aren't even normalized in the first place, probably because JOIN syntax is complicated and intimidating. JOINs can be expensive, but they can also improve performance. I once got a 15x increase in performance by turning a 6-way join into a 7-way join. It was originally 6-way because the author had bought into the superstition that "JOINs are bad and Are To Be Avoided". He thought he was doing good by minimizing JOINs, but he hadn't taken the time to dig further to understand query planning. Modern RDBMSs are pretty good when it comes to (internally) optimizing the order of JOINs when the fields you are joining on are indexed. And some RDBMSs will even optimize to handle cases where you're joining against a table that requires a linear scan. It pays to understand JOINs. The syntax isn't really that complicated, and the payoff you get in data integrity by using a normalized form (2NF or 3NF) to avoid duplication can be a really big win. Really big shops separate logical database design from physical design. Physical design involves things like deciding how to physically partition the database so that files involved in performance-critical joins live under separate disk heads (since moving disk heads is a relatively expensive operation). One way to measure the maturity of a shop is to look at what kind of physical design they've done. If their tables, temp space, and log space are on the same drive, they're probably not very mature (or performance isn't an issue).
In Section
Cool Uses for Perl
|
|