On 9/12/17, Jens Alfke <j...@mooseyard.com> wrote: > > I realize SQLite doesn't have the kind of industrial-strength query > optimizers that other SQL databases have, but (from an outside perspective) > this seems like a fairly straightforward optimization. SQLite is already > doing some similar tricks to recognize matching sub-expressions when it > applies an expression-based index to a query, for example. >
The query planner in SQLite will hold its own against most others. But we deliberately omit common subexpression elimination (CSE). This is because our research shows that out of the millions of queries that SQLite compiles every second, only a very tiny fraction would actually benefit from CSE, but checking for CSE is expensive in both memory and CPU cycles, and all queries would have to pay the extra checking overhead whether they benefit or not. A traditional compiler like GCC is free to use as much memory, time, and code space to implement esoteric optimizations as it wants, because compilation happens separately from the application and the build product will be reused many times and so the cost of compilation is amortized over many executions. But a query planner in an RDBMS (which is really just a compiler that translates the SQL programming language into some low-level representation - byte code in the case of SQLite) is more constrained because the compilation happens at application run-time and the number of uses of the build product is approximately 1. And so when writing a query planner, one must be careful in the use of memory, time, and code space devoted to optimizations. This is particularly so for SQLite which is an embedded RDBMS. Our belief is that CSE would be not worth the extra memory, CPU, and code space required to implement it since CSE simply does not come up that often in SQL statements. If, in the future, we find that people begin coding more complex SQL statements which will more often benefit from CSE, then we might revisit this decision. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
