Hi Ondřej,
On 27/08/10 08:31, Ondřej Surý wrote:
> can you try 3.7.2-1? I tried changing Ian's test case to use BY
> RANDOM(); and it works fine for me.
No improvement with 3.7.2-1, sorry. Here's the sluggish query, from
Banshee's --debug-sql output:
[1 Debug 09:30:09.750] Querying model for track to play in album:Next mode
[1 Warn 09:30:09.753] HyenaSqliteConnection command issued from the main thread
[2 Debug 09:30:31.893] Executed in 22140ms
SELECT
CoreAlbums.AlbumID,
CoreAlbums.Title,
MAX(CoreTracks.LastPlayedStamp) as LastPlayed,
MAX(CoreTracks.LastSkippedStamp) as LastSkipped
FROM
CoreTracks, CoreAlbums, CoreCache
WHERE
CoreCache.ItemId = CoreTracks.TrackID AND
CoreCache.ModelID = 43 AND
CoreTracks.AlbumID = CoreAlbums.AlbumID AND
CoreTracks.LastStreamError = 0
AND CoreTracks.PrimarySourceID = 1
GROUP BY CoreTracks.AlbumID
HAVING
(LastPlayed < 1282901369 OR LastPlayed IS NULL) AND
(LastSkipped < 1282901369 OR LastSkipped IS NULL)
ORDER BY RANDOM()
LIMIT 1
CoreCache is a temporary, in-memory table. From the debug log I've
recreated the statements used to create and fill it, and have two other
files containing a single query each: one exactly as above, and another
with all references to CoreCache removed.
With the CoreCache parts of the query removed, it's very snappy:
% time sqlite3 banshee.db.copy < banshee-init-core-cache.sql <
banshee-next-shuffle-by-album-no-core-cache.sql
4478|Heartland|1282844295|1277228957
sqlite3 banshee.db.copy < banshee-init-core-cache.sql < 0.22s user 0.04s
system 97% cpu 0.267 total
But in its original form, it's reliably a hundred times slower, taking
over 22s:
% time sqlite3 banshee.db.copy < banshee-init-core-cache.sql <
banshee-next-shuffle-by-album.sql
1063|Stars On The Wall|1282654498|1272661309
sqlite3 banshee.db.copy < banshee-init-core-cache.sql < 22.28s user 0.07s
system 99% cpu 22.377 total
It's equally slow if I make CoreCache a regular table. I've checked
Banshee's Git history briefly, and can't find any changes relating to
CoreCache that could have caused this. I wondered if some indexes have
been binned or something. But, if I downgrade to libsqlite3-0 3.6.23.1-4
the slower query is miraculously fast again:
% time sqlite3 banshee.db.copy < banshee-init-core-cache.sql <
banshee-next-shuffle-by-album.sql
550|Happy Songs for Happy People|1280848000|1280848242
sqlite3 banshee.db.copy < banshee-init-core-cache.sql < 0.27s user 0.03s
system 99% cpu 0.302 total
I've uploaded my Banshee database, plus the three SQL scripts used, to
http://willthompson.co.uk/misc/banshee-sqlite-bug-591298 in the hope
that they'll be useful.
Sorry for not providing sample data sooner!
Regards,
--
Will
--
To UNSUBSCRIBE, email to [email protected]
with a subject of "unsubscribe". Trouble? Contact [email protected]
