7 ms·

SQL is the assembly language of the modern world

10 points by muriithi 19y ago

corentin 19y ago
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 19y ago
  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.
xirium 19y ago
Surely, it is more like Cobol. Regardless, assembley and Cobol are quite primative.
bayareaguy 19y ago
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.
giardini 19y ago
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 19y ago
  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.
rcoder 19y ago
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.

10 points by muriithi 19y ago

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 19y ago
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.

xirium 19y ago

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

bayareaguy 19y ago

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.

giardini 19y ago

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 19y ago
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.

rcoder 19y ago

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.