SQL is the assembly language of the modern world

This isn’t a new idea, it’s been mentioned before, so I’m not taking credit for it, but it’s still worth pointing out… again… for the umpteenth time. So I’m gonna pick up the baton-of-flames and try the argument again:

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.

I once heard someone say that “SQL is the assembly language of the business application world” and I totally agree with this (I linked to Jett Attwood’s post above, but I heard it somewhere else first and my Google-fu is failing me tonight).

If you read the discussions on USENET back in the day when C++ and later Java were coming into popularity, there were a bunch of die-hards who held the conventional wisdom that C++ and Java compilers couldn’t possibly ever generate machine code that would achieve the super high performance of lovingly hand-crafted assembly language.  Add into that the bytecode interpreting of Java and the Garbage Collector and their heads nearly exploded.

Now, the same kinds are arguments are being made about not using stored procedures and instead using generators or even the inconceivable: Object/Relational Mappers.

While the arguments of the assembly-language advocates were mostly true: Compilers can’t create the mostly highly optimized code, it turns out that modern runtime environments, processors, and operating systems can account for this and actually do a better job of managing the complexities of runtime performance optimizations better than a human assembly language writer could ever hope to achieve.  The system now tunes itself on the fly based on the current reality of the operating environment as it stands right now. 

SQL Server is now getting to the point where it’s able to better tune itself based on the kinds of queries coming in and the volume and type of data it’s returning. It’s getting to the point where hand-crafting SQL may actually be self-defeating. We’re not quite there yet, and there are still scenarios where hand-crafting SQL makes sense. Coincidentally, there are still situations where, in C#, it’s appropriate to use the unsafe() keyword, or even use unmanaged code or even assembly language!

I don’t think we’ll ever be able to get away from hand-crafted SQL, but I think it should be our goal to standardized on a set and sytle of SQL generation and let the tools like Linq2SQL, NHibernate, etc work with tools like SQL Server and let them work together to optimize your intentions.

Our goal should be to get out of the SQL crafting business and get back into the data access business.