Re: [Banshee-List] SQLITE performance

[Bertrand Lorentz <bertrand lorentz gmail com>]

Hi,

A few comments in the text below.

On Mon, Sep 26, 2011 at 7:34 PM, gnomeuser gmail com
<gnomeuser gmail com> wrote:
> 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)

I think there a reason why we're using ORDER BY RANDOM, maybe to make
sure we don't play the same track ?

>> 2) SmartPlayListSource Refresh: delete and recreate cache. Why not just
>> register event to only update track which have changed.

I'm not sure I understand what you mean.

>> 3) call ANALYSE (ServiceManager.DbConnection.OptimizeDatabase ()) more often

ANALYZE just compute basic statistics for each table and store the
data in the sqlite_stat1 table. So it will only have a real effect if
the number of rows has changed significantly since the last time it
was called.
That's what the following code does :
http://git.gnome.org/browse/banshee/tree/src/Core/Banshee.Services/Banshee.Database/BansheeDbConnection.cs#n120

>> 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.

I've seen several cases where a smart playlist like 'Unheard' that
needs to be refresh on each track change created a significant CPU
load. So any improvement there would be great.

For anyone who wants to work on performance, a general piece of advice
in 3 words :
Measure. Measure. Measure.
Before you make changes, while you make changes, and after you've made changes.

A good tool for that is the performance testing tool created by
Gabriel, which can be found in the test/ folder in the Banshee source
tree.
Which reminds me that it's been a while since I last ran it...

> 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.

It looks like updating the patch would be easy, but I'm not sure I
understand the goal of the patch: it seems to me it make all queries
run on the main thread.
Anyway, I'd be happy to see numbers about it, see above ;)

-- 
Bertrand Lorentz