Re: [gnome-db] execute raw sqlite statements

[Vivien Malerba <vmalerba gmail com>]

On 30 May 2010 18:06, Massimo Cora' <maxcvs email it> wrote:
> Hi Vivien,
>
> I'm facing a performance problem when I'm inserting ~20k items on a table.
> Say I've got a long sql transaction string like
>
> BEGIN TRANSACTION;
> INSERT INTO sym_type (type_type, type_name) VALUES ('macro',
> 'g_marshal_value_peek_boolean');
> INSERT INTO sym_type (type_type, type_name) VALUES ('macro',
> 'g_marshal_value_peek_char');
> [...];
> COMMIT;
>
> I need the quickest way to insert them into the table.
> I don't find on libgda a method to speak directly with SQLite, and I'm
> 'forced' to use GdaStatement(s) to process the string and then execute them.
> I'm not interested in using objects or particular infos about the sql I'm
> going to execute.
>
> When using the cmd line with sqlite3 I get:
>
> pescio tal:/tmp$ time sqlite3 foodb < debug_sql.log
>
> real    0m0.600s
> user    0m0.452s
> sys     0m0.008s
>
>
> on the contrary using libgda it takes ages, about 50-60 seconds.

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.

> It would be really great if you could provide an API to have direct access
> to db engine. It would of course risky for user because it has no control on
> errors, sql correctness etc, but it seems to me the only way to have speed.
> Otherwise I'll be forced to bypass libgda and invoke the cmd line, which I
> would prefer to avoid because various problems, like dependencies on sqlite
> etc.

This might be a feature for future versions, but having this will
probably disable some other features of Libgda...

Vivien