3 ms·

Relational databases have their place, and lots of problems fit onto a relational database very well. But that doesn't mean SQL is a good language. SQL is poor

Relational databases have their place, and lots of problems fit onto a relational database very well. But that doesn't mean SQL is a good language. SQL is poor language by many measures, and to make matters worse, nearly all implementations of SQL have all sorts of procedural extensions that just doesn't fit well with declarative relational programming. Also mathematical purity doesn't imply maintainable code. I'm also not really sure that real life SQL databases stick too closely to their mathematical foundations. And yeah, the optimiser does work, if you know how to play it's games. Yesterday, I was working on a really slow query (13 seconds), and I restructured it in such a way that produced exactly the same output and shouldn't really have had any impact on the performance. However, suddenly the optimiser kicked in and started doing what it should have done in the first place, and the runtime went down to about 0.1s. Note that this isn't just a problem with SQL, you get the same kind of issues in anything declarative or functional, where you're relying on the compiler to select the appropriate algorithm. Awesome when it works... nightmare when it doesn't. Though a more fundamental problem with current SQL relational databases is locking. If you want an entity from a document/object database, you only need to lock that object, but a given entity stored normally in a SQL DB may be scattered all over the database in lots of tables, requiring loads of various kinds of lock. Plus, SQL acquires locks implicitly in whatever order it feels like (which may be optimiser dependent), which causes deadlocks. Avoiding this requires careful planning and denormalisation, which kills of a lot of the advantages of using SQL in the first place.

jeltz 15y ago
Your gripe about locks seems quite exaggerated. At least compared to my own experience. Locks are a problem, yes, but not more so than most systems. Most modern SQL databases support MVCC (multi-version concurrency control) which means they do not have to take any read locks, so your arguments really only applies to UPDATE where I do not think any database supports explicit order for the locking. Deadlocks in databases are generally detected which causes one transaction to abort. So they are not as harmful as in some other environments. Databases always in MVCC: PostgreSQL, MySQL+InnoDB, Oracle (if I remember correctly) Databases with optional MVCC: MS SQL, SQLite EDIT: Fixed some typos.
- cameronh90 15y ago
  It's true that MVCC does mitigate some of the problems, but in a write-heavy OLTP database (such as the one I'm working on now) it's still a significant issue. I also am not aware of any database that supports explicit ordering for the locks, but it's only ever really an issue when using a relational database since you have to lock multiple tables for a single entity. I agree that deadlocks aren't as harmful, but they are a pain and do hurt performance, so it's another thing you have to worry about.
  - jeltz 15y ago
    I agree with you about that writing write-heavy applications in SQL is much more painful than writing read-heavy. I have been hit by various types of deadlocks including the annoying foreign key deadlocks. In PostgreSQL 9.2 they are working on removing false (as in they are not necessary to preserve consistency) foreign key deadlocks so they should be less common if that patch lands.
  - leif 15y ago
    MySQL with TokuDB handles write-heavy OLTP workloads well, even with multiple complicated indexes. Since it uses Fractal Tree indexing, writes are so fast that locks aren't held long enough for this to become a problem.

jeltz 15y ago

Your gripe about locks seems quite exaggerated. At least compared to my own experience. Locks are a problem, yes, but not more so than most systems. Most modern SQL databases support MVCC (multi-version concurrency control) which means they do not have to take any read locks, so your arguments really only applies to UPDATE where I do not think any database supports explicit order for the locking. Deadlocks in databases are generally detected which causes one transaction to abort. So they are not as harmful as in some other environments. Databases always in MVCC: PostgreSQL, MySQL+InnoDB, Oracle (if I remember correctly) Databases with optional MVCC: MS SQL, SQLite EDIT: Fixed some typos.

cameronh90 15y ago
It's true that MVCC does mitigate some of the problems, but in a write-heavy OLTP database (such as the one I'm working on now) it's still a significant issue. I also am not aware of any database that supports explicit ordering for the locks, but it's only ever really an issue when using a relational database since you have to lock multiple tables for a single entity. I agree that deadlocks aren't as harmful, but they are a pain and do hurt performance, so it's another thing you have to worry about.
- jeltz 15y ago
  I agree with you about that writing write-heavy applications in SQL is much more painful than writing read-heavy. I have been hit by various types of deadlocks including the annoying foreign key deadlocks. In PostgreSQL 9.2 they are working on removing false (as in they are not necessary to preserve consistency) foreign key deadlocks so they should be less common if that patch lands.
- leif 15y ago
  MySQL with TokuDB handles write-heavy OLTP workloads well, even with multiple complicated indexes. Since it uses Fractal Tree indexing, writes are so fast that locks aren't held long enough for this to become a problem.