[Banshee-List] SQLITE performance

[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