I hate to break into this with a ‘me too’ post, but I might have some useful information to add. A friend of mine is having the same problem. Suspecting a sqlite issue, I ran a (rather tedious and manual) sqlite diagnostic trace.
His database is 1 GB in size, which seems to be in the same weightclass as Johnsen’s.
His system is a windows server 2019 with direct attached storage. But the following measurements were done on a copy of the database using a Windows 10 desktop (without the relevant disks attached).
database com.plexapp.plugins.library.db
table directories: 27488 rows
table library_sections: 9 rows
table section_locations: 64 rows
table media_items: 303486 rows
table media_parts: 303588 rows
Command: --scan --refresh --section 1 --directory “O:\TV Shows\My Show\Season 1”
Total time spent: 235.6 seconds
Time spent inside sqlite3_step: 222.9 seconds
Number of sqlite3_step invocations: 276607
(sqlite3_step is the main sqlite function used for executing sqlite queries)
The most frequent queries:
264684x (4227 unique) select ... from directories where parent_directory_id=?
7980x sqlite internal master table queries
957x (1 unique) select distinct(metadata_items.id) from metadata_items where metadata_items.library_section_id=1 and metadata_items.metadata_type in (2) order by metadata_items.title_sort
956x (956 unique) select ... from metadata_items where id=?
956x (956 unique) select ... from metadata_items join metadata_items as parents on parents.id=metadata_items.parent_id join metadata_items as grandparents on grandparents.id=parents.parent_id where grandparents.id=150932 and metadata_items.refreshed_at is null order by parents.`index`,parents.year,metadata_items.`index`,metadata_items.originally_available_at
954x (954 unique) select ... from metadata_items join metadata_items as parents on parents.id=metadata_items.parent_id join metadata_items as grandparents on grandparents.id=parents.parent_id where grandparents.id=150932 and (metadata_items.guid like 'local://%' or metadata_items.guid='') order by parents.`index`,parents.year,metadata_items.`index`,metadata_items.originally_available_at
Other relevant queries:
29x (1 unique) select ... from section_locations where library_section_id=1 order by created_at
3x (2 unique) select ... from directories where library_section_id=1 and path='MY SHOW/Season 1' and parent_directory_id=34251 order by path
Concerning here is of course the huge number of queries to the directories table, there’s also a significant amount of duplicate queries using the same parameter values.
The majority of these queries take place before any disk access is attempted on the target path, so the kind of disk mount or storage system is unlikely to be a factor.
PS: Johnsen, I did this on windows with some ugly custom tooling and cannot be easily duplicated. Well, it depends on your level of expertise with debugging tools.
