How do you teach a thinking pattern? In my previous job I worked with a data analyst, who was really good at thinking about how our data correlates among itself. He was good at the data analysis, good at thinking up relationships, and good at coming up with complex comparisons.
However, he wrote some pretty funky SQL. We had him take a course in basics of MySQL, and from time to time I'd take a gander in the slow query logs and pick a few queries and point out the good and bad things. Most of these are optimization tips, such as "Indexes don't apply to columns when you're applying a function to that column".
But it's hard to try to think outside of your own box, and even running EXPLAIN on every query won't necessarily tell you how you can fix a query. The problem is that there's no way to know if you have optimized the query as much as possible. Even experienced DBAs
I was often frustrated when repeating concepts -- though I always took a different approach, since I find that if I have to repeat myself it means that it didn't sink in with the way I said it before. But I know many people, myself included, have managed to learn how to think differently.
The question, I guess, is how do you help someone else think differently?
flajann makes some very good
flajann makes some very good points. It kinda reminds me of Joel's Law of Leaky Abstractions...
http://www.joelonsoftware.com/articles/LeakyAbstractions.html
-Larry
Now that I know I can post,
Now that I know I can post, let me state at least some of what I stated before.
The thinking patterns of a person is something that is learned over time and is driven by success and motivation and curiosity. "Success", of course, does not necessairly mean the greatest success to be had, but some success.
Outside of people like you and I, most get rather lazy about thinking and once they become somewhat successful are reluctant to improve further.
Optimizing SQL require one of two approaches: (a) an intimate knowledge of how the underlying database works -- basically, how it will parse the SQL, how it will attempt to optimize the SQL, how it will attempt to match it to indexes and the like. The other approach is by "trial and error".
Alas, SQL, if my may say so myself, sucks as a language. Basically, it works hard at trying to hide all the low-level machinations of the database system; yet you can't write good SQL unless you deal with those low-level machinations!!! The very fact that it hides these details makes it even trickier to optimize, because its "optimizer" is trying to be a "one size fits all", and it has to guess about a lot.
Indeed, in optimizing SQL, not only are you dealing with the low-level machinations, but you are also dealing with the default assumptions of its optimizer, as well! It's kinda insane having to work around both.
So, that certain data analyist will never be able to deal with all of these complications. He is to be understood, actually, because he is actually trying to use SQL in the way it was intended -- so as to not have to deal with all the low-level details that he shouldn't have to deal with anyway.
Alas, this is really the failing of many, if not most computer languages, ORMs, and other systems designed to "simplify" and to "hide complexity" -- to really effectively use them eventually you have to understand the complexity it's trying to hide you from, and worse -- how it's trying to hide you from it!!!!!
It's amazing how little has changed over the years. I ran into these same issues dealing with Microsoft's infamous MFC framework, and even Java. I had to deal with this in my C and C++ days, and also had to deal with it when I wrote a lot of PHP code.
So, for just kicking it around, the SQL language it great! But when you get serious that all the limitations comes to the fore. And this is true of nearl everything in computerdom.
My, this came out quite a bit different from what I wote before!
I lost a lengthy comment due
I lost a lengthy comment due to issues with getting OpenID working. Arrrgh!
"The problem is that
"The problem is that there’s no way to know if you have optimized the query as much as possible"
However, there are ways to tell if you have optimised it as much as you need to.