LibraryPanes: Library panes like these in Winamp, iRiver, ..

[Steegy]

Thanks for the requests. Most of them will probably be implemented... that's all I can say. Let them coming...


@SQL-knowledgable people:
Is there any way to make the current queries faster? I mean especially the ones that use the AddSongInfoInt and Lists tables. The artist and album list loading seems to be fast enough, but the loading of the tracks themselves in the tracks view is too slow in my opinion. MM itself can do it much faster.
Just check the Xtra checkbox, choose something like "occasion" as the classification and click on an occasion (or something else) with a lot of tracks in it. It takes quite long to load the tracks view...

Also, what's the exact difference between "distinct" and "distinctrow", also when we're looking at the speed of both. "Distinct" seems to sort automaticly, while "distinctrow" needs an extra "order by". (I usually only request one column for the responce, to fill the tracks view (e.g. Songs.ID).)
I checked the speeds a bit in MSAccess, but these can't really be compared with those of SQL using the Jet engine... I think.

[Bex]

Select Distinct and Select Distinctrow are much slower then just Select and should be avoided if possible.
The difference between Distinct and Distinctrow is pretty good explained here:
http://office.microsoft.com/en-au/assis ... 51033.aspx

Regarding speed there are some things one should remember.
A SQL statement is read in reversed order, from bottom to top.
All links (or where clauses) which eliminates records should come first (at bottom).
It's faster to link to an indexed field. (e.g. tableA.field1 = tableB.indexedfield1)
Sometimes the SQL gets faster if you change the order of the links and/or the tables in the from clause. Just test some.
Inline selects (e.g. views) are slow and should be avoided if possible.

I dont have the time now but later i'll try to examine your SQL code in this script and try to speed it up.

/Bex

[Steegy]

OK, thanks Bex!

I changed some things like (in a not-posted version):
- now use the select order of select fields as they are actually in the table
- now use WHERE Exists (SELECT Null FROM ...) as faster internal query than WHERE Songs.ID IN (SELECT DISTINCT Songs.ID FROM ...) (well, it should be faster) --> an inline query is needed: that's how "AddTracksFromQuery" works.
- some other tweaks

But almost no change in speed .
I now even use the exact same SQL query that MM uses, and still MM is 2 to 3 times faster in loading a tracks list for a specific occasion:

Code: Select all

SELECT Songs.*, Artists.Artist, Albums.Album, AlbArt.Id as IDAlbArt, AlbArt.Artist as AlbArt 
FROM Songs, Artists, Albums, Artists AlbArt 
WHERE Songs.IDAlbum=Albums.ID AND Songs.IDArtist=Artists.ID AND AlbArt.ID=Albums.IDArtist  
AND Exists (SELECT Null
            FROM AddSongInfoInt
            WHERE DataType=10103 AND IntData=33 AND AddSongInfoInt.IDSong=Songs.ID
            ORDER BY Songs.SongTitle, Artists.Artist
)

Only the part "AND Exists (SELECT Null ..........)" (the inline query) can be choosen. The things before it are always the same.

So it seems that no noticable improvement can be done on the SQL query, to make the script faster.


@Devs: is there any reason why MM is faster in doing the above than a script is, for exacly the same query?


Cheers
Steegy

[Nebbin]

I like where this is heading, but it's impractical for me to use in it's current form, unless I'm missing something.

As it stands, it only lists all artists, be they Artist or Album Artist. I know it's all kept in the same column in the DB, but it then only shows albums & tracks as if the selected name was ONLY an Artist. Thus, if you click on an album artist name you get no tracks or albums listed, which makes it messy and unusable for me. I have a lot of albums (>400) where the Album Artist name has no matching artist name.

[Steegy]

Before anyone asks: there are some inconsistencies in the SQL queries (between Albums, Artists and Album Artists). Maybe these can give "unexpected" listings as result. One reason for that is that I'm not really sure what the best solution is (when use Album ARtist, when use Artist). But it will be fixed.

The Album Artist VS Track Artist inconsistency will be fixed, and I plan to make things choosable (like some requested here).
Personally, I like to have everything is the lists: Album Artists and Track Artists together, and all albums of the chosen album/track artist. But I was not yet sure about this at "release time", so that's why it's kind of inconsistent for the moment.
A choice (like the one you seemingly want too) will be to show only album artists and the albums of these album artists + these tracks. Another choice may be to show only track artists with the albums where they appear in (as track artist) + these tracks (as it is now, but then with the album artists kept out of the list).

Cheers
Steegy

[gab]

Steegy - Awesome script (functionality should be built into mm imo). Is there a way to hide the Library Pane title like you can with the now playing window?

[Steegy]

No, that's currently not possible through scripting. Hopefully it will get implemented in one of the next versions of MediaMonkey, as I would like to hide the titles of all my dockable panels too.

[Bex]

Well Steegy, the problem with your SQL is indeed the inline query. It can however easily be incorporated in the outer SQL which would produce the same resullt and make the SQL lightning fast. I assume you know how todo ithat.

So I guess you need to rewrite the logic in your script so it can use SQL's without inline queries.


Cheers
/Bex

[Steegy]

Only the part "AND Exists (SELECT Null ..........)" (the inline query) can be choosen. The things before it are always the same. The inline query is not needed if we can give ID's (e.g. Songs.ID, Artists.ID, Albums.ID, ..., but since we have to give it

So an inline query is required to get songs from the database. There's no other way to do it

From the Scripting Reference:

Adds a set of tracks to the tracklist window. The tracks added are those selected by the supplied SQL. It is not a full SQL statement but only a condition (WHERE part).

So it's a strange thing that MM can to the exact same query 2 to 3 times faster. (And the script is only one function call, so that doesn't explain it.)
But maybe MM itself can be improved so it uses no more inline queries. That's another one for the devs then.

But, Woooooooooooooooooooooooooow... this is cool!.
I just used my original query again and removed the DISTINCT (songs are (should be) only once in the DB anyway). It now is superfast!!!

But still, MediaMonkey itself can probably be made faster by using JOINS so that no inline queries are necessary.

[Bex]

Cool Steegy!
Glad you sorted it out!

From the Scripting Reference:

Adds a set of tracks to the tracklist window. The tracks added are those selected by the supplied SQL. It is not a full SQL statement but only a condition (WHERE part).

I didnt understand that AddTracksFromQuery demanded to use only the WHERE clause. That's quite a limitation sometimes.

So it's a strange thing that MM can to the exact same query 2 to 3 times faster. (And the script is only one function call, so that doesn't explain it.)
But maybe MM itself can be improved so it uses no more inline queries. That's another one for the devs then.

I'm sure they use a smarter query than what can be produced with AddTracksFromQuery.

However I might have found a workaround that can be used. The idea is to create an indexed temp table which you populate with the data from the inline and use in the WHERE clause of AddTracksFromQuery. Something like this:

Code: Select all

1) CREATE TABLE TEMPX1 (ID INTEGER unique);
2) INSERT INTO TEMPX1 SELECT SONGS.ID FROM SONGS,Sometable,someothertable WHERE etc;
3) perform AddTracksFromQuery with this inline in the end: "AND SONGS.ID IN(SELECT TEMPX1.ID FROM TEMPX1)"
4) DROP TABLE TEMPX1;

It must performed in steps. The name of the temp table has to be nonexisting so be sure to select something unique. It also has to be deleted (DROP) so you dont get an error next time you want to create it.
Using an inline like this on an indexed table is fast!

I dont know if this workaround is possible to do in a script. But it would be cool if it was.

Cheers
/Bex

[A_Disciple]

Awesome Script !!

[BigMatty]

This is the best script I've ever used, one request tho...

With the 'xtra' pane enabled, the ability to choose 'Year' and 'Rating' would be awesome.

[Steegy]

OK. Besides Year and Rating, there will also be the CustomX fields and the Artist's first letter added to the list.

[gab]

Something else to add to the wishlist of my favorite script. When you click on an Artist or Album in the panes the album art window is blank (yoiu have to click on one of the tracks in the track window). It would be nice if the Album Art was alwaya displayed. Maybe default to the first track in the track list

[Steegy]

Ok, that was something I forgot. It's fixed for the next version (not yet posted). Thanks.