3.1 - Stats Report Based on Filters Modification Request

[nynaevelan]

I'm using NP - All Playcounts (No Xmas), it's still running but I am 8 minutes in.

EDIT: BTW, now that you mentioned it how do I sort the MN nodes??

[ZvezdanD]

how do I sort the MN nodes??

I am not sure that I understand. If you mean about their position, you could move one by one manually using the spinbox after the Move label in the Settings dialog box or Up and Down buttons in the Export/Import dialog box. You could also open the Export/Import dialog box and click on the header of the table, after that all masks/nodes would be alphabetically sorted, but this option could sort only all nodes from the .ini file, not some specific node or group of nodes.

[gege]

First let me say thank you for taking the time and creating it for me

For us. Don't be so selfish!

[nynaevelan]

First let me say thank you for taking the time and creating it for me

For us. Don't be so selfish!

My apologies I meant for us.

Nyn

[gege]

it's a little slow but I can live with that to get the filtered info.

Nyn, can you define "little slow", please?
For me, it took 20min, with 100% of CPU usage, until I gave up and killed MM process.
Looks like that query is too much for my 4 years old Athlon 2600+

[ZvezdanD]

OK, here is what you could try. Replace the line 643:

Code: Select all

        strSQL = strSQL & " AND" & sMMFilter & ")) AND" & sMMFilter

with the:

Code: Select all

        strSQL = strSQL & ")) AND" & sMMFilter

It would be much faster.

[Mizery_Made]

This should not be a problem for a script writer to customize the Stats.vbs script.
Question is whether this would be valuable to have it native (i.e. bundled with MM install package).

What would be valuable is an updated Statistics script. There's some issues with the one that's apparently been the same since 2005, such as the "Artists" section showing all people, including conductors, composers, etc. Also, some of the statistics seem odd, like the "Played" column for Albums will max out once they've all been played, but you can end up with something like "2928" & "4103" for Library & Played respectively for Artists, when everything has been played in your library.

As for this version, with Filters support, it looks interesting. I've installed it now and might find some use for it in the near future, because it's a good idea.

[gege]

It would be much faster.

Muuuuuuuuuuuch better! Thanks for fixing it for me... Yeah, I know, I'm evil

[ZvezdanD]

the "Artists" section showing all people, including conductors, composers, etc. Also, some of the statistics seem odd, like the "Played" column for Albums will max out once they've all been played, but you can end up with something like "2928" & "4103" for Library & Played respectively for Artists, when everything has been played in your library.

I noticed also this issue with the Artist section which could be easily resolved, but I don't know what you mean with the Played column for Albums.

[nynaevelan]

OK, here is what you could try. Replace the line 643:

Code: Select all

        strSQL = strSQL & " AND" & sMMFilter & ")) AND" & sMMFilter

with the:

Code: Select all

        strSQL = strSQL & ")) AND" & sMMFilter

It would be much faster.

Z:

This increased the speed tremendously, now it is just as quick or quicker with some filters. I need one more thing than I will be content. Can you make an installation package with the two fixes included and Gege's script.ini info?? While I will be using this rather than the original one which comes with MM, I would rather not have it have the same name to avoid it being overwritten everytime I update MM. And as a beta tester, I update alot.

There were a couple of filters that did not complete quickly but they never finish quickly even when I use them in nodes, they are the songs in playlist/songs not in playlist filters. If you release this publicly you might want to warn users that if they have filters that normally take a significant amount of time to complete, expect the same thing with the statistics report.

THANK YOU FOR THIS SCRIPT AND FOR YOUR QUICK RESPONSE TO MY/GEGE'S REQUEST.

Nyn

[ZvezdanD]

There were a couple of filters that did not complete quickly but they never finish quickly even when I use them in nodes, they are the songs in playlist/songs not in playlist filters.

Yeah, I noticed this as well and tried to see if I could do something about it, but now I need to go to sleep, here is 2:40 AM in my time zone

I will try to implement all your suggestions.

[Mizery_Made]

but I don't know what you mean with the Played column for Albums.

Code: Select all

Totals
Type      Library    Played
Artists    2928       4103
Albums      812        812
Genres        7          7
Years        26         26
Playlists     8          8


As you can see, it first gives you a total of said item, such as I have 7 Genres in my library, then in the second column it shows you how many of them have been played. Every track in my library has been played at least once, thus it shows that all 7 genres have been played. However, as you can see, there's a difference between the total number of Artists, and the number that have been played. I've yet to figure out why these numbers are different. I guess it might have something to do with it showing Conductors and such in the Top Artists section, maybe one of those two columns only counts "Artists" and the other counts "People" (Artists + Conductors, Composers, etc.). Not really sure.

[Mizery_Made]

I got a little bored and did some digging around.

Code: Select all

  'Totals
  strSQL = "SELECT Count(*) AS Nombre FROM Artists WHERE ID <> 0 AND Tracks>0"     ' Track artists only
  Set qryStats = SDB.Database.OpenSQL(strSQL)
  intArtistsCount = CLng(NoNull(qryStats.ValueByName("Nombre"),0))
 
  ' process background threads
  SDB.ProcessMessages

  strSQL = "SELECT Count(Distinct Artists.ID) AS CountOfID FROM Artists WHERE Artists.ID IN ( SELECT ArtistsSongs.IDArtist FROM ArtistsSongs WHERE ArtistsSongs.IDSong IN ( SELECT Played.IDSong FROM Played))"
  Set qryStats = SDB.Database.OpenSQL(strSQL)
  intArtistsCountPlayed = CLng(NoNull(qryStats.ValueByName("CountOfID"),0))

There's the code snippet from the original file. Looking at the SQL that finds the number of Artists in the library, it bases it off of the Artists.Tracks column, which a Person only has a number <> 0 in this column if they are an Artist for a track and not just a conductor or composer, etc (since they have their own columns). Thus, while it's only counting artists, the other that pulls the played one is pulling any person that has an entry in the Played table, regardless if they're a Artist, Conductor, etc. Toying with it, I got this to work as expected:

Code: Select all

  'Totals
  strSQL = "SELECT Count(*) AS Nombre FROM Artists WHERE ID <> 0 AND Tracks>0"     ' Track artists only
  Set qryStats = SDB.Database.OpenSQL(strSQL)
  intArtistsCount = CLng(NoNull(qryStats.ValueByName("Nombre"),0))
 
  ' process background threads
  SDB.ProcessMessages

  strSQL = "SELECT Count(Distinct Artists.ID) AS CountOfID FROM Artists WHERE Artists.ID IN ( SELECT ArtistsSongs.IDArtist FROM ArtistsSongs WHERE ArtistsSongs.IDSong IN ( SELECT Played.IDSong FROM Played) AND PersonType=1)"
  Set qryStats = SDB.Database.OpenSQL(strSQL)
  intArtistsCountPlayed = CLng(NoNull(qryStats.ValueByName("CountOfID"),0))

As you can see, I've added a "PersonType" clause to the Played SQL, which only accounts for people that are Artists (or I believe, Album Artists), which gives me a number that matches the one from the Library query. It seems the "Top Artists" problem can be fixed with a "PersonType" clause too:

Original:

Code: Select all

  strSQL = "SELECT Sum(PlayCounter) AS CountOfID, Sum(SongLength) AS TotalLength, Sum(FileLength) AS TotalFileLength, Artists.Artist "
  strSQL = strSQL &  "FROM ArtistsSongs, Songs, Artists "
  strSQL = strSQL &  "WHERE Songs.ID = ArtistsSongs.IDSong AND Songs.ID IN (SELECT Played.IDSong FROM Played) AND ArtistsSongs.IDArtist = Artists.ID "
  strSQL = strSQL &  "GROUP BY ArtistsSongs.IDArtist "
  strSQL = strSQL &  "ORDER BY Sum(PlayCounter) DESC LIMIT " & intTopCount & ""

  Set qryStats = SDB.Database.OpenSQL(strSQL)
  While Not qryStats.EOF


Edit:

Code: Select all

  strSQL = "SELECT Sum(PlayCounter) AS CountOfID, Sum(SongLength) AS TotalLength, Sum(FileLength) AS TotalFileLength, Artists.Artist "
  strSQL = strSQL &  "FROM ArtistsSongs, Songs, Artists "
  strSQL = strSQL &  "WHERE Songs.ID = ArtistsSongs.IDSong AND Songs.ID IN (SELECT Played.IDSong FROM Played) AND ArtistsSongs.IDArtist = Artists.ID AND ArtistsSongs.PersonType=1 "
  strSQL = strSQL &  "GROUP BY ArtistsSongs.IDArtist "
  strSQL = strSQL &  "ORDER BY Sum(PlayCounter) DESC LIMIT " & intTopCount & ""

  Set qryStats = SDB.Database.OpenSQL(strSQL)
  While Not qryStats.EOF


Again, this returns expected results for me, the "Top # Artists" section now only lists Artists, so I no longer have a Top 10 where half of the entries are producers who rarely if ever appear in the Artist field. I went ahead and set up a Top 10 for my producers too, since I have them stored in the Conductor field (yeah, I know... odd place, but it works for me), with a "PersonType=4" in place of "PersonType=1", woo, go me!

That's really only the beginning of the problems, I think, because in running the script over and over, I began to look closer at other numbers. Some of their purposes make no sense to me at all, or aren't acting as they should be expected to. Take for instance the "Top # Genre" columns. The regular "Top # Genre" and "Top # Genre played" entries are exactly the same. With 33,534 entries in my Played table and a tendency to play my Rap files more than my others, I know Rap should show more than being played 10,003 times when, you guessed it, I have 10,003 Rap files in my Genre, all of which have been played once, and some of which have been played upwards of 30 or 40 times. This Played entry doesn't act like the Artist played entry, which isn't really expected. My Top Artist only has 722 tracks in the library, but in the played entry, shows 3,709 plays, which if it acted like the Genre entry (which I don't think it should), it would only be showing 722 plays.

Another thing I find odd is the Averages section. Since I've played each track in my library once, the Average & Average Played columns show identical information, the only thing different is the "Played Per Day" entry for obvious reasons. Not really sure what this section was going for, but I would expect Played Averages to fluctuate with what I listen to, not to become static once I've played everything once, because then it's no longer relevant information because I could then just look at the Library entry. If my Year average is 1996, I would expect that if I play everything once, then the played Year Average would be 1996, but if I go playing a lot of 2008 files, then I would expect my continued listening to have some kind of impact, such as the average sliding more-so toward what I listen to most (since the older files would have one play each, while the newer ones would have more plays and thus would be closer to the average that I listen to)

After all, if I play two files, one from 1990 and one from 2008 each one time, the average would be 1999. If I then play that second file again, then the average of what I have played would actually be 2002, no? Not still 1999. It's late, so if my math is way off... oops.

Though, it's late, and I've been staring at VBScript coding for a while now (something I barely understand, and that would be stretching it), so maybe my mind has went off into loopy-land and I'm making no sense as everything is viewed as working perfectly fine to the rest of you. *shrugs*

PS -- Rating is the exact same way as Genre down in the Top # sections.

EDIT: I told you it was late, right? Cause I just realized that I had been messing with the Stats script that comes with Monkey, not your modified version. Haha. Though, I doubt you changed much in regards to what I pointed out, since your script when unfiltered presents the same results as the MM Bundled Version.

[Ludek]

You seem to be right.

In M 3.0 we have optimized performance of the queries in the stats.vbs, see:
http://www.ventismedia.com/mantis/view.php?id=4431

But as you indicated we should also try to improve the script somehow or at least fix the issue with PersonType which really looks like bug now.
So I have opened another issue:
http://www.ventismedia.com/mantis/view.php?id=5489
for this purpose.
I believe that some of the points should be fixed before releasing MM 3.1 (e.g. items 2 & 3)

[Mizery_Made]

Indeed you're right about it actually being updated since the time frame I gave, as now that you mention it, I seem to also recall a Bug Entry or Post on here discussing an improved Time or Size formula. So yeah, I admit to speaking wrongly about that, but the notation in the script was freshest in my mind, which only indicates it's coding between December 2004 & January 2005 and not any updates.

I think #2 is definitely a must to fix asap, because while it's fixable by experienced scripters (or people who like to toy around with things like me), the average user probably wouldn't have a clue on where to start. They're sure to scratch there head though if they look in the "Artists" section to see composers, conductors, etc since each of these are entered into separate fields in the properties. Again, that plays into the average user thing, because not everyone opens up the database and is thus aware that all of these fields are stored into one table.

Wow, it was late four hours ago... *goes away*... *for real this time*