

SQL is the assembly language of the modern world - muriithi
http://www.lostechies.com/blogs/chad_myers/archive/2008/02/21/sql-is-the-assembly-language-of-the-modern-world.aspx

======
giardini
The author's thesis: "I really would like to challenge the conventional wisdom
that stored procedures are the best and/or that hand-crafted SQL is the only
way to achieve good performance."

The author is obviously confused.

Both stored procedures (SPs) and dynamic SQL queries are "hand-crafted". Any
craftsmanship difference between the two is the level of craftsmanship of the
code author. In most shops SPs are written by more experienced SQL coders,
usually a good thing.

In current databases (IIRC SQL Server also) execution plans are created at
runtime for SPs as well as for dynamic SQL queries. So dynamic SQL has no
guaranteed efficiency advantage arising from an updated execution plan.

Other advantages of restricting developers to use of SPs:

Security - SPs allow access control. By restricting developers to using SPs a
DBA can restrict users and developers to particular operations/tables/fields.

Efficiency - SPs execute inside the database engine. Minimal network I/O (if
any) is required. But a series of dynamic SQL operations may require moving
the data outside the database proper, mandating record locking, network I/O,
etc.

Maintainability - SPs allow data hiding. The DBA can restructure the database
internally without requiring application rewrite.

Consequently any dynamic SQL operation can be rendered more efficient by
rewriting it as a SP.

~~~
chadmyers
I don't know about you, but I try to avoid hand-crafting SQL just like I avoid
hand-crafting machine code/assembler. I use tools for that (either generators
or O/RM).

Dynamic SQL doesn't have any specific advantage, correct, but neither do
SProcs, so why put all your stuff in sprocs? Why not let a tool do the SQL and
guide the tool to do the right thing?

SP Security: If you don't trust your devs and applications against the DB,
you've already lost. SP security is just mainframe-thinking and a lost cause.

Efficiency: At least with SQL Server, there is no difference between SProcs
and dynamic SQL (caveat: Parameterized queries, which should be the only type
of dynamic SQL anyone is doing). They both get compiled and stuff in the
parameter cache and are treated equal. This is as of SQL 7.0 (it's in the
docs)

Maintainability: Wha? This is the silliest argument I've heard. If you're
swapping data around out from under the application you have HUGE
maintainability DECREASES because now there is non-obvious, hidden magic going
on.

This is all data-centric thinking and DBA protectionism. The DB exists to
serve applications which in turn serve users. Rather than pushing off
applications, DB's should work as closely as possible with applications.

"Consequently any dynamic SQL operation can be rendered more efficient by
rewriting it as a SP."

This statement makes no sense and has no bearing in fact.

~~~
giardini
"I use tools for that (either generators or O/RM)."

As long as you know what your tools are doing.

"If you don't trust your devs and applications against the DB, you've already
lost."

No distrust is necessary. Consider developer error: mandating and restricting
access to databases through SPs assures certain mistakes cannot happen (and
makes clear where responsibility lies should they happen). This actually
protects the developer.

And many serious security attacks occur from inside an organization. Some
corporations are criminally liable for violations of access: see the Sarbanes-
Oxley Act.

"SP security is just mainframe-thinking and a lost cause."

"Mainframe-thinking" is increasingly relevant as data centers continue to
centralize.

You ignored what I said about efficiency. Indeed you ignored most of what I
said. I sense you have an axe to grind but it's missing a head. Another
developer error? Perhaps you can ask your DBA for a proper weapon?

------
rcoder
I don't think this article would ever have been written by someone with any
significant amount of experience writing assembler.

The reasons to move away from low-level machine code and towards high-level
languages like Java are numerous (safety, portability, programmer
productivity), while the advantages of writing raw assembler basically boil
down to two: low-level machine access and performance. The former is only
useful to a small number of applications, and the latter is mostly covered by
Moore's Law.

SQL, on the other hand, is not itself a low-level language. It has powerful,
declarative constructs for efficiently dealing with large datasets, and modern
database engines already serve as high-level runtime environments abstracting
away the mundane details of page faults, index maintenance, and caching.

------
corentin
Considering the fact that SQL is a declarative query and data definition
language, I don't know what kind of nasty, low-level stuff you would want to
abstract away.

SQL may not be perfect, and there probably is a mismatch with the programming
language, but is generating declarative statements from procedural/OO
languages the best solution?

~~~
brlewis
You're thinking of abstraction as a way to hide low-level details that aren't
pertinent to your thinking about how to solve a problem.

If you think of abstraction as a way to hide anything a Blub programmer
doesn't want to think about, then abstracting away the relational model makes
sense.

------
bayareaguy
This sort of thing is often heard and there is a little truth to it, but
people who think this way reveal more about themselves than the state of the
art.

An assembly language is just a nice presentation for machine code which
regulates the load, compute and store activity of some kind of processor and
in a sense the familiar "crud" part of SQL expresses similar activity in some
kind of database. Both are widely used and both are also fairly old and often
automatically generated from higher level abstractions.

Whether or not either is modern is more of a function of what "level" you're
operating. "Modern" has a different meaning to someone working at the level of
Hadoop processes and Amazon services then to someone working with GPGPUs.

------
xirium
Surely, it is more like Cobol. Regardless, assembley and Cobol are quite
primative.

