3 ms·
Pretty sure this is why sql engines normally jit compile to either bytecode or straight to machine code, so that they can easily recompile and replace the query
by runevault 2y ago
Pretty sure this is why sql engines normally jit compile to either bytecode or straight to machine code, so that they can easily recompile and replace the query if the statistics or other factors change. So long as you don't regularly recompile the query you pay the cost once then reuse the results possibly thousands or even millions of times, but without being forced to keep using the exact same plan until you rebuild the application.
- anacrolix 2y agoPretty sure Sqlite's bytecode is generated after choosing the optimal plan. So you can't change it after that point. I had never considered this but you probably do want to prepare or expire statements after a while if your DB changes significantly
- runevault 2y agoIt never reconsiders? The one I have the most experience with (SQL server) certainly will, sometimes at weird times in ways that are non-obvious leading to performance degradation heh.
- maxbond 2y agoPoking at the documentation, they're only recompiled when the schema changes or ANALYZE has been run[1]. So, if you want them recompiled, you can use the optimize PRAGMA[2]. They raise a good point in the docs that in the contexts SQLite works in, if there's a regression caused by changing the plan, there won't be a DBA around to fix it. [1] https://sqlite.org/queryplanner-ng.html https://sqlite.org/queryplanner-ng.html [2] https://sqlite.org/lang_analyze.html#req https://sqlite.org/lang_analyze.html#req