Various Artists in Now Playing screen ?

[garym]

This is why, if you have no ALBUMARTIST tags for non-compilations, generating a single page full of albums requires hundreds of queries. As an example, if you had a page of 100 albums with an average of 12 tracks per album, it will take a minimum of 1200 queries just to generate that one web page. It's little wonder that it takes a couple of seconds to spit out a page on even the fastest servers. Doing it the other way would take _one_ query.

Hmmmm, so this speaks to having an Album Artist for every album, compilation or not.... (I knew this was some folk's preference, but didn't realize the mechanics of LMS noted above).

[Philip Meyer]

>No, it does not. It stores a contributor that is used only for album
>sorting (by artist) purposes. It does not store an album artist role in
>the contributor_album table for the album. If there is no ALBUMARTIST
>tag, then every time it puts together an 'album_name by artist' string
>it has to retrieve the artists for every track on the album and then
>parse them to construct the string. This is why, if you have no
>ALBUMARTIST tags for non-compilations, generating a single page full of
>albums requires hundreds of queries. As an example, if you had a page of
>100 albums with an average of 12 tracks per album, it will take a
>minimum of 1200 queries just to generate that one web page. It's little
>wonder that it takes a couple of seconds to spit out a page on even the
>fastest servers. Doing it the other way would take _one_ query.

The thing is, you can have more than one album artist per album.

It is possible to retrieve the correct info in less queries. eg. one query to return a list of unique artists for all songs on an album, so would require 1 query for the list of 100 albums, and 100 queries (1 per album) to get a list of the artists on each album.

If there were a rule that there should only be one album artist per album, then it may as well use album.contributor for quickly returning this. That wouldn't work well for me, because I like having albums appearing under each primary album artist. eg. "No Pussyfooting" appearing under "Robert Fripp" and also under "Brian Eno", rather than nominally choosing only one of those artists to list under. I'd also not like to create an aggregated name, eg "Fripp & Eno" as the album artist for it to only appear under, as I could not then see all Brian Eno work in one list.

[JJZolx]

The thing is, you can have more than one album artist per album.

Just for testing, I have one album like this. There are two 'Ella & Louis' albums in the library. For one of them I have an aggretated ALBUMARTIST

ALBUMARTIST=Ella Fitzgerald & Louis Armstrong
ARTIST=Ella Fitzgerald
ARTIST=Louis Armstrong

but for the other I use two album artists

ALBUMARTIST=Ella Fitzgerald
ALBUMARTIST=Louis Armstrong
ARTIST=Ella Fitzgerald
ARTIST=Louis Armstrong

Here's what the query looks like for SQLite, sorted by artist/year/album:

-- Generate an album/artist list
SELECT ca.album AS album_id,
       CAST(a.title AS CHAR) AS album,       
       a.year,
       GROUP_CONCAT(ca.contributor, ", ") AS album_artist_id,       
       GROUP_CONCAT(CAST(c.name AS CHAR), ", ") AS album_artist,
       GROUP_CONCAT(c.namesort, " ") AS album_artist_sort
FROM contributor_album ca
  INNER JOIN albums a ON a.id = ca.album
  INNER JOIN contributors c ON c.id = ca.contributor
WHERE ca.role = 5
GROUP BY ca.album
ORDER BY album_artist_sort, a.year, a.titlesort;

Generating the following:

[Philip Meyer]

>Makes no difference. The database is set up for M:M relationships as it
>is (albums : contributor_album : contributor). If every album had an
>album artist or -artists- in the database, it would still take just one
>query to generate a list of albums with album artists.
>
I thought you were suggesting that if it always had album artist(s) stored, rather than sometimes needing to fetch artist(s) for tracks, that it would simplift and aid performance. I agree, it would simplify, but performance isn't really impacted (and could be worse, because it would need to store more contributors in the link table?).

There's no real difference if the query has to fetch album artist contributor roles, and/or artist contributor roles.

A query to fetch details that would be required to calculate the display of the albums list (sorted by album), would be something like this:

SELECT a.id, a.titlesort, c.id, c.namesearch, ca.role, a.compilation
FROM contributor_album ca
JOIN albums a ON a.id = ca.album
JOIN contributors c ON c.id = ca.contributor AND ca.role in (1,5)
WHERE c.id=1137
ORDER BY titlesort

Of course, the query would be tweaked a bit depending on settings (eg. add role 4 to pull back Band role instead of reporting artist roles).

For me, using MySQL DB, this returns the artists to report against all albums in my 4000+ album library in less than 50ms. If fetching for a single artist, it's ~ 1ms.

The application can then decide what data to use to render (if compilation, ignore artist roles), rather than overcomplicate the query (or make lots of smaller queries) to do this at SQL level.

>I haven't looked at it in a while. At one time it appeared to be doing
>a query for every track on every album being listed. It may be smarter
>now.
There is a contributor_album table, that holds artist roles required for album queries, so it shouldn't need to search through all possible contributor roles mapped to tracks.

>Still, there's no reason to be doing dozens of queries to generate
>a simple album list that also shows the album artists.
I agree.

I guess this is the way that the DB access layer code works; if specialised SQL were written, it would undoubtedly be more efficient. But it goes through layers that map tables to objects, abstracting the DB engine.

[JJZolx]

With every album having an album artist(s), it also makes it very simple to implement 'Browse Album Artists'.

-- Generate list of album artists
SELECT DISTINCT ca.contributor,
       CAST(c.name AS CHAR) AS album_artist
FROM contributor_album ca
  INNER JOIN contributors c ON c.id = ca.contributor
WHERE ca.role = 5
ORDER BY c.namesort;

If you want to have the VA artist bubble to the top, as it's done now in Browse Artists, all you need to do is manipulate its namesort column in the contributors table.

[Philip Meyer]

>With every album having an album artist(s), it also makes it very simple
>to implement 'Browse Album Artists'.
>
I agree, it's a teeny bit easier to have ca.role = 5, instead of ca.role IN (1,5) and ignoring artists that are on compilations. It's cleaner, easier to understand.

The equivalent query for the current release isn't too bad though:

SELECT DISTINCT ca.contributor, CAST(c.name AS CHAR) AS album_artist
FROM contributor_album ca
JOIN albums a ON a.id = ca.album AND a.compilation IS NULL
JOIN contributors c ON c.id = ca.contributor
WHERE ca.role IN (1, 5)
ORDER BY c.namesort

with roles 2,3,4 to be added if you want band, composer and conductors in the list too.

Query execution time is practically the same for me (both less than 10ms).

For consideration:
1) there would be more rows to write in the DB at scan time (at least another contributor_album row per album).
2) in SBS, adding an album artist per album would break artist navigation for me, as navigating artist links also included the role. This is okay now in LMS, that doesn't include the role in the artist navigation links. But some people don't like that.

>If you want to have the VA artist bubble to the top, as it's done now
>in Browse Artists, all you need to do is manipulate its namesort column
>in the contributors table.
It's not Album Artist=Various Artists that gets put on the top, it's a fixed entry called "Various Artists", that has all Compilations. i.e. not a query to return album artist contributors, but a query to return albums where compilation=1.

So, a browse mode for album artists should just return a straight list of album artists, ordered in artist name order, just as your query has it. "Various Artitsts" would just be a normal artist, sorted under V.

[MrSinatra]

http://bugs.slimdevices.com/show_bug.cgi?id=16362

i thought i remembered another bug for this as well, but i can't seem to find it...