

Opinion: moreSQL is real - willvarfar
http://williamedwardscoder.tumblr.com/post/16399069781/google-moresql-is-real

======
tom_b
I love SQL (stay with me).

I cut my SQL teeth on SQL scripts written by programmers who were mainly Java
jockeys. Lots of imperative code built around getting a result set and
iterating over it.

I was learning relational programming by playing in SQL cmd lines. And getting
massive speed-ups by pushing code back to the SQL engines and out of
imperative loops.

I love this style of coding - when you squint just right, it looks (a bunch)
like a REPL and exploratory development.

And for that last fact, I will always have a bit of a soft spot for SQL. ANSI,
please. Host language interfaces on the side.

~~~
jcromartie
I came here to say pretty much just this. SQL and declarative relational
programming are _extremely_ powerful. The new breed of DBs are throwing away
something with a solid mathematical and theoretical foundation.

In fact, I wonder if a lot of the reaction _against_ relational databases
stems from a lack of understanding of the declarative style and the underlying
relational algebra.

Most of the programmers I have worked with can write Java or C# code just
fine, but they do just like you described when writing SQL, which makes SQL
seem clunky and perform horribly. On the other hand, when you start thinking
in sets, and learn to embrace the query optimizer (it does work) you can stop
writing silly SQL code and write beautiful and elegant code that does exactly
what you need 99% of the time.

I like Ruby, Clojure, Haskell, JSON, shell scripts, and dang it I like SQL.

~~~
cameronh90
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
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
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.

~~~
leif
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.

------
dhruvbird
You can find out more about tokutek here:
<http://duckduckgo.com/?q=tokutek.com> <http://www.quora.com/Andrew-
Byde/answers/Tokutek>

~~~
Tokutek
To expand the MVCC list noted above, MySQL + TokuDB is always in MVCC as well
(since version 5.0 last year). Also note that the Quora link describes
Fractional Cascading. TokuDB uses Fractal Tree indexes. To learn more about
Fractal Tree indexes as opposed to basic LSM, see here -
[http://www.tokutek.com/2011/09/write-optimization-myths-
comp...](http://www.tokutek.com/2011/09/write-optimization-myths-comparison-
clarifications/)

