Advanced Features as Crutches
I am glad to see all of the advanced features in MySQL. They all have their place, whether it's making things possible that weren't before, or improving ease-of-use, or improving thru-put and performance. However I find that just because you can use an advanced feature in place of the old way doesn't necessarily mean that you should.
If there are several ways to accomplish the same thing, my first inclination is to go with the one that is simplest to code. My philosophy is that if I arrange the syntax in a logical order with each link in the chain in its proper place, that this will make it that much easier for the server to optimally execute it.
I think that some people will code a subquery in preference to the equivalent join syntax mostly from a basic lack of understanding of how joins work. My personal preference is to code something as a join and avoid use of subqueries wherever possible. If the subquery functionality did not exist (and it still doesn't for a lot of users, but that's an entirely different issue) then people would perforce use joins.
Subqueries are not bad things. They do make things possible that are not possible with joins alone. They can eliminate the need for temporary tables, although a properly-defined temporary table may out-perform a straight subquery. People may code using all the bells and whistles available, saying that it's the software's job to properly optimize the execution. But until the optimizer rivals the human brain in terms of complexity, nothing can beat some good old-fashioned skull sweat in terms of convincing a computer to put out its best performance.
Plus, once you've coded the same sort of problem a time or two, it gets easier to wrap your brain around a scenario and crank out the code using the optimal correct aproach. Trying to convince a newbie that there is a method to the madness, there is a reason for doing things a certain way, that it's not a bug that one approach will outperform another— now there's a challenge.
Some features I see as going further to enhance security than to increase performance; views seem to fall into that category, and to a certain extent, stored routines. Triggers and cursors make many things possible, yet it's also possible to use these to code stuff that looks perfectly nice on paper, yet there is a perfectly simple solution using only basic SQL statements.
It's certainly worth exploring all the functionality, to find out what it can and cannot do, but also to find out what it does well, and what is better done the 'old way'.
If there are several ways to accomplish the same thing, my first inclination is to go with the one that is simplest to code. My philosophy is that if I arrange the syntax in a logical order with each link in the chain in its proper place, that this will make it that much easier for the server to optimally execute it.
I think that some people will code a subquery in preference to the equivalent join syntax mostly from a basic lack of understanding of how joins work. My personal preference is to code something as a join and avoid use of subqueries wherever possible. If the subquery functionality did not exist (and it still doesn't for a lot of users, but that's an entirely different issue) then people would perforce use joins.
Subqueries are not bad things. They do make things possible that are not possible with joins alone. They can eliminate the need for temporary tables, although a properly-defined temporary table may out-perform a straight subquery. People may code using all the bells and whistles available, saying that it's the software's job to properly optimize the execution. But until the optimizer rivals the human brain in terms of complexity, nothing can beat some good old-fashioned skull sweat in terms of convincing a computer to put out its best performance.
Plus, once you've coded the same sort of problem a time or two, it gets easier to wrap your brain around a scenario and crank out the code using the optimal correct aproach. Trying to convince a newbie that there is a method to the madness, there is a reason for doing things a certain way, that it's not a bug that one approach will outperform another— now there's a challenge.
Some features I see as going further to enhance security than to increase performance; views seem to fall into that category, and to a certain extent, stored routines. Triggers and cursors make many things possible, yet it's also possible to use these to code stuff that looks perfectly nice on paper, yet there is a perfectly simple solution using only basic SQL statements.
It's certainly worth exploring all the functionality, to find out what it can and cannot do, but also to find out what it does well, and what is better done the 'old way'.
posted by Bob Field at 12:40 PM
0 Comments:
Post a Comment
<< Home