If you're looking for a simple/easy/clean way of doing it, there isn't one. You have to modify the library to do it properly. But I still find it an interesting design challenge.
Maybe instead of going the eager route you can go lazy and just cache subexpressions which might be called again. This is messy though, a cacheable expression would have to be a user-defined function call that takes parameters that are easy to compute (since the params are always evaluated), plus an id param to identify the sub expression. The first time the function is called you calculate and store the value under the id and next time it's called you return the stored value that was previously computed. This has the advantage of being automatic and you could hand tune it, storing only the calls you know you will reuse. Having to use user defined function calls is pretty ugly although you could automatically transform the statement and generate the C source for the calls. Another approach I've been thinking about implementing, which is a bit speculative to say the least, is to compile the SQLite byte codes to something like WebAssembly byte codes, then running it through the BinaryEn which optimizes it, then into native code. That would implement not only CSE but a host of other optimizations and might be useful where the statement and query plan will not change much, or it's worth spending compute time optimizing it thus. Typically SQLite will be IO bound, but for some applications (like mine) it might make sense. > On Sep 14, 2017, at 6:37 PM, Jens Alfke <j...@mooseyard.com> wrote: > > > >> On Sep 14, 2017, at 8:38 AM, Warren Young <war...@etr-usa.com> wrote: >> >> All the examples I’ve seen attempting to support the value of this feature >> are simple enough that even a naive text compression algorithm could find >> the similarities and “hoist” the copies so the value is computed only once. >> That means the *human* can also see the CSE and hoist it manually. > > Fine; **can someone please tell me how to hoist/factor out the subexpression > manually then**? My SQL queries are generated procedurally and I can easily > change my code to do this refactoring, if I know the trick. > > I've tried using a "WITH" clause, but it doesn't help; it results in the same > number of calls to the native function. (See previous post in this thread for > an actual example.) > > I need something that doesn't modify the database, so generating a new table > with the function results is right out. Even a temporary table wouldn't help > because it would probably be more overhead than it's worth (the functions I > want to factor out aren't _that_ expensive.) > > —Jens > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
