2017-05-09, 08:29
Ok an artists node with 3900 artists.... I have near that number and don't see slowness (but that is on SQLite on RPi3). But you do, so let's get into it. 
While you can use a SQL browser like HeidiSQL to usefully check the releative performance of queries, the times taken by the browser to display the results don't really mean much. To get Kodi times some can come from the log, but I also analyse in VS2017 debug and add extra time checks to investigate, so things you probably can't do.
Because the SQL used is something that users can see and test in SQL browsers it often gets the blame. I will admit that efforts could be made to optimise the query syntax for MySQL. MySQL has some horrible weaknesses (e.g. it does not apply indexes to correlated subqueries), but working around them could easily slow SQLite and will only gain relatively small amounts of time. As you observed in your example the query itself is fast.
Yes only fetching the fields you want to display, rather than all of them as Kodi does, would make the query faster, but again the query speed is not the bottleneck.
The slowness between fetching the data (query execution of 0.03s so fast) and displaying it comes from 3 main areas that you can't easily see -
a) Sorting the results in local memory (rather than at the DB with an ORDER BY statement)
b) Looping through these results and doing processing per record (artist) to get the thumbnail.
c) Displaying a progress bar from the same thread as that doing the looping.
Of course it does take some time to allocate local memory to hold the biogrgraphies etc. too, but again small issue compared to the above.
The current design logic is loosely this:
UI wants to show artist name (no other info) for the 2500th to 2520th artist when ordered by name.
All fields of all artists are queried from the db and loaded into a local list of objects.
This list is sorted by name.
For each entry the thumbnail is queried and loaded into memory too.
The UI can now display that page for artists.
There are several alternative approaches with features like:
Don't lock the UI thread while doing all this.
Only fetch what you display (20 records at a time), or fetch in the background (lazy load UI side as well as db).
Redesign will happen eventually, if the team had more devs it would happen sooner.
While you can use a SQL browser like HeidiSQL to usefully check the releative performance of queries, the times taken by the browser to display the results don't really mean much. To get Kodi times some can come from the log, but I also analyse in VS2017 debug and add extra time checks to investigate, so things you probably can't do.
Because the SQL used is something that users can see and test in SQL browsers it often gets the blame. I will admit that efforts could be made to optimise the query syntax for MySQL. MySQL has some horrible weaknesses (e.g. it does not apply indexes to correlated subqueries), but working around them could easily slow SQLite and will only gain relatively small amounts of time. As you observed in your example the query itself is fast.
Yes only fetching the fields you want to display, rather than all of them as Kodi does, would make the query faster, but again the query speed is not the bottleneck.
The slowness between fetching the data (query execution of 0.03s so fast) and displaying it comes from 3 main areas that you can't easily see -
a) Sorting the results in local memory (rather than at the DB with an ORDER BY statement)
b) Looping through these results and doing processing per record (artist) to get the thumbnail.
c) Displaying a progress bar from the same thread as that doing the looping.
Of course it does take some time to allocate local memory to hold the biogrgraphies etc. too, but again small issue compared to the above.
Quote:Cant kodi execute another query on focus or something?No, not with the current design and that is the final big flaw. I'm sure the design made sense years ago for the video lib, but for large nodes (and we all have more media these days) it is a problem. But to change it is a fundametal redesign of the way the UI to DB interaction works.
The current design logic is loosely this:
UI wants to show artist name (no other info) for the 2500th to 2520th artist when ordered by name.
All fields of all artists are queried from the db and loaded into a local list of objects.
This list is sorted by name.
For each entry the thumbnail is queried and loaded into memory too.
The UI can now display that page for artists.
There are several alternative approaches with features like:
Don't lock the UI thread while doing all this.
Only fetch what you display (20 records at a time), or fetch in the background (lazy load UI side as well as db).
Redesign will happen eventually, if the team had more devs it would happen sooner.
Quote:Also the actual time to render everything (cant messure) but it feels like 7-8 seconds on an arm device.What you said in your op about x86 being OK in comparison does point to it being a memory allocation/processor speed issue, and that changes db side (to the server, query syntax etc.) are not going to help much. It makes an interesting test platform for any solution claiming to fix the slow large node issue.
