A bit of a general question really - does SQL abdicate it's responsibility in terms of it's language processing? What I'm getting at is that 4GLs such as SQL typically invite programming in terms of what should be done, rather than how it should be done, which is where 3GLs (e.g. Java, C#, C, Latest-programming-language-of-the-day). Given that the invitation is typically to get the user to specify what should be achieved, is it the case that SQL abdicates it's responsibility in terms of optimisation and query plan generation?
As we see time and time again, how you specify what should be achieved can have massive impacts on the performance of the system. Don't get me wrong - I'm happy that's the case, because it means there's an incredible amount of depth to the domain, but I am wondering if really SQL should be aiming at taking that level of understanding and making it much less necessary.
Obviously, rules will always apply for data storage and effective use of the data engine (for example, storing your integers in varchar fields will always be sub-optimal) - but should the differences between the various types of join (and I am meaning MERGE, HASH and LOOP here - not INNER/OUTER/CROSS) become something that we actually don't care about? Should SQL generally be responsible for extracting the meaning from what's been written and creating the optimal execution plan that satisfies that meaning?
I know this is a bit of a waffly question, but it's something I've been thinking about recently and I find all your insights generally help me achieve a balanced view on my thoughts...
Edit -> Re-reading this, yes I am probably being a bit too harsh - but what I'm getting at is a more fundamental question about what SQL is and what SQL isn't, rather than any comment about any particular implementation. Definitely not saying the SQL Server QO sucks, and definitely not saying I could make a better job of it! :)
Matt, the short answer is that SQL has not yet lived up to its potential. SQL, and indeed any language meant to be declarative, is supposed to hide all technical details from the user/programmer. The user/programmer should be able to declare what they want and have no concern for the technical details.
But that is an ideal. SQL in general and SQL Server in particular has been slowly moving closer to that as it evolves. The optimizer has been getting better with every iteration of SQL Server, and this both lessens the need to understand the technical underpinnings at the same time it increases the opportunity to work with those underpinnings to truly maximize performance.
SQL in general and SQL Server in particular will continue to move closer to that ideal, but I do not think it will get there any time soon. Leaving out utopias filled with strong AI, in the near future there will always be things that the DBA will know and understand that the optimizer will not. The DBA will know the intent of the code being executed at a higher level than the machine, and this means they can use that information to provide hints. The DBA will also know what is likely to happen in the future far better than the machine, so they will be able to do things like design indexes for future use and allocate resources for what will be used.
I could be wrong, but I suspect for the forseeable future there will be a need for DBA/Developers to have a good knowledge of how SQL Server works under the hood to maximize that performance. And if in the more distant future we have strong AI that I can do the things a DBA does now, then I will start working towards my Microsoft Certified AI Trainer certification...
answered Apr 20 '10 at 01:09 PM
Having just been to SQLBits (www.sqlbits.com) in London last week and heard Conor Cunnigham talk about the optimiser and how it builds query plans I am happy that its there, built my people far more capable and intelligent at it than me. It takes components of your TSQL and parses 0,000's of alternatives before deciding on the best of the bunch and using it. He offered that its never going to give the best solution for a complex query as the number of options increases because evaluating every one would take too long, it simply takes what it thinks is a reasonable time to find a plan that is pretty good. This leaves me happy that it will do that, with reasonable consistency and I can worry about the storage and loading on the server etc as my DBA role rather than being a TSQL programmer. I have plenty to occupy my time on that bit, and frankly I enjoy that bit more too.
answered Apr 19 '10 at 06:39 PM
Heh... my feeling is that if you make something "Idiot Proof", only idiot's will use it. ;-)
answered Apr 24 '10 at 01:29 AM
Can I abdicate SQL of its responsibility?
answered Apr 20 '10 at 01:14 AM