In what sense SQL is 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).
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.
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.
I can understand wanting an unlogged table for the speedup (missing the last sec of weather data won't cause huge problems and I'm more forgiving of missing data when a whole machine fails), but just to be able to randomly drop things seems scary.
Also, I thought you were joking, but you're not http://dev.mysql.com/doc/refman/5.0/en/blackhole-storage-eng... bu
What exactly don't you like about SQL?
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.
What? Not having a value is not having a value, it's not false. It's also a fairly standard concept. http://c2.com/cgi/wiki?ThreeValuedLogic
> The fact that the result-set must be in the form of a table instead of a graph
Don't use a hammer to screw in a screw. There are graph databases (which support ACID, btw) out there for a reason.
> 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.
I don't get this. It is improving and it's stood the test of time because it solves a problem and solves it well.
> 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 quite get you. When a SQL query fails, it's not because you need to understand the database arch better. It's like when a C program fails, you don't need to understand GCC to fix it. You do however, need to understand how things are being used to pull the most performance out of it as possible, just like a regular language and a compiler.
* SQL not being relational algebra
* SQL implementations being incompatible
If you have not experienced problems as a result of these core issues, it honestly makes me feel you must not have a lot of experience -- at least not in diverse projects and environments.
That you think picking one database is some sort of viable solution in the general case amplifies that feeling. That is so often simply not an option, and even where supporting only one database at a time is an option, you absolutely cannot guarantee that you will not have to migrate later. I've been through that pain many times, it is a real-world problem.
I would not expect a new database query language or NoSQL databases to do this better.