Re: [gnome-db] execute raw sqlite statements

[Vivien Malerba <vmalerba gmail com>]

On 30 May 2010 23:52, Massimo Cora' <maxcvs email it> wrote:
> Hi Vivien,
>
> On 05/30/2010 06:20 PM, Vivien Malerba wrote:
>>
>> On 30 May 2010 18:06, Massimo Cora'<maxcvs email it>  wrote:
>>
>> There is no way you can avoid using a GdaStatement right now. However
>> there is no reason it could be made as fast as or even faster than
>> using raw SQLite...
>>
>> Here is how:
>> * create a single GdaStatement by parsing "INSERT INTO sym_type
>> (type_type, type_name) VALUES (##type::string, ##name::string)"
>> * get the GdaSet object representing the "type" and "name" parameters
>> in the statement
>> * loop over what you need to insert, each time setting values in the
>> GdaSet for the values you need to insert, and calling
>> gda_connection_statement_execute_non_select(). Pass NULL for the
>> last_insert_row parameter if you don't need it.
>> * discard the GdaStatement and the GdaSet
>>
>> I've found that using several successive transactions containing about
>> 1000 inserts each is faster than not using any transaction and faster
>> than using a single big transaction.
>>
>
> my tests pointed out this:
>
> sqlite cmd line: 0.6 sec for 20965 symbols.
> libgda single big transaction: 2.94 seconds.
> libgda multiple transactions (1000 symbols each): 2.99
> libgda connection_execute non select (raw sql): 45 seconds.
>
> SQLite remains the fastest. There's some overhead on libgda, I don't know
> how it performs on older pcs though.
> I'll have some checks.
>

For your information, I've just checked in a modification which, if
the GDA_CONNECTION_OPTIONS_THREAD_SAFE flags is specified, will not
automatically execute a sub thread (if the "normal" connection already
supports multi thread access).

As SQLite is normally already thread safe, you will have performances
increases if you use the GDA_CONNECTION_OPTIONS_THREAD_SAFE flag.

Vivien