4 ms·
> I wonder how much of the limitation are necessary in order for the query optimizer to have any chance at finding a good execution plan. Probably in exactly t
by masklinn 5y ago
> I wonder how much of the limitation are necessary in order for the query optimizer to have any chance at finding a good execution plan.
Probably in exactly the opposite way: the limitations of SQL put a lot of work on the back of the query optimiser without allowing for said optimiser to easily reason about the queries, or for the writer to easily feed the optimiser (short of dedicated extensions e.g. Oracle's optimizer hints).
Or so I would think, I've never heard of SQL being praised for its optimisability.
- ris 5y agoI think it's important to distinguish between what's highly optimizable in theory and what's easily optimizable in practise. The latter working here and now, and the latter being a (possibly perpetual) decade of compiler development away. An example here is how, sure, in theory, JITs can outpace AOT compilation because they have all the information the AOT compiler has plus runtime insights. But the ability to truly do that always seems to be a decade of compiler development away, with many giving up on the idea entirely. It's also important to consider what we're comparing SQL's optimizability against. If it's against typical NoSQL databases, most of which seem to favour a lower-level query specification, I can defend SQL's optimizability to the end - with SQL databases having the freedom to dynamically switch algorithms to adapt to the actual contents of the database at the time of the query. Something which, ironically, a stale optimizer hint (i.e. written with the size & shape of the database as it was last year in mind) can actually get in the way of. Not that I'm saying that SQL planners never produce stupid query plans of course.
- aliasEli 5y ago> An example here is how, sure, in theory, JITs can outpace AOT compilation because they have all the information the AOT compiler has plus runtime insights. But the ability to truly do that always seems to be a decade of compiler development away, with many giving up on the idea entirely. JIT has been used very successfully for a couple of languages, e.g. Java. But adding it requires a lot of effort. The same thing is unfortunately true for new query languages, building a new database is really expensive, this is probably the main reason that there have been no serious alternatives to SQL.
- ris 5y ago> JIT has been used very successfully for a couple of languages, e.g. Java I'm not at all disputing that - but its promises of "beating AOT" that we used to hear have not come to fruition. Where it really matters, e.g. HPC, you don't really see any JIT. C, C++ and Fortran still rule. And even for Java, you tend to be able to achieve equal or better performance with AOT compilers.
- aliasEli 5y agoWith an AOT compiler a lot of work is done at compile time, but with JIT it is done at runtime, which makes these programs slower to start. But for programs that do run for a longer time, like servers or IDE's the performance is quite decent. But Java is a memory safe language which means that it has to do a lot of checks that unsafe languages do not have to do, which means that it will always be slower that unsafe languages. In some cases the JIT can eliminate those safety checks, but that is not always possible. Java always had (and still has) an AOT that compiled to byte code. Adding JIT was a major improvement.
- taffer 5y agoOptimizability is one of the core ideas of the relational model. The others being data independence and a declarative query language. SQL is based on relational calculus and relational algebra, which in turn are based on first-order logic. The reason that every attribute in the relational model must be atomic is that first-order logic cannot be applied to nested sets. However, first-order logic is what makes query optimization possible in the first place. Thus, if one were to develop a query language that did not have these constraints, then one would lose optimizability.
- BadInformatics 5y agoAs has been mentioned in this article and many previous takedowns of SQL the language, it's a pretty poor approximation of both relational calculus and relational algebra. So in that sense, SQL barely has a leg to stand on. If anything, SQL not adhering more closely to those techniques is why query planning is so fraught in modern relational DBs. A language like the one described in TFA would be both closer to the relational model as theorized and probably easier/more consistent to optimize.