Re: Proposal: 6NF
Date: 30 Sep 2006 17:04:56 -0700
Message-ID: <1159661096.259459.37470_at_b28g2000cwb.googlegroups.com>
Brian Selzer wrote:
>
[Quoted] > With today's technology, querying multiple tables
[Quoted] > is almost always more expensive than querying one ...
A couple of years ago, working on a database of major importance to our company, we had reached a situation where, due to evolving product requirements, we had a case of *two* tables that had ceased to have any importance. These tables sat smack in the middle of the very most often used join paths.
Each table below had a foreign key to the table listed immediately prior:
Customers <- A <- B <- C <- D <- E <- F1, F2, F3 etc.
Tables C and D had evolved such that every B had exactly one C, and every C had exactly one D. Because table E was of such importance to the product, there were a huge number of queries that included "Customers join A join B join C join D join E" with various other things. 9 and 10 table joins were not uncommon.
Once the product changes had settled down and it became clear that tables C and D were never going to be relevant again, I embarked on a course of getting rid of them. Hundreds of queries would need to be rewritten, but I really thought it would be worth it to shorten the join path by 2! Performance was always an issue, and I felt certain that a 7 table join would perform better than a 9 table join.
However, I have enough experience to know that one doesn't go on one's feelings about performance but rather on measurement. So I devised a series of test queries to measure the benefit of all this work.
Total measured benefit: bupkiss. I changed my mind about the refactoring.
Marshall Received on Sun Oct 01 2006 - 02:04:56 CEST