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,
StringID TEXT UNIQUE,
CachedCount INTEGER,
IsTemporary INTEGER DEFAULT 0
);
CREATE TABLE CoreTracks (
PrimarySourceID INTEGER NOT NULL,
TrackID INTEGER PRIMARY KEY,
ArtistID INTEGER,
AlbumID INTEGER,
TagSetID INTEGER,
ExternalID INTEGER,
MusicBrainzID TEXT,
Uri TEXT,
MimeType TEXT,
FileSize INTEGER,
BitRate INTEGER,
SampleRate INTEGER,
BitsPerSample INTEGER,
Attributes INTEGER DEFAULT 5,
LastStreamError INTEGER DEFAULT 0,
Title TEXT,
TitleLowered TEXT,
TitleSort TEXT,
TitleSortKey BLOB,
TrackNumber INTEGER,
TrackCount INTEGER,
Disc INTEGER,
DiscCount INTEGER,
Duration INTEGER,
Year INTEGER,
Genre TEXT,
Composer TEXT,
Conductor TEXT,
Grouping TEXT,
Copyright TEXT,
LicenseUri TEXT,
Comment TEXT,
Rating INTEGER,
Score INTEGER,
PlayCount INTEGER,
SkipCount INTEGER,
LastPlayedStamp INTEGER,
LastSkippedStamp INTEGER,
DateAddedStamp INTEGER,
DateUpdatedStamp INTEGER,
MetadataHash TEXT,
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,
ArtistID INTEGER,
TagSetID INTEGER,
MusicBrainzID TEXT,
Title TEXT,
TitleLowered TEXT,
TitleSort TEXT,
TitleSortKey BLOB,
ReleaseDate INTEGER,
Duration INTEGER,
Year INTEGER,
IsCompilation INTEGER DEFAULT 0,
ArtistName TEXT,
ArtistNameLowered TEXT,
ArtistNameSort TEXT,
ArtistNameSortKey BLOB,
Rating INTEGER,
ArtworkID TEXT
);
CREATE INDEX CoreAlbumsIndex ON CoreAlbums(ArtistID, TitleSortKey);
CREATE INDEX CoreAlbumsArtistIndex ON CoreAlbums(TitleSortKey,
ArtistNameSortKey);
CREATE TABLE CoreArtists (
ArtistID INTEGER PRIMARY KEY,
TagSetID INTEGER,
MusicBrainzID TEXT,
Name TEXT,
NameLowered TEXT,
NameSort TEXT,
NameSortKey BLOB,
Rating INTEGER
);
CREATE INDEX CoreArtistsIndex ON CoreArtists(NameSortKey);
CREATE TABLE CorePlaylists (
PrimarySourceID INTEGER,
PlaylistID INTEGER PRIMARY KEY,
Name TEXT,
SortColumn INTEGER NOT NULL DEFAULT -1,
SortType INTEGER NOT NULL DEFAULT 0,
Special INTEGER NOT NULL DEFAULT 0,
CachedCount INTEGER,
IsTemporary INTEGER DEFAULT 0
);
CREATE TABLE CorePlaylistEntries (
EntryID INTEGER PRIMARY KEY,
PlaylistID INTEGER NOT NULL,
TrackID INTEGER NOT NULL,
ViewOrder INTEGER NOT NULL DEFAULT 0,
Generated INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX CorePlaylistEntriesIndex ON CorePlaylistEntries(PlaylistID,
TrackID);
CREATE TABLE CoreSmartPlaylists (
PrimarySourceID INTEGER,
SmartPlaylistID INTEGER PRIMARY KEY,
Name TEXT NOT NULL,
Condition TEXT,
OrderBy TEXT,
LimitNumber TEXT,
LimitCriterion TEXT,
CachedCount INTEGER,
IsTemporary INTEGER DEFAULT 0,
IsHiddenWhenEmpty INTEGER DEFAULT 0
);
CREATE TABLE CoreSmartPlaylistEntries (
EntryID INTEGER PRIMARY KEY,
SmartPlaylistID INTEGER NOT NULL,
TrackID INTEGER NOT NULL
);
CREATE INDEX CoreSmartPlaylistEntriesIndex ON
CoreSmartPlaylistEntries(SmartPlaylistID, TrackID);
CREATE TABLE CoreRemovedTracks (
TrackID INTEGER NOT NULL,
Uri TEXT,
DateRemovedStamp INTEGER
);
CREATE TABLE CoreCacheModels (
CacheID INTEGER PRIMARY KEY,
ModelID TEXT
);
CREATE TABLE CoreShuffles (
ShufflerId INTEGER,
TrackID INTEGER,
LastShuffledAt INTEGER,
CONSTRAINT one_entry_per_track UNIQUE (ShufflerID, TrackID)
);
CREATE INDEX CoreShufflesIndex ON CoreShuffles (ShufflerId, TrackID,
LastShuffledAt);
CREATE TABLE CoreShufflers (
ShufflerId INTEGER PRIMARY KEY,
Id TEXT UNIQUE
);
CREATE TABLE CoreShuffleModifications (
ShufflerId INTEGER,
TrackID INTEGER,
LastModifiedAt INTEGER,
ModificationType INTEGER,
CONSTRAINT one_entry_per_track UNIQUE (ShufflerID, TrackID)
);
CREATE INDEX CoreShuffleModificationsIndex ON CoreShuffleModifications
(ShufflerId, TrackID, LastModifiedAt, ModificationType);
CREATE TABLE CoverArtDownloads (
AlbumID INTEGER UNIQUE,
Downloaded BOOLEAN,
LastAttempt INTEGER NOT NULL
);
CREATE TABLE IaItems (
ItemID INTEGER PRIMARY KEY,
ID TEXT UNIQUE NOT NULL,
Title TEXT NOT NULL,
MediaType TEXT,
DetailsJson TEXT,
SelectedFormat TEXT,
BookmarkFile TEXT,
BookmarkPosition INTEGER DEFAULT 0
);
CREATE TABLE LastfmStations (
StationID INTEGER PRIMARY KEY,
Creator STRING NOT NULL,
Name STRING NOT NULL,
Type STRING NOT NULL,
Arg STRING NOT NULL,
PlayCount INTEGER NOT NULL
);
CREATE TABLE HyenaModelVersions (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
version INTEGER);
CREATE TABLE PodcastSyndications(last_auto_download INTEGER,AutoDownload
INTEGER,DownloadStatus INTEGER,IsSubscribed INTEGER,FeedID INTEGER PRIMARY
KEY,Title TEXT,Description TEXT,Url TEXT,Keywords TEXT,Category TEXT,Copyright
TEXT,ImageUrl TEXT,UpdatePeriodMinutes INTEGER,Language TEXT,LastDownloadError
INTEGER,LastDownloadTime INTEGER,Link TEXT,MaxItemCount INTEGER,PubDate
INTEGER,LastBuildDate INTEGER,SyncSetting INTEGER);
CREATE INDEX PodcastSyndicationsIndex ON PodcastSyndications (IsSubscribed,
Title);
CREATE TABLE PodcastItems(FeedID INTEGER,LicenseUri TEXT,ItemID INTEGER PRIMARY
KEY,Author TEXT,Comments TEXT,Description TEXT,StrippedDescription TEXT,Guid
TEXT,IsRead INTEGER,Link TEXT,Modified INTEGER,PubDate INTEGER,Title
TEXT,Active INTEGER);
CREATE INDEX PodcastItemsFeedIDIndex ON PodcastItems(FeedID);
CREATE INDEX PodcastItemsGuidIndex ON PodcastItems(Guid);
CREATE INDEX PodcastItemIsReadIndex ON PodcastItems(IsRead);
CREATE TABLE PodcastEnclosures(ItemID INTEGER,EnclosureID INTEGER PRIMARY
KEY,LocalPath TEXT,Url TEXT,Keywords TEXT,Duration INTEGER,FileSize
INTEGER,MimeType TEXT,DownloadedAt INTEGER,DownloadStatus INTEGER);
CREATE INDEX PodcastEnclosuresItemIDIndex ON PodcastEnclosures(ItemID);
CREATE TABLE MetadataProblems (
ProblemID INTEGER PRIMARY KEY,
ProblemType TEXT NOT NULL,
TypeOrder INTEGER NOT NULL,
Generation INTEGER NOT NULL,
Selected INTEGER DEFAULT 1,
SolutionValue TEXT,
SolutionOptions TEXT,
ObjectIds TEXT,
ObjectCount INTEGER,
UNIQUE (ProblemType, Generation, ObjectIds) ON CONFLICT
IGNORE
);
CREATE TABLE Bookmarks(BookmarkId INTEGER PRIMARY KEY,Position
INTEGER,CreatedAt INTEGER,Type TEXT,TrackId INTEGER);
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
