question

Matt Whitfield avatar image
Matt Whitfield asked

Does SQL abdicate it's responsibility?

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! :)

sqlexecution-planoptimizationprogrammingquery-plan
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image Blackhawk-17 commented ·
Matt, I think you are being a little harsh. The optimizer does rewrite many portions of a query based on what you provide and babysits you through to a better execution (that sounds bizarre doesn't it?). For the most part you don't need to worry about MERGE, HASH and LOOP until you are trying to pull the absolute last bit of performance out of a query - not for the faint of heart or uninformed. I actually think SQLs problem lies in its flexibility. Too many options leads to exponentially increased chances of making a poor decision. I would love if SQL was more natural language than it is.
1 Like 1 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@Blackhawk - I'm not really saying it does - I'm just asking people's opinions as to whether SQL lives up to what it says on the tin. I think it does a pretty good job most of the time... And, most certainly, in terms of language use it has huge longevity, so it's definitely 'doing it right'!
0 Likes 0 ·

4 Answers

·
TimothyAWiseman avatar image
TimothyAWiseman answered

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...

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

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.

7 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
OK, I see what you are saying. That depends on the assistant having experience with nails and hammers though. If you take it further back then you need something heavy and hard that will drive a metal spike into wood. You could end up with a cooker and a javelin ... (if B+Q did sporting goods, :-/ ) I guess it depends how much you trust them and your experience with their judgement to-date.
2 Likes 2 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
I'd have liked to have been able to do SQL Bits, I must say... I think though, the dilemma is more about what it's trying to achieve - the QO at the moment finds the best way to execute what you have written, *in the way that you have written it* - and it's the italic bit that's important for me - in that the way you write it shouldn't matter. For example, if you went in to B&Q and asked for 'one of those things you bash nails with' you would end up with a hammer, because the meaning of what you have asked for was clear from what you said...
0 Likes 0 ·
CirqueDeSQLeil avatar image CirqueDeSQLeil commented ·
Good answer Jack.
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@Jack - ROFL - actually, probably B&Q was a bad example, as I am not sure if I could name many places where the number of clueless people per square foot was higher (except the Inland Revenue).
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Although... if a SQL implementation (SQL Server or not) was to achieve try abstraction of the what from the how, then you would expect that SQL implementation to have experience of nails and hammers... in the data sense! :)
0 Likes 0 ·
Show more comments
CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

Can I abdicate SQL of its responsibility?

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
You could do... I think you'd have to introduce a 'meh' error though... SELECT * FROM [sys].[columns] - Error 42: Meh. Congrats on the 2K btw!
1 Like 1 ·
CirqueDeSQLeil avatar image CirqueDeSQLeil commented ·
LOL - Thanks...
0 Likes 0 ·
Jeff Moden avatar image
Jeff Moden answered

but I am wondering if really SQL should be aiming at taking that level of understanding and making it much less necessary.

Heh... my feeling is that if you make something "Idiot Proof", only idiot's will use it. ;-)

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image TimothyAWiseman commented ·
Also, they will build better idiots.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.