On Feb 1, 2010, at 3:44 PM, erland wrote:
>
> 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.
The way it works now is that this query is run after every track is added, when
there is not an explicit COMPILATION tag present. So it gets run once per
track. The results from the query are longer for each subsequent track in a
given album, but unless you have an album with a ton of tracks that's not a big
deal. I benchmarked this today and it's actually not that slow, so maybe the
drastic slowdown came from somewhere else. I need to do some more testing.
SELECT contributor, track
FROM contributor_track
WHERE role = ?
AND track IN (
SELECT id
FROM tracks
WHERE album = ?
)
ORDER BY contributor, track
_______________________________________________
beta mailing list
[email protected]
http://lists.slimdevices.com/mailman/listinfo/beta
