4 ms·

Well, I like SQL, but let me play devil's advocate and guess at some of the ways people may consider it "bad": - SQL queries flatten structured and hierarchica

by Joeri 12y ago

Well, I like SQL, but let me play devil's advocate and guess at some of the ways people may consider it "bad": - SQL queries flatten structured and hierarchical relationships into unstructured rows, often requiring very creative join operations to get at the exact set of data you want, after which you then have to convert everything back to the structured data you wanted in the first place. ORM's are a leaky abstraction on top of that, which means that when you use them you end up dealing with two problems instead of one. - SQL is superficially simple, but at scale the simplicity is a lie. Thhe performance trade-offs you make require you to understand the internals of the database you're running on and how it interprets the query, and often you feel like you're dealing with a too high level API. Often you're resigned to "tricks" to force the engine to do the right thing, like optimizer hints, special index types or denormalized data. - SQL has no ability to express relexation of the ACID principles. If you're streaming weather data into a SQL database, it could be ok to drop inserts here and there to gain throughput for the data analysis operations. SQL has no abilities to express such a thing (except in db-specific hacks).

Pacabel 12y ago
Even those reasons are really stretching for it these days. There are numerous ways of dealing with hierarchical or otherwise structured data using relational DBs. Support for recursive CTEs is becoming more common, which makes such querying quite clean and simple. And with many relational systems offering support for storing and manipulating XML and JSON data, storing more structured data becomes a non-issue. I think that SQL managing to hide a lot of the underlying implementation details is one of its strongest points. In many situations the defaults are more than sufficient, saving developers and users a significant time. Yes, there are situations where more control over the exact handling is needed, but it gives ways of managing this. At least a simplified, high-level abstraction is offered; many NoSQL systems immediately force you to take a very low-level approach for even the simplest of queries. And I don't see the ability to intentionally lose data as something that's good, either. The database should be concerned with doing everything in its power to protect and ensure the integrity of the data it has been given. If it's fine to lose some of that data, then whatever or whoever is providing that data should just not provide it to the DB in the first place. So while some people may present those as possible disadvantages of SQL, I just don't think the arguments really hold up upon further scrutiny.
Pxtl 12y ago
I know we're not supposed to comment just to say you agree with the above post, but: OMG yes! Everything you just wrote. Relational algebra is a great concept for a data-store language, but why are we stuck with the COBOL of data-store languages?
- jimktrains2 12y ago
  I thought COBOL was hated because it's very verbose, the programs written in it are very complex because of the domain, and contain warts because they're so old. What exactly don't you like about SQL?
  - pessimizer 12y ago
    I think COBOL is just being thrown around here as a euphemism for old people used this. ORMs are 1000x more verbose than SQL. Map-reduce queries are no shorter, require you to denormalize your data ahead of time in frequently nonobvious (and always inflexible) ways, and have vastly different syntaxes and semantics depending on which product you're using. They're definitely new, though.
  - Pxtl 12y ago
    All the stuff the grandparent post mentioned. Insane 3-value boolean logic. The fact that the result-set must be in the form of a table instead of a graph of data (when the client code wants a graph). Even just moving the "select" keyword to the end of the query so query-writing tools can provide some autocomplete. Even simple things like providing a simple syntax for walking foreign key relations instead of cumbersome Join operations. Using any ORM worth its salt, I can say customer.company, whereas in native SQL I have to say "from customer join company on customer.companyid = company.companyid". To me, SQL is like Common LISP. A fantastic invention of a bygone era that has stood the test of time... but is stubbornly resistant to real improvement and shows too many warts of its age. And again, as the grandparent said, the cases where the abstraction fails and you end up having to figure out how exactly it's implementing your query. Abstraction failures make a tool worse-than-useless, because it means I have to not only understand the problem the tool is trying to solve, but I have to understand the tool and the quirks of its handling of the problem. I don't want to imply that ORMs are better (the last thing we need is another abstraction layer) or NoSQL is better (throwing the baby out with the bath-water there) just that SQL is old and has not seen the kind of improvement that other languages have seen.
jimktrains2 12y ago
> SQL has no ability to express relexation of the ACID principles. If you're streaming weather data into a SQL database, it could be ok to drop inserts here and there to gain throughput for the data analysis operations. SQL has no abilities to express such a thing (except in db-specific hacks). Why should the language control the underlying data store to that level? Also, SQL itself is not defined by or defines ACID principles. You can (and some people do) run SQL over very non-ACID databases (everyone using MyISAM or SQLite?) I personally feel that tying the data-store so close to the query language is a recipe for unneeded complexity. Though, the transaction isolation levels may be close to what you're talking about (though, I don't think there is anyway to tell a db like PostgreSQL "Go ahead, lose this. I don't care about it".) > SQL is superficially simple, but at scale the simplicity is a lie. Thhe performance trade-offs you make require you to understand the internals of the database you're running on and how it interprets the query, and often you feel like you're dealing with a too high level API. Often you're resigned to "tricks" to force the engine to do the right thing, like optimizer hints, special index types or denormalized data. Please tell me of anything where it isn't true that the more intimately you know your tools the better you can use them. Quite honestly most query planners are really friggin' good at what they do (just like a compiler, or are you a person that still claims hand tune assembly is the answer to everything). EDIT: PostgreSQL does allow unlogged tables that are not crash safe, though I don't think they're randomly lose rows, but are faster. So there are some semantics that allow you to control that, but they are probably vendor-specific (getting into my complexity argument). Thanks Myon on #postgresql for pointing that out.
- AlterEgo20 12y ago
  There is Asynchronous Commit feature in postgres, that allows you to gain performance by allowing postgres to lose some commited transactions. It can be controlled on transaction level and is exactly :"Go ahead, lose this. I don't care about it". Manual: http://www.postgresql.org/docs/current/static/wal-async-commit.html http://www.postgresql.org/docs/current/static/wal-async-comm...
  - jimktrains2 12y ago
    Like an unlogged table that's only not crash safe. I think GP was talking about rows actually not being written to the db during normal circumstances. Maybe I'm misunderstanding what GP is asking for.

by Joeri 12y ago

Pacabel 12y ago

Even those reasons are really stretching for it these days. There are numerous ways of dealing with hierarchical or otherwise structured data using relational DBs. Support for recursive CTEs is becoming more common, which makes such querying quite clean and simple. And with many relational systems offering support for storing and manipulating XML and JSON data, storing more structured data becomes a non-issue. I think that SQL managing to hide a lot of the underlying implementation details is one of its strongest points. In many situations the defaults are more than sufficient, saving developers and users a significant time. Yes, there are situations where more control over the exact handling is needed, but it gives ways of managing this. At least a simplified, high-level abstraction is offered; many NoSQL systems immediately force you to take a very low-level approach for even the simplest of queries. And I don't see the ability to intentionally lose data as something that's good, either. The database should be concerned with doing everything in its power to protect and ensure the integrity of the data it has been given. If it's fine to lose some of that data, then whatever or whoever is providing that data should just not provide it to the DB in the first place. So while some people may present those as possible disadvantages of SQL, I just don't think the arguments really hold up upon further scrutiny.

Pxtl 12y ago

I know we're not supposed to comment just to say you agree with the above post, but: OMG yes! Everything you just wrote. Relational algebra is a great concept for a data-store language, but why are we stuck with the COBOL of data-store languages?

jimktrains2 12y ago
I thought COBOL was hated because it's very verbose, the programs written in it are very complex because of the domain, and contain warts because they're so old. What exactly don't you like about SQL?
- pessimizer 12y ago
  I think COBOL is just being thrown around here as a euphemism for old people used this. ORMs are 1000x more verbose than SQL. Map-reduce queries are no shorter, require you to denormalize your data ahead of time in frequently nonobvious (and always inflexible) ways, and have vastly different syntaxes and semantics depending on which product you're using. They're definitely new, though.
- Pxtl 12y ago
  All the stuff the grandparent post mentioned. Insane 3-value boolean logic. The fact that the result-set must be in the form of a table instead of a graph of data (when the client code wants a graph). Even just moving the "select" keyword to the end of the query so query-writing tools can provide some autocomplete. Even simple things like providing a simple syntax for walking foreign key relations instead of cumbersome Join operations. Using any ORM worth its salt, I can say customer.company, whereas in native SQL I have to say "from customer join company on customer.companyid = company.companyid". To me, SQL is like Common LISP. A fantastic invention of a bygone era that has stood the test of time... but is stubbornly resistant to real improvement and shows too many warts of its age. And again, as the grandparent said, the cases where the abstraction fails and you end up having to figure out how exactly it's implementing your query. Abstraction failures make a tool worse-than-useless, because it means I have to not only understand the problem the tool is trying to solve, but I have to understand the tool and the quirks of its handling of the problem. I don't want to imply that ORMs are better (the last thing we need is another abstraction layer) or NoSQL is better (throwing the baby out with the bath-water there) just that SQL is old and has not seen the kind of improvement that other languages have seen.

jimktrains2 12y ago

> SQL has no ability to express relexation of the ACID principles. If you're streaming weather data into a SQL database, it could be ok to drop inserts here and there to gain throughput for the data analysis operations. SQL has no abilities to express such a thing (except in db-specific hacks). Why should the language control the underlying data store to that level? Also, SQL itself is not defined by or defines ACID principles. You can (and some people do) run SQL over very non-ACID databases (everyone using MyISAM or SQLite?) I personally feel that tying the data-store so close to the query language is a recipe for unneeded complexity. Though, the transaction isolation levels may be close to what you're talking about (though, I don't think there is anyway to tell a db like PostgreSQL "Go ahead, lose this. I don't care about it".) > SQL is superficially simple, but at scale the simplicity is a lie. Thhe performance trade-offs you make require you to understand the internals of the database you're running on and how it interprets the query, and often you feel like you're dealing with a too high level API. Often you're resigned to "tricks" to force the engine to do the right thing, like optimizer hints, special index types or denormalized data. Please tell me of anything where it isn't true that the more intimately you know your tools the better you can use them. Quite honestly most query planners are really friggin' good at what they do (just like a compiler, or are you a person that still claims hand tune assembly is the answer to everything). EDIT: PostgreSQL does allow unlogged tables that are not crash safe, though I don't think they're randomly lose rows, but are faster. So there are some semantics that allow you to control that, but they are probably vendor-specific (getting into my complexity argument). Thanks Myon on #postgresql for pointing that out.

AlterEgo20 12y ago
There is Asynchronous Commit feature in postgres, that allows you to gain performance by allowing postgres to lose some commited transactions. It can be controlled on transaction level and is exactly :"Go ahead, lose this. I don't care about it". Manual: http://www.postgresql.org/docs/current/static/wal-async-commit.html http://www.postgresql.org/docs/current/static/wal-async-comm...
- jimktrains2 12y ago
  Like an unlogged table that's only not crash safe. I think GP was talking about rows actually not being written to the db during normal circumstances. Maybe I'm misunderstanding what GP is asking for.