Sure, here is some wall of text explaining what I did:
Renamed “tvshow_view” and “season_view” so I have them just in case I screw up.
Then copied the content of these views to new tables replacing the old views.
I now have “tvshow_view” and “season_view” as a table
At this point you already can test the speed difference if fiddling with the DB is worth your time.
For me on with over 30K TV episodes opening the season view took very long. Reason is the way the views/tables are designed, and even worth season_view is referencing the tvshow_view. On top of that the queries are quite bad as well.
For me opening a larger TV show with a lot of season and episodes could take several seconds. Very annoying delay. Sure you get used to it. Felt often like a HDD is waking up…
So far so good.
Next I created an update procedure for the both views, so changes to the underlying tables are reflected. Just formalism, nothing special here. They are basically doing the query the view did before but now doing an update as well afterwards to the new table.
These two procedures take naturally the season ID or the TV Show ID as a parameter, which is also the primary key on the tables I created.
Also I needed to create some helper procedures that take File ID, Path ID, Show ID, or ShowID plus Season ID as a parameter. These do nothing more than a quick SQL query getting me the season ID and then call the main procedure (TV Shows only needed wrapper for File ID and Path ID).
Then setting up the update, insert and delete triggers for the various scenarios (e.g. watched status in files changed) and call the appropriate procedures I create before.
Sounds more complex than it is: Think of it like this
- Trigger called
- Trigger calls helper procedure with e.g. File ID
- Helper procedure gets TV Show ID from File ID
- Helper procedure calls main TV Show update procedure to update a row in TV Show View.
and steps 2 to 4 and repeated with the season then.
Now the two new tables get updated whenever something changed. No view needed anymore. That means reading is really fast compare to before.
How did I know which tables need a trigger?
That is simple: I looked at the view and from which underlying table the data came from. And naturally then that table needed a trigger.
Now it is really fast navigating… The “wait icon” though pops up for a fraction of a second. Need to check where to turn that off. Probably hardcoded in relation to SQL queries maybe. Need to check.
it is as fast basically as a local DB. Well almost.
We talking now 250ms for example when opening Cheers with 11 season and 273 episodes. I think that one took 5+ seconds or so before.
As said the way the DBs and the queries are designed your SQL database is basically forced to get all epsiode information to create the season view.
The “tvshowcounts” view is OK. But it is responsible right now for most of the delay
I will do that probably as well just for good measure.
Then apply the same logic to movies.
If you know SQL basics this is nothing complex. Just a lot of work to type it down and test stuff of course.
Will not do music videos as I do not have any. And probably in the future music. Though I do not store much music right now in Kodi. Though have a ton of it, I might scrape into Kodi now. Tried once and it was unbearable slow.
Once I am done I can share my SQL script here (use at own risk of course). Just execute it and you are done. Naturally with DB schema updates on Leia some adjustments might be needed. But Kodi will update perfectly fine! The changes would just go poof.
Anyway off doing the other views for videos now…