22

I ask because so many of the questions I see in SQL amount to: "This is slow. How do I speed it up"? Or are tutorials stating "Do this this way and not that way as it's faster".

It seems to me that a large part of SQL is knowing just how an expression would be performed and from that knowledge chosing expression styles that perform better. This doesn't square with one aspect of declaritive programming - that of leaving the system to decide how best to perform the calculation with you just specifying what the calculation should produce.

Shouldn't an SQL engine not care about if you used in, exists or join if it is truly declarative shouldn't it just give you the correct answer in reasonable time if possible by any of the three methods? This last example is prompted by this recent post which is of the type mentioned in my opening paragraph.

Indexes

I guess the easiest example I could have used relates to creating an index for a table. The gumph here on w3schools.com even tries to explain it as something unseen by the user that is there for performance reasons. Their description seems to put SQL indices in the non-declarative camp and they are routinely added by hand for purely performance reasons.

Is it the case that their is somewhere an ideal SQL DB that is much more declarative than all the rest but because it is that good one doesn't hear about it?

  • @FrustratedWithFormsDesigner: I know exactly what that means. select whatever from sometable where FKValue in (select FKValue from sometable_2 where other_value = :param). It should be trivial to see how to restate that with an exists or a join. – Mason Wheeler Jun 3 '13 at 20:47
  • Using similar reasoning I guess regular expressions are a more declarative method of expression as I rarely see performance questions answered by "you should write it this way to get better performance". I am wracking my brains and can half remember some question to do with negative look-behind or ahead assertions in a slow regexp where the answer was to rewrite the regexp in a different way to do the same in less time. – Paddy3118 Jun 3 '13 at 21:16
  • Performance is an implementation detail. The performance of nearly any IN implementation could be comparable or better than EXISTS and JOIN if the query processor developers felt it was a priority. – JustinC Jun 4 '13 at 7:06
  • 1
    @JustinC, it seems to be more than a detail given the preponderance of performance oriented SQL questions and tips for a supposedly declarative language? – Paddy3118 Jun 4 '13 at 12:34
  • There is no clear definition of a declarative programming language, and so it's meaningless to talk about. Some languages are higher-level than others, that's all. – gardenhead Nov 6 '16 at 17:15
21

SQL is theoretically declarative. But you know what they say about the difference between theory and practice...

At its core, the concept of "declarative programming" has never been truly effective, and likely never will until we have an AI-based compiler that's capable of looking at code and answering the question "what is the intention of this code?" intelligently, in the same way that the person who wrote it would. At the heart of every declarative language is a whole bunch of imperative code trying frantically to solve that problem without the help of an AI.

Often it works surprisingly well, because the most common cases are common cases, which the people who wrote the language's implementation knew about and found good ways to handle. But then you run up against an edge case that the implementor didn't consider, and you see performance degrade quickly as the interpreter is forced to take the code much more literally and handle it in a less efficient manner.

  • 3
    Never truly effective? SQL, LINQ, Knockout.js, Prolog, ELM language. You may want to check again. I am using mostly declarative technologies at the moment. – brian Jun 3 '13 at 21:01
  • 5
    @brian: And all of them degenerate rather quickly when you happen upon an edge case that no one thought of. I suppose I should have said "never truly effective in the general case." – Mason Wheeler Jun 3 '13 at 21:06
  • When is your reply set to degrade seeing as how it's stored in a SQL Server database? :) I rarely hit an edge case in any of them that couldn't be solved within the framework. I see where you're coming from but the edge cases really don't cause me much pain for how beneficial and easy to reason about 99% of declarative code is. It's like saying Clojure or F# is bad because you had to use a mutable type to solve your problem. – brian Jun 3 '13 at 21:15
  • 11
    @brian: I rarely hit an edge case in any of them that couldn't be solved within the framework. Yeah, that's the whole point: having to figure out a way to solve them within the framework because the framework isn't smart enough to solve it for you the way you originally declared it. – Mason Wheeler Jun 3 '13 at 21:45
  • What about select ... for update? It seems an imperative command. – Jesvin Jose Mar 9 '15 at 9:56
6

I was thinking of this some days ago after an SQL optimization. I think we can agree that SQL is a "declarative language" in the definition of Wikipedia:

Programming paradigm that expresses the logic of computation without describing its control flow

If you think how many things are done behind the curtains (looking at statistics, deciding if an index is useful, going for a nested, merged or hash join, etc..etc..) we must admit that we give just an high level logic, and the database took care of all the low level control flow logic.

Also in this scenario, sometimes the database optimizer needs some "hints" from the user to give the best results.

Another common definition of "declarative" language is (I can't find an authorative source):

Programming paradigm that expresses the desired result of computation without describing the steps to achieve it (also abbreviated with "describe what, not how")

If we accept this definition, we encounter the issues described by the OP.

The first issue is that SQL give us multiple equivalent ways to define "the same result". Probably that's a necessary evil: the more expressive power we give to a language, the more it is likely to have different ways to express the same thing.

As an example, I've been asked once to optimize this query:

 SELECT Distinct CT.cust_type,  ct.cust_type_description 
   from customer c 
              INNER JOIN 
              Customer_type CT on c.cust_type=ct.cust_type;

Since the types were a lot less than the customer and there was an index on the cust_type on customer table, I've achieved a great improvement by rewriting it as:

 SELECT CT.cust_type,  ct.cust_type_description 
   from Customer_type CT
  Where exists ( select 1 from customer c 
                  Where c.cust_type=ct.cust_type);

In this specific case, when I asked the developer what he wanted to achieve he told me "I wanted all the customer types for which I had at least one customer", that incidentally is exactly how the optimizer query could be described.

So, if I could find an equivalent and more efficient query, why can't the optimizer do the same?

My best guess is that it is for two main reasons:

SQL expresses logic:

since SQL expresses high-level logic, would we really want the optimizer to "outsmart" us and our logic? I would enthusiastically shout "yes" if it was not for all the times I had to force the optimizer pick the most efficient execution path. I think that the idea could be to allow for the optimizer to do its best (also revising our logic) but give us an "hint mechanism" to come to the rescue when something go crazy (it would be like having the wheel+brakes in an autonomous car).

More choices = more time

Even the best RDBMS optimizer don't test ALL the possible execution paths, as they must be really fast: how good would be to optimize a query from 100ms to 10ms if I need to spend every time 100ms choosing the best path? And that's with the optimizer respecting our "high-level logic". If it should also test all the equivalent SQL queries the optimizer time could grow multiple times.

Another good example of query rewrite the no RDBMS is actually capable of doing is (from this interesting blog post)

SELECT t1.id, t1.value, SUM(t2.value)
  FROM mytable t1
       JOIN mytable t2
         ON t2.id <= t1.id
 GROUP BY t1.id, t1.value;

than can be written as this (Analytical functions required)

 SELECT id, value, SUM(t1.value) OVER (ORDER BY id)
   FROM mytable
  • 1
    The example of rewriting the join to an exists is interesting. One rule of thumb I try to impress on SQL developers is that use of DISTINCT is a code smell -- either the query, or the data model, is very possibly wrong, and a different approach should be sought. – David Aldridge Nov 7 '16 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.