View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0004431 | MMW v4 | Framework: Scripts/Extensions | public | 2008-03-01 14:52 | 2020-11-30 15:05 |
| Reporter | Ludek | Assigned To | |||
| Priority | immediate | Severity | major | Reproducibility | always |
| Status | closed | Resolution | fixed | ||
| Product Version | 3.0 | ||||
| Fixed in Version | 3.0 | ||||
| Summary | 0004431: 'Create Reports -> Statistics' is horribly slow on some DBs + reported incorrectly for 'Played' values | ||||
| Description | By using this DB from a user http://www.drathus.com/MM.DB It takes ages before the statistics are shown. There should be reviewed the SQL queries whether they could be optimized somehow. | ||||
| Tags | No tags attached. | ||||
| Attached Files | |||||
| Fixed in build | 1143 | ||||
|
|
Optimized first two pages ('Totals' and 'Avarages') and fixed some bugs - especially 'Played' column of 'Avarages' page showed bad results. Fixed in build 1141. |
|
|
Optimized a lot of further queries. Fixed incorrectly reported values for "Top 10 Albums Played", "Top 10 Artists Played", "Top 10 Genres Played" pages + added missing "Top 10 Albums" page. Fixed in build 1141. Note: Statistics are shown in 10 seconds now for the Drathus' DB |
|
|
Retested on one of mine Libraries and It takes Aprox. 1 Min to show, but problem is that in first 20+Seconds MM is locked, and After that is reasonably slow responsive but reacts on Commands. Do you wish that I UL My Library? |
|
|
Yes, if you are sure that you have tested the DB by using the fixed stats.vbs (located in https://svn1.cvsdude.com/jirik/MediaMonkey/Trunk/Scripts/Stats.vbs) then yes, please write here a link to download the DB. |
|
|
I Have uploaded My settings.rar to FTP. you can use supplied app to Make Exact settings like mine on your PC. If You start that App in Separate Folder you will be able to Backup your Settings prior to applying Mine. Be careful when using this app it can overwrite all MM Settings even in Registry. |
|
|
I tested in on your DB and I have to say that statistics are shown in 20 seconds on my system. I don't see any SQL query taking more than 2 seconds. So I think that this is very good result on such large DB (121 MB, 80k tracks) and I guess that there is nothing to do about query optimization. Also I did't observe any problem related to slow responsive or locked MM. I guess that the difference can be caused by system equipment, I have Win XP SP2, AMD Athlon 64 X2, Dual Core 4000+, 2.10 GHz, 1.93 GB RAM, and you?? |
|
|
Win XP SP2, AMD Athlon 64 X2, Dual Core 4400+, 2 GB RAM, ATi HD3870. I have Sent you manual Debug Log In the Middle of Lock down. Note that I have Lots of Plug-ins Installed. Like for G15 Keyboard, ... You are most likely right. Would You think That Adding Some Debug MSGS will help us to track how Much Time is needed to run execute queries? |
|
|
Reminder sent to: rusty Rusty, This is maybe specific Problem to my settings and number of plugins I have installed. We should possibly Lower the Priority or even resolve issue? |
|
|
Strange, you have almost the same HW equipment, where is the log - I haven't received it :-( (on email? or on FTP?) There is no need to add any debug messages, from the log you can see which SQL query takes a long time and you can also see its SQL structure. e.g. 00000485 6.62664457 [3228] 1732 DB open SQL: SELECT ArtistsSongs.IDArtist FROM ArtistsSongs WHERE ArtistsSongs.PersonType=1 AND ArtistsSongs.IDSong IN (SELECT ID FROM songs WHERE idalbum>0 ) GROUP by artistssongs.idartist 00000486 6.80846923 [3228] 1732 DB lock finished, took 381696956. 00000487 6.84594258 [3228] 1732 DB open SQL: SELECT Artists.ID, Artists.Artist FROM Artists 00000488 6.84594258 [3228] WHERE Artists.Tracks > 0 00000489 6.84621253 [3228] 1732 DB lock finished, took 494364. The former took: 6.80846923 - 6.62664457 = 0,18 seconds The latter took: 6.84621253 - 6.84594258 = 0,00027 seconds |
|
|
Petr Will FWD Eureka Debug Log for you when he finds it in bunch of Debug Logs. I'll check all that Again on clean Install to be 100% sure and report you additional Findings. |
|
|
I don't need Eureka Log, you can simply save a debug log (generated by DbgView app) during creating of statistics and email to me ar attach to this bug. |
|
|
I uploaded Debug log. The Only Thing I have dine from MM start is that I have Started Statistics Creation. |
|
|
From the log I found one query that taken too long (i.e. 25 seconds) on your system 00004673 100.92991638 [1780] 2308 DB open SQL: SELECT Count(Distinct PlayLists.IDPlaylist) AS CountOfID FROM PlayLists INNER JOIN PlaylistSongs ON PlayLists.IDPlaylist = PlaylistSongs.IDPlaylist INNER JOIN Songs ON PlaylistSongs.IDSong = Songs.ID INNER JOIN Played ON Songs.ID = Played.IDSong WHERE (PlayLists.ISAutoPlayList ISNULL or Playlists.IsAutoPlaylist=0) 00004674 105.01103210 [1436] << REGISTERING IN SKYPE 00004675 110.01061249 [1436] << REGISTERING IN SKYPE 00004676 115.01206970 [1436] << REGISTERING IN SKYPE 00004677 120.01361847 [1436] << REGISTERING IN SKYPE 00004678 125.01323700 [1436] << REGISTERING IN SKYPE 00004679 125.09280396 [1780] 2308 DB lock finished, took 86491955. , but strange thing is that it takes only 1 second on my system by using your DB. Could it be somehow caused by the << REGISTERING IN SKYPE messages? i.e. If you don't register Skype during the process is it still so slow? Nevertheless I optimized the query in build 1143 and it should be much faster now! + optimized five more queries. |
|
|
00004674 105.01103210 [1436] << REGISTERING IN SKYPE Is different process than MM in it is used by My Siemens USB Dong for accessing Skype API. Playlist Query. If you observe I have lots of Playlists that Uses Accessible as Condition and My DB on your Computer would render all tracks "dead links". Will Check in 1143 as soon as it become available. |
|
|
Verified 1143 Much Much faster, it is cut in Half maybe even less. Also I was Right about making accessible tracks unavailable. Doing that speed Statistics is like Ludek wrote. Those 2500+ Playlists make problems and slow things down much. |
