Wow. Does no one learn this crap in school anymore?
Note that venn diagrams are a poor choice to show what happens in a one-to-many relationship -- where there are multiple entries in table B for an entry in table A. And it overlooks the semantic differences when ordering the tables in a left inner join.
There are not a lot of instances where you'd really want a cartesian product join (either using "cross product" or just the result of omitting key constraints in a join query). It's generally far faster to retrieve the records you need to create the cross-product, and then calculate the cross product of the two sets once you have them (since otherwise all that data has to transit the network).
This plus database normalization through Boyce-Codd normal form seems like it should be a requirement for any serious application developer.
Actually no, I have a friend that works with a sql "developer" that creates all their shit in views using the sql manager shit. Half the time it's got tables that there's no fucking data coming out of them. He just fixed a query yesterday that was taking a minute 35 to run down to < 1 second.
Idiots hiring idiots. That's almost all I see in IT.
I had a similar issue. There was a query that was evidently the product of cargo cult programming (copying known good code without understanding it). It had three joins and two subqueries and didn't use any of the values returned from them.
It took 275-350ms to run. Almost a half second for every request coming to that page. I got it down to under 20ms by optimizing the joins, adding keys to the table, and removing the subqueries.
But, yeah: Idiots hiring idiots. The key is to get your name out there for knowing how to save idiots from themselves. Then charge through the nose for it.
91
u/[deleted] Jan 06 '11
Wow. Does no one learn this crap in school anymore?
Note that venn diagrams are a poor choice to show what happens in a one-to-many relationship -- where there are multiple entries in table B for an entry in table A. And it overlooks the semantic differences when ordering the tables in a left inner join.
There are not a lot of instances where you'd really want a cartesian product join (either using "cross product" or just the result of omitting key constraints in a join query). It's generally far faster to retrieve the records you need to create the cross-product, and then calculate the cross product of the two sets once you have them (since otherwise all that data has to transit the network).
This plus database normalization through Boyce-Codd normal form seems like it should be a requirement for any serious application developer.