View Single Post
Old 05-23-2013, 12:01 AM   #31
davidfor
Grand Sorcerer
davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.
 
Posts: 24,905
Karma: 47303824
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos
One of the performance problems is opening the shelf list. As far as I can tell it is purely about building that list. It doesn't matter where the books are, how big they are or whether they have covers. The time taken increases with the number of shelves, and the number of books on any shelf.

The shelf list shows all the shelves and a count of the books on them. At first glance it is simple to write an SQL statement to fetch the list. But, there are some complications.

The main complication is the SD card. If you insert an SD card, all the books on it are added to the database. You can then put these on shelves. If you remove the SD card, the books are not removed from the database. But, they are no longer listed in the library. And the count of books on the shelves does not include them.

Even with that complication, it takes to long. I had a play with this earlier in the week. I can produce the appropriate results with the following query:
Code:
SELECT name, COUNT(c.contentID)
FROM shelf s LEFT OUTER JOIN shelfContent sc ON s.name = sc.shelfname
LEFT OUTER JOIN content c ON c.contentid = sc.contentid
WHERE (c.contentType = 6 or c.contentType  IS NULL)
AND (externalId IS NULL OR externalid = '90164EF8' OR externalid = '')
AND s._IsDeleted = 'false'
AND (sc._IsDeleted = 'false' OR sc._IsDeleted is NULL)
GROUP BY name
ORDER BY name
That takes no time at all on my PC. That is the version when the SD card is inserted. Remove the second test on externalId when it isn't.

I also had a play with the indices. I added a couple that I thought might help, but didn't get any performance improvements.

From the above, I can only assume that someone has written some bad code that iterates over the database and manually counts everything. Or maybe the device is low enough powered to not be able to execute the query quickly. I would love to look at the code and see what they did. Alternatively, I might have missed something in my query and there is a legitimate reason for the slow speed.

And for the interested, "externalId" appears to be an id for the external SD card. I don't know where it comes from, but any book with this set, is on the SD card. The value is also in the "Kobo Reader.conf" in a line "90164EF8=true".
davidfor is offline   Reply With Quote