Re: [sqlite] Performance regression in Banshee with sqlite 3.8.7 on Arch Linux
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); On this URL: https://mail.gnome.org/archives/banshee-list/2009-January/msg00174.html Anyway I have attached the .fullschema output. On Thu, Oct 23, 2014 at 3:09 AM, Richard Hipp <d...@sqlite.org> wrote: > Thanks for sending in the performance regression report. > > On Wed, Oct 22, 2014 at 1:51 PM, Tomislav Ljubej <spor...@gmail.com> wrote: > >> Hello, >> >> There is a performance regression in the media player Banshee on the >> query that is in the attachment. The query takes about 13 seconds on >> my i7 4770 processor. I tried the latest stable version before the >> current one which is 3.8.6.1 and the performance regression doesn't >> happen, the query completes in less than half a second. >> >> The query is run 3 times at Banshee startup so it slows down the >> startup of the application significantly. >> >> Also attached is the .schema output for the database Banshee uses. I'm >> not sure why the 'CoreCache' table isn't mentioned in the schema but >> I'm pretty sure it's actually referring to 'CoreCacheModels', I got >> the query from Banshee's debug output so it may not reflect the actual >> names in the database. >> > > Several problems: > > (1) Your "query" is actually two statements: a DELETE followed by an > INSERT. The INSERT is fed from a SELECT. Would I be correct in assuming > that the SELECT is the problem? > > (2) You are right that CoreCache is not in the schema, so the SELECT is not > valid. I tried changing the name of the CoreCache table in the SELECT into > CoreCacheModels, but then it complains that CoreCacheModels has no ItemID > column. > > (3) After you determine the correct schema and query, it would be helpful > to us if you can send us the output of ".fullschema". The ".fullschema" > output includes the sqlite_stat1 and sqlite_stat4 tables (if they exist) > which contain histogram data used by the query planner. Those table (if > they exist) are needed by us in order to reproduce the behavior your are > observing. > > (4) If you are able to send us your complete database file, perhaps by > private email, that would be even more helpful, but is not absolutely > necessary. > > >> >> -- >> Tomislav Ljubej >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Tomislav Ljubej CREATE TABLE CoreConfiguration ( EntryID INTEGER PRIMARY KEY, Key TEXT, Value TEXT ); CREATE TABLE CorePrimarySources ( PrimarySourceID INTEGER PRIMARY KEY, StringIDTEXT UNIQUE, CachedCount INTEGER, IsTemporary INTEGER DEFAULT 0 ); CREATE TABLE CoreTracks ( PrimarySourceID INTEGER NOT NULL, TrackID INTEGER PRIMARY KEY, ArtistIDINTEGER, AlbumID INTEGER, TagSetIDINTEGER, ExternalID INTEGER, MusicBrainzID TEXT, Uri TEXT, MimeTypeTEXT, FileSizeINTEGER, BitRate INTEGER, SampleRate INTEGER, BitsPerSample INTEGER, Attributes INTEGER DEFAULT 5, LastStreamError INTEGER DEFAULT 0, Title TEXT, TitleLoweredTEXT, TitleSort TEXT, TitleSortKeyBLOB, TrackNumber INTEGER, TrackCount INTEGER,
[sqlite] Performance regression in Banshee with sqlite 3.8.7 on Arch Linux
Hello, There is a performance regression in the media player Banshee on the query that is in the attachment. The query takes about 13 seconds on my i7 4770 processor. I tried the latest stable version before the current one which is 3.8.6.1 and the performance regression doesn't happen, the query completes in less than half a second. The query is run 3 times at Banshee startup so it slows down the startup of the application significantly. Also attached is the .schema output for the database Banshee uses. I'm not sure why the 'CoreCache' table isn't mentioned in the schema but I'm pretty sure it's actually referring to 'CoreCacheModels', I got the query from Banshee's debug output so it may not reflect the actual names in the database. -- Tomislav Ljubej DELETE FROM CoreCache WHERE ModelID = 9; 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 YearCREATE TABLE CoreConfiguration ( EntryID INTEGER PRIMARY KEY, Key TEXT, Value TEXT ); CREATE TABLE CorePrimarySources ( PrimarySourceID INTEGER PRIMARY KEY, StringIDTEXT UNIQUE, CachedCount INTEGER, IsTemporary INTEGER DEFAULT 0 ); CREATE TABLE CoreTracks ( PrimarySourceID INTEGER NOT NULL, TrackID INTEGER PRIMARY KEY, ArtistIDINTEGER, AlbumID INTEGER, TagSetIDINTEGER, ExternalID INTEGER, MusicBrainzID TEXT, Uri TEXT, MimeTypeTEXT, FileSizeINTEGER, BitRate INTEGER, SampleRate INTEGER, BitsPerSample INTEGER, Attributes INTEGER DEFAULT 5, LastStreamError INTEGER DEFAULT 0, Title TEXT, TitleLoweredTEXT, TitleSort TEXT, TitleSortKeyBLOB, TrackNumber INTEGER, TrackCount INTEGER, DiscINTEGER, DiscCount INTEGER, DurationINTEGER, YearINTEGER, Genre TEXT, ComposerTEXT, Conductor TEXT, GroupingTEXT, Copyright TEXT, LicenseUri TEXT, Comment TEXT, Rating INTEGER, Score INTEGER, PlayCount INTEGER, SkipCount INTEGER, LastPlayedStamp INTEGER, LastSkippedStampINTEGER, DateAddedStamp INTEGER, DateUpdatedStampINTEGER, MetadataHashTEXT, BPM INTEGER, LastSyncedStamp INTEGER, FileModifiedStamp INTEGER ); CREATE INDEX CoreTracksPrimarySourceIndex ON CoreTracks(ArtistID, AlbumID, PrimarySourceID, Disc, TrackNumber, Uri); CREATE INDEX CoreTracksAggregatesIndex ON CoreTracks(FileSize, Duration); CREATE INDEX CoreTracksExternalIDIndex ON CoreTracks(PrimarySourceID, ExternalID); CREATE INDEX CoreTracksUriIndex ON CoreTracks(PrimarySourceID, Uri); CREATE INDEX CoreTracksCoverArtIndex ON CoreTracks (PrimarySourceID, AlbumID, DateUpdatedStamp); CREATE TABLE CoreAlbums ( AlbumID INTEGER PRIMARY KEY, ArtistIDINTEGER, TagSetIDINTEGER, MusicBrainzID TEXT, Title TEXT, TitleLoweredTEXT, TitleSort TEXT, TitleSortKeyBLOB, ReleaseDate INTEGER, DurationINTEGER, Year
