View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0003089 | MMW v4 | Player | public | 2007-06-05 15:46 | 2007-06-12 14:27 |
| Reporter | jiri | Assigned To | |||
| Priority | urgent | Severity | minor | Reproducibility | always |
| Status | closed | Resolution | fixed | ||
| Product Version | 3.0 | ||||
| Fixed in Version | 3.0 | ||||
| Summary | 0003089: Speed problems of Auto-DJ | ||||
| Description | Auto-DJ has problem in several cases: 1. If none of the tracks in Library is accessible - in such a case it attempts to add tracks, probably endlessly. 2. If there are _many_ tracks in Library, Auto-DJ is very slow. The main reason probably is that FillSongListRandomSong() uses ORDER BY on all tracks. Both problems can be easily reproduced using a big DB (~107k tracks) from our user that's uploaded to FTP, BigDB folder. | ||||
| Additional Information | Suggested solution: 2. a. Instead of using 'ORDER BY sin(songs.id + filelength*(1+%.7f) + filemodified*(1+%.7f) + %.7f)' which is there since MS Access times, we could use 'ORDER BY random(*)' that's available in SQLite. However, this probably won't help much and so I'd rather prefer b. Use a solution I proposed in SQLite mailing list, i.e. in order to get a random record from a table, the following can be used: SELECT * FROM Table LIMIT 1 OFFSET round((CAST(random(*) as float)/(9223372036854775807)+1)/2*(SELECT COUNT(*) FROM Table)) Some tweaks might be needed, it can e.g. be divided into two queries, some error handling might be needed, filters have to be taken into account, etc. | ||||
| Tags | No tags attached. | ||||
| Fixed in build | 1038 | ||||
|
|
Note: Only 1. can be easily reproduced using the big DB from BigDB folder, because the DB is full of deadlines and Auto-DJ doesn't add a deadlines at all. 1. It is fixed 2. I have resolved it another (my own way) which is the fastest. Tested on my DB with 5500 songs and here are a test results for one song adding: - Original solution takes 1 s - Using of 2a ('ORDER BY random(*)') takes 1 s - Using of 2a with 'LIMIT' clause takes 0,3 s - Using of 2b takes 0,05 s. - Using of my solution takes 0,0024 s So the my solution is the fastest and in addition 2b would not work right because COUNT(*) could not match the top ID of the table which has to be used in this case. |
|
|
As discussed over IM, 2b. will be implemented and will be used even when some filters are defined. |
|
|
Fixed this according to Jiri's 2b suggestion/solution |
|
|
Verified 1039. |
