2011/9/26 olivier dufour <olivier duff gmail com>: > Hello, > I just want to know if some people has ever worked on SQL query to optimise > long time query. > I see few bugs open for that (632618, 638482 540835, ...). > Most used database (Oracle, sybase, sqlServer, ...) is very scalar and we > have difficulty to query the DB only with table bigger 100K or even 1M. > So Why banshee query is slow with a 10k song lib? > With search in log with regexp (Executed in [5-9][0-9][0-9]), I only get SQL > which take more that 500ms to run. > And it is always the same case. > So I see few perf improvement tips, we can apply in code: > 1) ORDER BY RANDOM ==> RAND() * MAX(ID) > 2) SmartPlayListSource Refresh: delete and recreate cache. Why not just > register event to only update track which have changed. > 3) call ANALYSE (ServiceManager.DbConnection.OptimizeDatabase ()) more often > I see few tips here > too: http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html > Maybe we could use them... > For example, replace (x GLOB 'abc*') can be replaced by (x >= 'abc' AND x < > 'abd'). > Maybe it will improve search query. > The worse use case seems to be importing and searching. so we must focus on > them. > Olivier Dufour When we were at the Hackfest after FOSDEM Alan McGovern cooked up a 10 sec patch that might help a bit. It was unfortunately against an older Hyena and Gabriel didn't seem convinced it was going to make a big difference. Regardless I just found it in my cache of old files if anyone wants to poke at updating it and collecting some numbers. - David
Attachment:
sql.patch
Description: Binary data