6 ms·

I thought this was a very good article, unusually so for a blog post about databases. I do take issue, however, with the idea that there's a beauty to SQL. Ther

by jfb 13y ago

I thought this was a very good article, unusually so for a blog post about databases. I do take issue, however, with the idea that there's a beauty to SQL. There's a beauty in the conceptual model, but you have to squint sideways to see it through the deeply hostile syntax.

chris_wot 13y ago
The conceptual model is great, but IMHO a little impractical. Anyone who has ever had to trick the optimiser into choosing a better plan by changing the semi join from WHERE EXISTS to NOT IN (or vice versa) will appreciate what I'm saying :-)
- jfb 13y ago
  Lovely conceptual models always founder on the shoals of terrible implementation.
  - chris_wot 13y ago
    Not necessarily. It's almost impossible for a CBO to get the best query plan for every query under every load under all data distributions. But the relational model is still beautiful, I find its limitations are minor in the grand scheme of things.
    - jeffdavis 13y ago
      "It's almost impossible for a CBO to get the best query plan for every query under every load under all data distributions." It's also impossible for a human to do it. At least the optimizer will be awake at 3 in the morning and able to respond to data changes; the human will be asleep.
      - chris_wot 13y ago
        I'm not disagreeing :-) Although the poor DBA on pager may not agree with you.
- lukaseder 13y ago
  That really depends on the database. With MySQL? Yes, you have to apply hundreds of tricks. With Oracle? Chances are, that the CBO knows much better than you how to transform your SQL.
  - chris_wot 13y ago
    Tell that to all those people still adding rule hints to their queries :-)
    - lukaseder 13y ago
      Good point. It's been some time though, since I've last been using anything below Oracle 12c ;-)
- jeffdavis 13y ago
  The problem of a query optimizer doesn't go away by not having one. They were invented for a reason: data changes. Data volume changes and data distribution changes. If your algorithms don't change to suit the inputs, performance will still fall off a cliff. That's a fundamental problem and not unique to SQL. And this really only matters for somewhat "interesting" queries anyway. It's not like the database will all of a sudden change an "UPDATE foo SET x = x + 1 WHERE id = 13" into a 14-table join. In practice, you have many queries adapting nicely to changing data, and then a couple where they get some estimates wrong and drive you crazy. But just because you are able to fix the plans in some of those instances doesn't make it a valid comparison. You have to compare it against hand crafting all of the plans (or writing equivalent algorithms) and the results of doing that. I suspect that in most cases, more plans will drive you crazy if you choose the plans rather than letting an optimizer do it. Remember, the data is going to change once you get in production.
  - chris_wot 13y ago
    Of course. Sorry, I didn't mean to imply CBOs are bad or unnecessary. They are fundamental and necessary. Tuning queries is of course something that needs to be reviewed as data changes - pruning unnecessary indexes is often as importent as adding new ones! Hopefully 95% or more of queries don't need to be nudged :-)
clarkevans 13y ago
I'm not sure the "Relational Model" is even a great conceptual model for common business or analytical processing. I think SQL is ugly because the model is a poor fit; if the model was solid, a clean syntax would follow naturally. As for alternatives, MDX has superseded it for core "pivot table" analytics. For application processing, it seems modern document/hierarchical databases are good for many transactional needs. What modern relational databases have... is a very intuitive abstract storage model. That's awesome. We've been working on an alternative "navigational model" (inspired from CODASYL and ORMs) based on this storage model. Our experimental query language is at http://htsql.org http://htsql.org Our critique of SQL is at http://htsql.org/doc/overview.html#why-not-sql http://htsql.org/doc/overview.html#why-not-sql
- lukaseder 13y ago
  I'm sure you'll agree with Stonebraker's recent (actually, not so recent) claims that the relational model is "all wrong": http://blog.jooq.org/2013/08/24/mit-prof-michael-stonebraker-the-traditional-rdbms-wisdom-is-all-wrong/ http://blog.jooq.org/2013/08/24/mit-prof-michael-stonebraker... htsql seems very interesting. I should soon blog about your product and critique. Have you been publishing elsewhere?
  - bcoates 13y ago
    That's not complaining about the relational model at all, he's complaining about implementation details of common RDBMSes being obsolete, as they're based on row-oriented storage, slow disks, small memory, and limited concurrency. Things like Amazon Redshift are still relational but make radically different decisions on most of those points. Edit: that talk is well worth reading, btw. I wonder why he completely ignores flash though?
  - GFischer 13y ago
    Thanks for linking to that talk, it deserves a submission all of its own.
    - pkj 13y ago
einhverfr 13y ago
I disagree. There are issues with SQL (how many things can NULL mean?) but there is also beauty in it. Every language has issues. You don't see the beauty in SQL until you try to get as much bang for the buck out of specific queries. Then the idea that you can think in terms of math and sets really shines. You can do a lot in SQL which would require a lot more app code to accomplish, and the SQL will be more elegant and easier to maintain.
- twerquie 13y ago
  > There are issues with SQL (how many things can NULL mean?) Please elaborate, I can only think of one.
  - dragonwriter 13y ago
    The two main meanings of NULL are "Does not exist" and "Exists but is unknown", though there are more.
    - yawaramin 13y ago
      Also, `we don't know if it exists'.
    - epo 13y ago
      NULL is a marker that means this data value does not exist in the database. Your second, and any other 'meanings' are a misuse of NULL, which is your fault, not SQLs
      - dragonwriter 13y ago
        > NULL is a marker that means this data value does not exist in the database. Both of the uses I stated are data values that do not exist in the database. > Your second, and any other 'meanings' are a misuse of NULL No, they aren't. They are different, and substantively different, reasons why data does not exist in a database. One is the value does not exist in the domain of which the DB is model, and one is that the value exists in that domain but it is not known. (Others have pointed out the additional case of "we do not know if it exists", which is ambiguity between those two cases.) These two substantially different meanings of NULL were recognized fairly early in the history of the relational model by the creator of the model (E.F. Codd), who proposed refining the model by having separate markers for each of those two cases.
mathattack 13y ago
I found the relational beauty in being able to "store every unique value just once." It resonated with the programming concept of "write a function so that you don't need to change things in many places." This in turn gave me a lens to viewing how businesses operate independent of what systems you put on top of the data. Does this mean SQL is pretty? Not really, but it gets the job done. Though I did have to spend a lot of time optimizing SQL to force certain query optimizations. Does this mean relational should be used everywhere? No, but it has a distinct place. I think beer is beautiful too, but I won't drink it for breakfast with the in-laws.

by jfb 13y ago

chris_wot 13y ago

The conceptual model is great, but IMHO a little impractical. Anyone who has ever had to trick the optimiser into choosing a better plan by changing the semi join from WHERE EXISTS to NOT IN (or vice versa) will appreciate what I'm saying :-)

jfb 13y ago
Lovely conceptual models always founder on the shoals of terrible implementation.
- chris_wot 13y ago
  Not necessarily. It's almost impossible for a CBO to get the best query plan for every query under every load under all data distributions. But the relational model is still beautiful, I find its limitations are minor in the grand scheme of things.
  - jeffdavis 13y ago
    "It's almost impossible for a CBO to get the best query plan for every query under every load under all data distributions." It's also impossible for a human to do it. At least the optimizer will be awake at 3 in the morning and able to respond to data changes; the human will be asleep.
    - chris_wot 13y ago
      I'm not disagreeing :-) Although the poor DBA on pager may not agree with you.
lukaseder 13y ago
That really depends on the database. With MySQL? Yes, you have to apply hundreds of tricks. With Oracle? Chances are, that the CBO knows much better than you how to transform your SQL.
- chris_wot 13y ago
  Tell that to all those people still adding rule hints to their queries :-)
  - lukaseder 13y ago
    Good point. It's been some time though, since I've last been using anything below Oracle 12c ;-)
jeffdavis 13y ago
The problem of a query optimizer doesn't go away by not having one. They were invented for a reason: data changes. Data volume changes and data distribution changes. If your algorithms don't change to suit the inputs, performance will still fall off a cliff. That's a fundamental problem and not unique to SQL. And this really only matters for somewhat "interesting" queries anyway. It's not like the database will all of a sudden change an "UPDATE foo SET x = x + 1 WHERE id = 13" into a 14-table join. In practice, you have many queries adapting nicely to changing data, and then a couple where they get some estimates wrong and drive you crazy. But just because you are able to fix the plans in some of those instances doesn't make it a valid comparison. You have to compare it against hand crafting all of the plans (or writing equivalent algorithms) and the results of doing that. I suspect that in most cases, more plans will drive you crazy if you choose the plans rather than letting an optimizer do it. Remember, the data is going to change once you get in production.
- chris_wot 13y ago
  Of course. Sorry, I didn't mean to imply CBOs are bad or unnecessary. They are fundamental and necessary. Tuning queries is of course something that needs to be reviewed as data changes - pruning unnecessary indexes is often as importent as adding new ones! Hopefully 95% or more of queries don't need to be nudged :-)

clarkevans 13y ago

I'm not sure the "Relational Model" is even a great conceptual model for common business or analytical processing. I think SQL is ugly because the model is a poor fit; if the model was solid, a clean syntax would follow naturally. As for alternatives, MDX has superseded it for core "pivot table" analytics. For application processing, it seems modern document/hierarchical databases are good for many transactional needs. What modern relational databases have... is a very intuitive abstract storage model. That's awesome. We've been working on an alternative "navigational model" (inspired from CODASYL and ORMs) based on this storage model. Our experimental query language is at http://htsql.org http://htsql.org Our critique of SQL is at http://htsql.org/doc/overview.html#why-not-sql http://htsql.org/doc/overview.html#why-not-sql

lukaseder 13y ago
I'm sure you'll agree with Stonebraker's recent (actually, not so recent) claims that the relational model is "all wrong": http://blog.jooq.org/2013/08/24/mit-prof-michael-stonebraker-the-traditional-rdbms-wisdom-is-all-wrong/ http://blog.jooq.org/2013/08/24/mit-prof-michael-stonebraker... htsql seems very interesting. I should soon blog about your product and critique. Have you been publishing elsewhere?
- bcoates 13y ago
  That's not complaining about the relational model at all, he's complaining about implementation details of common RDBMSes being obsolete, as they're based on row-oriented storage, slow disks, small memory, and limited concurrency. Things like Amazon Redshift are still relational but make radically different decisions on most of those points. Edit: that talk is well worth reading, btw. I wonder why he completely ignores flash though?
- GFischer 13y ago
  Thanks for linking to that talk, it deserves a submission all of its own.
  - pkj 13y ago

einhverfr 13y ago

I disagree. There are issues with SQL (how many things can NULL mean?) but there is also beauty in it. Every language has issues. You don't see the beauty in SQL until you try to get as much bang for the buck out of specific queries. Then the idea that you can think in terms of math and sets really shines. You can do a lot in SQL which would require a lot more app code to accomplish, and the SQL will be more elegant and easier to maintain.

twerquie 13y ago
> There are issues with SQL (how many things can NULL mean?) Please elaborate, I can only think of one.
- dragonwriter 13y ago
  The two main meanings of NULL are "Does not exist" and "Exists but is unknown", though there are more.
  - yawaramin 13y ago
    Also, `we don't know if it exists'.
  - epo 13y ago
    NULL is a marker that means this data value does not exist in the database. Your second, and any other 'meanings' are a misuse of NULL, which is your fault, not SQLs
    - dragonwriter 13y ago
      > NULL is a marker that means this data value does not exist in the database. Both of the uses I stated are data values that do not exist in the database. > Your second, and any other 'meanings' are a misuse of NULL No, they aren't. They are different, and substantively different, reasons why data does not exist in a database. One is the value does not exist in the domain of which the DB is model, and one is that the value exists in that domain but it is not known. (Others have pointed out the additional case of "we do not know if it exists", which is ambiguity between those two cases.) These two substantially different meanings of NULL were recognized fairly early in the history of the relational model by the creator of the model (E.F. Codd), who proposed refining the model by having separate markers for each of those two cases.

mathattack 13y ago

I found the relational beauty in being able to "store every unique value just once." It resonated with the programming concept of "write a function so that you don't need to change things in many places." This in turn gave me a lens to viewing how businesses operate independent of what systems you put on top of the data. Does this mean SQL is pretty? Not really, but it gets the job done. Though I did have to spend a lot of time optimizing SQL to force certain query optimizations. Does this mean relational should be used everywhere? No, but it has a distinct place. I think beer is beautiful too, but I won't drink it for breakfast with the in-laws.