07-20-2023, 11:04 AM
> I've heard a lot lately that SQL is a terrible language, and it seems that every framework under the sun comes pre-packaged with a database abstraction layer.
Note that these layers just convert their own stuff into `SQL`. For most database vendors `SQL` is the only way to communicate with the engine.
> In my experience though, SQL is often the much easier, more versatile, and more programmer-friendly way to manage data input and output. Every abstraction layer I've used seems to be a markedly limited approach with no real benefit.
… reason for which I just described above.
The database layers don't *add* anything, they just *limit* you. They make the queries disputably more simple but never more efficient.
By definition, there is nothing in the database layers that is not in `SQL`.
> What makes `SQL` so terrible, and why are database abstraction layers valuable?
`SQL` is a nice language, however, it takes some brain twist to work with it.
In theory, `SQL` is declarative, that is you declare what you want to get and the engine provides it in the fastest way possible.
In practice, there are many ways to formulate a correct query (that is the query that return correct results).
The optimizers are able to build a Lego castle out of some predefined algorithms (yes, they are multiple), but they just cannot make new algorithms. It still takes an `SQL` developer to assist them.
However, some people expect the optimizer to produce "the best plan possible", not "the best plan available for this query with given implementation of the `SQL` engine".
And as we all know, when the computer program does not meet people's expectations, it's the program that gets blamed, not the expectations.
In most cases, however, reformulating a query can produce a best plan possible indeed. There are tasks when it's impossible, however, with the new and growing improvements to `SQL` these cases get fewer and fewer in number.
It would be nice, though, if the vendors provided some low-level access to the functions like "get the index range", "get a row by the `rowid`" etc., like `C` compilers let you to embed the assembly right into the language.
I recenty wrote an article on this in my blog:
* [**Double-thinking in SQL**][1]
[1]:
Note that these layers just convert their own stuff into `SQL`. For most database vendors `SQL` is the only way to communicate with the engine.
> In my experience though, SQL is often the much easier, more versatile, and more programmer-friendly way to manage data input and output. Every abstraction layer I've used seems to be a markedly limited approach with no real benefit.
… reason for which I just described above.
The database layers don't *add* anything, they just *limit* you. They make the queries disputably more simple but never more efficient.
By definition, there is nothing in the database layers that is not in `SQL`.
> What makes `SQL` so terrible, and why are database abstraction layers valuable?
`SQL` is a nice language, however, it takes some brain twist to work with it.
In theory, `SQL` is declarative, that is you declare what you want to get and the engine provides it in the fastest way possible.
In practice, there are many ways to formulate a correct query (that is the query that return correct results).
The optimizers are able to build a Lego castle out of some predefined algorithms (yes, they are multiple), but they just cannot make new algorithms. It still takes an `SQL` developer to assist them.
However, some people expect the optimizer to produce "the best plan possible", not "the best plan available for this query with given implementation of the `SQL` engine".
And as we all know, when the computer program does not meet people's expectations, it's the program that gets blamed, not the expectations.
In most cases, however, reformulating a query can produce a best plan possible indeed. There are tasks when it's impossible, however, with the new and growing improvements to `SQL` these cases get fewer and fewer in number.
It would be nice, though, if the vendors provided some low-level access to the functions like "get the index range", "get a row by the `rowid`" etc., like `C` compilers let you to embed the assembly right into the language.
I recenty wrote an article on this in my blog:
* [**Double-thinking in SQL**][1]
[1]:
[To see links please register here]