Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

Re: Migrating a 1NF table to multiple 2NF tables

by dws (Chancellor)
on Dec 05, 2001 at 02:06 UTC ( #129458=note: print w/replies, xml ) Need Help??

in reply to Migrating a 1NF table to multiple 2NF tables

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).

  • Comment on Re: Migrating a 1NF table to multiple 2NF tables

Replies are listed 'Best First'.
Re: Re: Migrating a 1NF table to multiple 2NF tables
by uwevoelker (Pilgrim) on Dec 06, 2001 at 04:20 UTC
    Hello dws,

    can you please tell me, where I can learn more about JOINs?

    Thank you very much,

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://129458]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (7)
As of 2017-06-23 05:31 GMT
Find Nodes?
    Voting Booth?
    How many monitors do you use while coding?

    Results (535 votes). Check out past polls.