Just to confirm, Custom Scan will delete all data and re-create it every
time you rescan.
If there are more than 40000 rows for the Custom Tag/Mixed Tag module in
the customscan_track_attributes table, it will instead drop the table
and recreate it, since DELETE is too slow when the table gets large.
Are you storing your tables in library.db, too?
I was wondering about the dropping of tables (or truncate), too. But
Andy left that note:
-- Use DELETE instead of TRUNCATE, as TRUNCATE seems to need unlocked
tables.
I'd expect a drop table to be as restrictive at least. Did you never
encounter any problems? Because yes, delete is slow.
Custom Tag module should for each track do a:
Just a few comments (and keep in mind I'm no SQL expert at all, only
just read quite a few performance FAQs :-)):
SELECT id,url from tracks where audio=1 limit 1 offset ?
Wouldn't it be faster to create a statement handler with this query
(without the limit & offset) and iterate it?
SELECT
tracks.id,
tracks.url,
case when tracks.musicbrainz_id like '%-%' then tracks.musicbrainz_id else
null end,
'mixedtag',
(See - I didn't even know such a thing as a case within a select was
available!..)
> ...
from tracks,contributor_track,contributors
where
tracks.audio=1 and
tracks.id=contributor_track.track and
contributor_track.role=1 and
contributor_track.contributor=contributors.id
Could you change the order of the where statements? If I understood the
FAQs correctly (http://www.sqlite.org/optoverview.html), only the first
statement's index would be used to narrow down the selection of records.
In your case that's the "audio=1", which would probably still return
>>90% of all tracks. If you used the tracks.id first, that might
probable speed up the processing?
But that's rather OT.
If auto vacuum
works, I suspect it will probably be the biggest improvement related to
Custom Scan related performance.
TBH: I still don't understand why vacuum would have such a large impact,
as I'd expect the DB to be smart enough to only read those parts of the
data which it requires. Thus the file _size_ shouldn't matter, should
it? A full vacuum might help re-arrange things to keep them together.
But audo_vacumm does not.
--
Michael
_______________________________________________
beta mailing list
[email protected]
http://lists.slimdevices.com/mailman/listinfo/beta
