banshee performance regression

Datum: Thu, 23 Oct 2014 18:42:17 -0400
Von: Richard Hipp <drh  sqlite.org>
An: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Betreff: Re: [sqlite] Performance regression in Banshee with sqlite
3.8.7 on Arch Linux


On Thu, Oct 23, 2014 at 4:03 PM, Tomislav Ljubej <sporbie@gmail.com>
wrote:

> I've searched Banshee's source code and it seems 'CoreCache' is a TEMP
> table according to some comments in the code but I have no clue where
> it's actually defined, there is no 'CREATE TABLE' statement in the
> code for that particular table (I've searched even with TEMP or
> TEMPORARY keywords, nothing). Then I've googled around and found this
> definition:
>
> CREATE TABLE CoreCache (
>                         OrderID INTEGER PRIMARY KEY,
>                         ModelID INTEGER,
>                         ItemID INTEGER);
>

Thanks.  That was sufficient for me to reproduce the problem.  The
problem is caused by this check-in:

   http://www.sqlite.org/src/info/0bdf1a086b

And that check-in was necessary to ensure correct behavior in certain
obscure circumstances.  So I cannot easily back it out.  But I can work
to try to find a different optimization that makes your query run
faster.

In the meantime, may I suggest rewriting your query.  The query you
have is this:

INSERT INTO CoreCache (ModelID, ItemID)
SELECT 9, CoreTracks.TrackID
  FROM (SELECT MIN(CoreTracks.TrackID) AS TrackID, CoreTracks.Year
          FROM CoreTracks GROUP BY CoreTracks.Year) AS CoreTracks
 WHERE CoreTracks.Year IN
       (SELECT CoreTracks.Year FROM CoreTracks, CoreCache
         WHERE CoreCache.ModelID = 71
           AND CoreCache.ItemID = CoreTracks.TrackID )
 ORDER BY Year;

I suggest rewriting it as follows:

INSERT INTO CoreCache (ModelID, ItemID)
SELECT 9, MIN(TrackID)
  FROM CoreTracks
 GROUP BY Year
HAVING Year IN
       (SELECT CoreTracks.Year FROM CoreTracks, CoreCache
         WHERE CoreCache.ModelID = 71
           AND CoreCache.ItemID = CoreTracks.TrackID )
 ORDER BY Year;

Or perhaps this:

INSERT INTO CoreCache (ModelID, ItemID)
SELECT 9, MIN(TrackID)
  FROM CoreTracks
 WHERE Year IN
       (SELECT CoreTracks.Year FROM CoreTracks, CoreCache
         WHERE CoreCache.ModelID = 71
           AND CoreCache.ItemID = CoreTracks.TrackID )
 GROUP BY Year
 ORDER BY Year;

In 3.8.6, SQLite was making the above transformation automatically.  But
there are corner cases where this transformation is not valid and so it
was disabled for 3.8.7, which is apparently what is causing your
slowdown.

No promises, but I will try to make this transformation occur
automatically again for SQLite 3.8.8, at least in your case where it
does appear to be valid.

--
D. Richard Hipp
drh@sqlite.org