PDA

View Full Version : How do browsing in the Web UI and Remote UI differ?



JJZolx
2005-07-11, 18:07
I just saw Dan's checkin regarding storing a "year" for an album to speed up the db queries. I'm not sure how you arrive at a year for an album in which different tracks are tagged with different years, but that's another discussion.

What I wonder is how this change would significantly speed up browsing albums. The difference between doing a SQL query for, say, 50 rows in the albums table and one in which you do a join of the album table with the tracks table for those 50 shouldn't be noticable when a user is browsing web pages. But I don't know how browsing from the remote UI is implemented - you don't have "pages" of N items. Are _all_ albums returned when a user browses into albums using the remote?

kdf
2005-07-11, 18:48
On 11-Jul-05, at 6:07 PM, JJZolx wrote:

>
> I just saw Dan's checkin regarding storing a "year" for an album to
> speed up the db queries. I'm not sure how you arrive at a year for an
> album in which different tracks are tagged with different years, but
> that's another discussion.
>
> What I wonder is how this change would significantly speed up browsing
> albums. The difference between doing a SQL query for, say, 50 rows in
> the albums table and one in which you do a join of the album table with
> the tracks table for those 50 shouldn't be noticable when a user is
> browsing web pages. But I don't know how browsing from the remote UI
> is implemented - you don't have "pages" of N items. Are _all_ albums
> returned when a user browses into albums using the remote?
>

the remote uses the BrowseDB mode, which borrows a lot of configuration
for the hierarchies, lists and finds from Pages.pm. Pages.pm handles
all of the web UI (browsetree() and browsedb())

as for timing, best prove it for yourself how much it takes. Look into
Devel::Profile and you can run as many tests using both sets of code as
you like in order to determine down to the millisecond how much time it
gained.

cheers,
kdf

Dan Sully
2005-07-11, 23:39
* JJZolx shaped the electrons to say...

>I just saw Dan's checkin regarding storing a "year" for an album to
>speed up the db queries. I'm not sure how you arrive at a year for an
>album in which different tracks are tagged with different years, but
>that's another discussion.

In my opinion if you have tracks that have different years in a single album,
you have bigger problems. :)

Every single piece of ripping and tagging software I've used applies the Year
at the Album level. Yes, I do realize that for some albums, such as Greatest
Hits and the like - technically each song was performed in a different year.
But the data one gets from CDDB/MusicBrainz, or by looking at the CD insert
itself, 99.95% of the time is refering to the entire album for the year.

My change doesn't actually alter the previous behavior that we had - it just
optimizes it.

>What I wonder is how this change would significantly speed up browsing
>albums. The difference between doing a SQL query for, say, 50 rows in
>the albums table and one in which you do a join of the album table with
>the tracks table for those 50 shouldn't be noticable when a user is
>browsing web pages. But I don't know how browsing from the remote UI
>is implemented - you don't have "pages" of N items. Are _all_ albums
>returned when a user browses into albums using the remote?

Jim - we are using an Object <-> Relational mapper called Class::DBI. This
allows us to write very little SQL, and use objects instead. Unfortunately
this means that access to the database isn't always extremely optimized.

When a user selects 'Browse Albums' - this query is made:

SELECT DISTINCT albums.id AS id,albums.title AS title,albums.titlesort AS
titlesort,albums.contributors AS contributors,albums.year AS
year,albums.artwork_path AS artwork_path,albums.disc AS disc,albums.discc AS
discc,albums.musicmagic_mixable AS musicmagic_mixable FROM albums ORDER BY
albums.titlesort, albums.disc

The results are then turned into an array of Slim::DataStores::DBI::Album
objects which are stored in memory. Yes - this is all the albums in your
collection.

For the player UI case - this list is then browseable via the INPUT.List
method that allows one to scroll up and down.

For the web UI case - all of the albums are loaded, passed to the
alphaPageBar() function in Slim::Web::Pages - but only the first N (50 is the
default I believe) are displayed.

If a user has the 'Show Year' option turned on - the previous implementation
would call the tracks() method on each album object in the list. tracks() is
defined in Slim/DataStores/DBI/Album.pm as:

$class->has_many(tracks => 'Slim::DataStores::DBI::Track', { order_by => 'tracknum'});

This means - when the tracks() method is called, fetch from the table that is
defined by Slim::DataStores::DBI::Track, any row in the db that has my id
(the album id) as a foreign key. So for each of those rows, average 10 per
album, turn them into objects. We only really wanted the first one, so we
could fetch the year from the track object to display it. This turns into 500
queries to the database (I did say unoptimized, right?) to display a list of albums.

As you've stated - the obvious solution here is to just do a join, and all
would be good. Yes, that's right - but also not very flexible in all cases.

6.x is reaching a point that I consider to be stable with respect to the
database - which means we can start doing more performance tweaks. There's
also a superclass of Class::DBI called Class::DBI::Sweet which is much
smarter about joins and the like which I will be investigating when time is
available to.

-D
--
<iNoah> I think someone should create a magazine for computer peripherals, called Card & Driver

sben
2005-07-12, 09:31
Dan Sully wrote:
> * JJZolx shaped the electrons to say...
>
>> I just saw Dan's checkin regarding storing a "year" for an album to
>> speed up the db queries. I'm not sure how you arrive at a year for an
>> album in which different tracks are tagged with different years, but
>> that's another discussion.
>
> In my opinion if you have tracks that have different years in a single
> album,
> you have bigger problems. :)
>
> Every single piece of ripping and tagging software I've used applies the
> Year
> at the Album level. Yes, I do realize that for some albums, such as
> Greatest
> Hits and the like - technically each song was performed in a different
> year.
> But the data one gets from CDDB/MusicBrainz, or by looking at the CD insert
> itself, 99.95% of the time is refering to the entire album for the year.
>
> My change doesn't actually alter the previous behavior that we had - it
> just
> optimizes it.

I realize that I may be on the anal-retentive side of MP3 taggers, but I
do actually alter the year of individual files in collection albums
(greatest hits, soundtracks, etc.). Will there be sane behavior? (I.e.
non-crashing behavior; "album year = track #1's year" would be sane enough.)

Dan Sully
2005-07-12, 09:33
* S. Ben Melhuish shaped the electrons to say...

>I realize that I may be on the anal-retentive side of MP3 taggers, but I
>do actually alter the year of individual files in collection albums
>(greatest hits, soundtracks, etc.). Will there be sane behavior? (I.e.
>non-crashing behavior; "album year = track #1's year" would be sane enough.)

First, just let me say, that ya'll are crazy. :)

The behavior right now is actually last track sets the year in the album table.

-D
--
<jwb> burning substations is manifestly the desire of the free market. hooray for utility deregulation
<jwb> the government would never be able to set fires with such brutal efficiency

sben
2005-07-12, 09:40
Dan Sully wrote:
> First, just let me say, that ya'll are crazy. :)

Hey, if iTunes would let me set the release date of the album separately
from the date of each track, I'd do that. So, yes, crazy.

> The behavior right now is actually last track sets the year in the album
> table.

That'll do (unless you can somehow magically hack iTunes...).

Thanks!

kdf
2005-07-12, 09:49
Quoting "S. Ben Melhuish" <sben (AT) pile (DOT) org>:
>
> That'll do (unless you can somehow magically hack iTunes...).
>
ooh, I'd LOVE to see that!
-k

Harald Wilhelm
2005-07-13, 02:38
Dan,

sorry for buttin' in:

> The behavior right now is actually last track sets the year in the
> album table.

IMHO, this is a design bug. Every track owns it's own set of tags. TYER
is one of them. So it belongs to the track and not to the album. Nothing
that can be found in or around an individual track belongs to an album.

dean
2005-07-13, 06:33
I agree. To be most complete, an album can have a year that may be
different than the individual songs. (i.e. when was the album
published vs. when was the song recorded), but the tags may not be
expressive enough for this.




On Jul 13, 2005, at 2:38 AM, Harald Wilhelm wrote:

> Dan,
>
> sorry for buttin' in:
>
> > The behavior right now is actually last track sets the year in the
> > album table.
>
> IMHO, this is a design bug. Every track owns it's own set of tags.
> TYER is one of them. So it belongs to the track and not to the
> album. Nothing that can be found in or around an individual track
> belongs to an album.
>
>
>

Harald Wilhelm
2005-07-13, 08:07
Dean,

exactly.

So you need a year for the track and a year for the album. If somebody
wants to make his library a perfect one, he'll need both.

I don't know about your current DB design. As far as I know you allow
collections (i.e. several discs in a box). You'll need an additional
year for this too. The Box can have a year, CD1 might have a different
year than CD2, and all tracks can have individual years too.

I would be happy with an album year and a track year.


dean blackketter wrote:
> I agree. To be most complete, an album can have a year that may be
> different than the individual songs. (i.e. when was the album
> published vs. when was the song recorded), but the tags may not be
> expressive enough for this.
>
>
> Developers mailing list
> Developers (AT) lists (DOT) slimdevices.com
> http://lists.slimdevices.com/lists/listinfo/developers

Ben Sandee
2005-07-13, 08:23
On 7/13/05, Harald Wilhelm <slimp3 (AT) haraldwilhelm (DOT) de> wrote:
> Dean,
>
> exactly.
>
> So you need a year for the track and a year for the album. If somebody
> wants to make his library a perfect one, he'll need both.
>
> I don't know about your current DB design. As far as I know you allow
> collections (i.e. several discs in a box). You'll need an additional
> year for this too. The Box can have a year, CD1 might have a different
> year than CD2, and all tracks can have individual years too.
>
> I would be happy with an album year and a track year.

Even if you can tag it as such, how would you want SlimServer to use
this information? There are only so many ways to effectively browse
and I can't imagine that making a distinct "browse by published year"
and "browse by recorded year" would be useful to anybody.

The fact is that if you want to you can put as much information as you
want into your files but SlimServer just populates the database with
information it can actually use to aid in presenting the tracks. I
suppose one could argue that an advanced search might want to
distinguish between published year and recorded year, but there are so
many higher-priority items....

anyway, just a little bit of reality check here.

Ben

Harald Wilhelm
2005-07-14, 00:15
Ben,

adding three years is plain theory, sure. Using the last year of a
number of tracks as the album year is completely wrong.

I just imagined the house I live in. The government software would use
the age of the person living on the highest floor as the age of the
building ;-) (It's just a joke!)


Ben Sandee wrote:
> On 7/13/05, Harald Wilhelm <slimp3 (AT) haraldwilhelm (DOT) de> wrote:
>
> Even if you can tag it as such, how would you want SlimServer to use
> this information? There are only so many ways to effectively browse
> and I can't imagine that making a distinct "browse by published year"
> and "browse by recorded year" would be useful to anybody.
>
> The fact is that if you want to you can put as much information as you
> want into your files but SlimServer just populates the database with
> information it can actually use to aid in presenting the tracks. I
> suppose one could argue that an advanced search might want to
> distinguish between published year and recorded year, but there are so
> many higher-priority items....
>
> anyway, just a little bit of reality check here.
>
> Ben
>

Ben Sandee
2005-07-14, 07:53
On 7/14/05, Harald Wilhelm <slimp3 (AT) haraldwilhelm (DOT) de> wrote:
> adding three years is plain theory, sure. Using the last year of a
> number of tracks as the album year is completely wrong.

Harald,

The vast majority of ID3 tagging software can only set a single year
tag per track and there is no way to set an album-level tag without
delving into custom/advanced tags that no modern tagger supports or
external files such as an album.txt in each album directory.

As such, I fail to see why using the last year is completely wrong.
The designers of slimserver decided that the year is something that is
most usefully scoped to an album -- and for the majority of users and
the majority of music out there this is a good compromise given the
limited tagging options that are out there. It may not be your first
choice, but effectively what they are saying is that as we iterate
through all of the tracks in an album we simply continue to overwrite
the album year with the current track's year until all scanning is
done. Last one through the gate wins. It's simple code to write and
it works in the majority of cases.

Now, you may be biased because maybe you have 100 compilation albums
where each track's year is the year in which it was originally
recorded. I can see how this would be aggravating because it
eliminates the ability to see the entire compilation when browsing by
year and the metadata for each track is arguably wrong.

Me... well I'm biased because I decided that for compilation albums I
would use the compilation release date for the year for all tracks.
Slimserver's behavior is exactly how I want it to be in this respect
and I love the browse by year feature. I'll pick a year in the
morning and put the entire year on random album shuffle and my music
is set for the day.

Anyway, just remember that these guys aren't out to do everything
completely wrong -- despite what some might say. :-)

Ben

kdf
2005-07-14, 09:25
I wonder if anyone has tried asking Bowmore to 'correctly' list all years on the
bottle instead of just one... ;)

Harald Wilhelm
2005-07-14, 13:01
Hi,

come on that's not fair ;-)

In their box are 6 identical bottles.

We're talking about a box with 6 different bottles, produced by six
different companies, with six different labels, and ... you get the
idea. On the box you'll find a tag which lists all 6 bottles with all
details. And below the details you'll find a package date <ROF,L>.

The current design is no problem for me. But IMHO it's wrong.


kdf wrote:
> I wonder if anyone has tried asking Bowmore to 'correctly' list all years on the
> bottle instead of just one... ;)
>

Harald Wilhelm
2005-07-14, 13:22
Ben,

"completly wrong" was to hard. Sorry about that.

When I looked at the ID3Vx papers several years ago I decided to go the
easy way and I used the tags as columns for the track table. For me it
was a natural 1:1 relationship.

What I see now is that you might get problems with those that own or
manage nearly perfect libraries. You don't need the track year to play
the songs. But perhaps someone wants to build a library managing tool on
top of the Slim DB.

I use my own tool and when v6 looked up I checked if I can drop my own
database and switch over to one single DB. One of the reasons I'm still
using two different databases is the missing year. I would love to
switch over. Currently I'm wasting a lot of time and resources with two
rescans ;-)


Ben Sandee wrote:
> On 7/14/05, Harald Wilhelm <slimp3 (AT) haraldwilhelm (DOT) de> wrote:
>
>>adding three years is plain theory, sure. Using the last year of a
>>number of tracks as the album year is completely wrong.
>
>
> Harald,
>
> The vast majority of ID3 tagging software can only set a single year
> tag per track and there is no way to set an album-level tag without
> delving into custom/advanced tags that no modern tagger supports or
> external files such as an album.txt in each album directory.
>
> As such, I fail to see why using the last year is completely wrong.
> The designers of slimserver decided that the year is something that is
> most usefully scoped to an album -- and for the majority of users and
> the majority of music out there this is a good compromise given the
> limited tagging options that are out there. It may not be your first
> choice, but effectively what they are saying is that as we iterate
> through all of the tracks in an album we simply continue to overwrite
> the album year with the current track's year until all scanning is
> done. Last one through the gate wins. It's simple code to write and
> it works in the majority of cases.
>
> Now, you may be biased because maybe you have 100 compilation albums
> where each track's year is the year in which it was originally
> recorded. I can see how this would be aggravating because it
> eliminates the ability to see the entire compilation when browsing by
> year and the metadata for each track is arguably wrong.
>
> Me... well I'm biased because I decided that for compilation albums I
> would use the compilation release date for the year for all tracks.
> Slimserver's behavior is exactly how I want it to be in this respect
> and I love the browse by year feature. I'll pick a year in the
> morning and put the entire year on random album shuffle and my music
> is set for the day.
>
> Anyway, just remember that these guys aren't out to do everything
> completely wrong -- despite what some might say. :-)
>
> Ben
>