x

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

more ▼

asked Apr 19, 2010 at 06:11 PM in Default

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

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.
Apr 20, 2010 at 09:46 AM Blackhawk-17
@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'!
Apr 20, 2010 at 10:22 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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

more ▼

answered Apr 20, 2010 at 01:09 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

I'm just accepting that straight off because it mirrors my thoughts exactly - thanks for taking the time to (once again) share your wise words...
Apr 20, 2010 at 02:36 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 19, 2010 at 06:39 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 74 78 108

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...
Apr 19, 2010 at 06:54 PM Matt Whitfield ♦♦
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.
Apr 19, 2010 at 07:19 PM Fatherjack ♦♦
Good answer Jack.
Apr 19, 2010 at 07:31 PM CirqueDeSQLeil
@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).
Apr 19, 2010 at 07:34 PM Matt Whitfield ♦♦
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! :)
Apr 19, 2010 at 07:36 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Apr 24, 2010 at 01:29 AM

Jeff Moden gravatar image

Jeff Moden
1.7k 2 4 8

Also, they will build better idiots.
Apr 24, 2010 at 02:11 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

Can I abdicate SQL of its responsibility?

more ▼

answered Apr 20, 2010 at 01:14 AM

CirqueDeSQLeil gravatar image

CirqueDeSQLeil
4k 10 11 15

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!
Apr 20, 2010 at 03:39 AM Matt Whitfield ♦♦
LOL - Thanks...
Apr 20, 2010 at 11:45 AM CirqueDeSQLeil
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x713
x40
x36
x20
x12

asked: Apr 19, 2010 at 06:11 PM

Seen: 1814 times

Last Updated: Apr 20, 2010 at 10:30 AM