

Double thinking in SQL - j_baker
http://explainextended.com/2009/07/12/double-thinking-in-sql/

======
tom_b
A good article, although I would have preferred some editing down on the
assembly example. Most readers who bother to read through it are probably
somewhat already on the SQL path, so parts are long-winded.

I know it's more of an intro to the "thinking in SQL" idea, but once you throw
in the query optimization stuff, my experience is a little knowledge goes a
long way.

In general, if the underlying relational model has been designed instead of
thrown together, maybe even with some appropriate indexing, SQL developers
shouldn't spend much time worrying about optimizing queries.

My main point is that usually, a sub-optimal query is just fine. Yes, you
should understand how to identify your really bad queries using whatever tools
your RDBMs provides, but I've always found that you have a very small number
of problematic queries are the runtime monsters. And in that class of bad
queries, you're usually looking at table scans run amok.

More important is that most developers still lack exposure to really using
SQL. In the article, there is a simple query that the author identifies as bad
and a few lines later shows a shorter (and more optimal) piece of code that is
functionally equivalent. The real problem here is not that a RDBMS optimizer
will struggle with the first query, but rather why a developer wouldn't
automatically write the second version?

I've worked pretty extensively with Other People's Code (SQL code that is) and
almost all the problems I've encountered seem to relate more to developer
ignorance of SQL and bad underlying table design or indexing.

