current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 6 down vote favorite
1

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?

share|improve this question
    
@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
up vote 9 down vote accepted

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.

share|improve this answer
1  
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
3  
@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
4  
@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. – aitchnyu Mar 9 '15 at 9:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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