LM 18/Sarah - Banshee UI freezes on large music collections (slow SQL for cache reload)

[Adam Szmigin]

Banshee 2.6.3+sarah in LM 18/Sarah has an inefficient SQL query that runs frequently, causing UI unresponsiveness on a regular basis. On my machine (Intel i7-6700K CPU and fast SSD), these queries take ~5 seconds to complete, meaning that the UI is unresponsive for 5 seconds at a time. On slower machines, I imagine this behaviour is more problematic.

Note that I have ~9000 tracks in my music collection. This issue probably won't manifest for small music collections.

The bug is also reported upstream here:
https://bugzilla.gnome.org/show_bug.cgi?id=771896

The offending SQL is in DatabaseYearListModel.cs:

            ReloadFragmentFormat = @"
                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{0}
                        WHERE (CoreCache.ModelID = {1}) AND
                              CoreCache.ItemID = {2} {3})
                    ORDER BY Year";

I have not been able to determine the root cause, but my hunch is that this is due to a change in the way that SQLite forms its query plans for this particular query.

In the upstream codebase, all cache reload queries like this are modified to include a 5th positional parameter {4} (zero-counting here), which supplies an UNLIKELY hint before for the WHERE clause. This seems to resolve the issues, but the fact remains that the query is not well optimised. The version supplied in the LM packages does not have any code for the UNLIKELY hints. Note that the Ubuntu xenial package includes a patch that introduces this hinting:
http://archive.ubuntu.com/ubuntu/pool/universe/b/banshee/banshee_2.9.0+really2.6.2-7ubuntu2.debian.tar.xz

An alternative fix is to rewrite the query to be more performant, which I was able to do on my LM18 workstation to resolve the problem. Maybe this will be done upstream at some point, but I'm not sure what LM's policy is for making patches outside of both Ubuntu and upstream. I used the following fragment instead, which resolved the issue for me:

            ReloadFragmentFormat = @"
                FROM (SELECT MIN(CoreTracks.TrackID) AS TrackID, CoreTracks.Year
                      FROM CoreTracks, CoreCache{0}
                      WHERE (CoreCache.ModelID = {1}) AND CoreCache.ItemID = {2} {3}
                      GROUP BY Year) AS CoreTracks";

Steps to reproduce:

* Install LM 18
* Have a music collection with many (>9000) files
* Run Banshee, set it to look at your large music collection.
* Search for files and clear the search query, play tracks. These actions trigger cache reloads.
* Observe slow performance.
* Optionally run banshee with --debug to see the slow SQL written to console.

Steps to fix:

1) Apply patch (can borrow this from Ubuntu source package) that introduces the UNLIKELY query hint. I have not been able to verify this fix personally yet.
2) OR replace SQL fragment in DatabaseYearListModel.cs with the more performant version above. I have been able to verify that this fix works.

Kind regards,
Adam

[Adam Szmigin (smidge)]

Have changed the title of this bug to be more obviously-guessable to anyone also experiencing the issue.

I am also uploading a patch that fixes the problem (for me at least) by replacing the inefficient SQL query with a better one. I have tested this as working. The patch is adapted from Nicolas on the upstream bug thread:
https://bugzilla.gnome.org/show_bug.cgi?id=771896