Re: [sqlite] Crippling query plan change between 3.7.13 and 3.8.10.2

[Richard Hipp <drh@sqlite.org>]

On 5/28/15, Richard Hipp <drh@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@sqlite.org