On 5/28/15, Richard Hipp <drh at sqlite.org> wrote: > > In the meantime, you can work around the problem by manifesting the > view yourself.
Another temporary work-around is to run the following C-langauge API when the process first starts up (or at any other time prior to running the query that is not performing well): sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, 0x100); Doing so will disable the specific "optimization" that seems to be causing your performance problems. Another work-around is to use the latest "snapshot" of SQLite on the https://www.sqlite.org/download.html page and then add the "ALL" keyword to the offending subquery. Like this: SELECT sp.name, st.bug_name, (SELECT cve_desc FROM nvd_data WHERE cve_name = st.bug_name), (SELECT ALL debian_cve.bug FROM debian_cve ---^^^^------- Add this keyword. WHERE debian_cve.bug_name = st.bug_name ORDER BY debian_cve.bug), sp.release, sp.subrelease, ... "SELECT ALL" means exactly the same thing as just "SELECT" in standard SQL, but hardly anybody ever uses the ALL keyword. So, for the time being, SQLite has commendeered that syntax as a hint that the subquery in the FROM clause (the debian_cve VIEW here) should be manifested into a transient table rather than run incrementally by a co-routine. This is an experimental hint and might well disappear before the next official release, but if you want to help us experiment with it, that would be great. -- D. Richard Hipp drh at sqlite.org
