andyg;512550 Wrote:
>
> > sebp;512243 Wrote:
> >> I also notice the slow scanning with latest embedded versions, but
> I'm
> >> using SQLite:
> >> 29857 & 29883 took around 1 hour and 5 minutes
> >> 29953 & 29961 take around 2 hours and 27 minutes
> >
> >
> http://svn.slimdevices.com/slim/7.5/branches/embedded/server/Slim/Schema.pm?r1=29932&r2=29931&pathrev=29932
> >
> > Checking that all the tracks have exactly the same contributors list
> is
> > quite an expensive chunk of code. I think the code is doing the
> right
> > thing; it's just that the right thing is expensive to do.
> >
> > I note the use of the 'last' statement to break out of the
> comparisons
> > as soon as one track is found that has different contributors, but
> for
> > most albums in my collection, the logic will be required to check
> every
> > last track before it can conclude that it is not a
> compilation.[/color]
>
> Yeah I am not very happy with this code, I spent a while trying to find
> a better way of doing this but couldn't come up with anything.
What's the main problem ?
- Is it the SQL with the inner select ? Have you tried using a ordinary
join instead ?
- Is the problem that the SQL with the inner select is executed a lot
of time ?
- Is the problem the perl code that is executed based on the result
from the SQL with the inner select ?
It would probbaly be possible to do it all with SQL, but I'm not sure
how database independent it would be as I think for example
group_concat is database specific. I'm also not sure how this will
perform in a large database, in my 3000 track database everything tends
to be fast.
I think something like this will get all albums which doesn't have the
same ARTIST value of all tracks:
Code:
--------------------
select album from
(select distinct album,group_concat(distinct contributor order by
contributor) as contributors
from contributor_track,tracks
where
track=tracks.id and
role=?
group by track) album_contributor_combinations
group by album
having count(*)>1;
--------------------
You can probably combine this with a UPDATE statement to update the
compilation flag on all these albums.
--
erland
Erland Isaksson
'My homepage' (http://erland.isaksson.info) (Install my plugins through
Extension Downloader)
(Developer of 'TrackStat, SQLPlayList, DynamicPlayList, Custom Browse,
Custom Scan, Custom Skip, Multi Library, Title Switcher and Database
Query plugins'
(http://wiki.erland.isaksson.info/index.php/Category:SlimServer))
------------------------------------------------------------------------
erland's Profile: http://forums.slimdevices.com/member.php?userid=3124
View this thread: http://forums.slimdevices.com/showthread.php?t=74700
_______________________________________________
beta mailing list
[email protected]
http://lists.slimdevices.com/mailman/listinfo/beta
