Re: [gnome-db] execute raw sqlite statements

[Massimo Cora' <maxcvs email it>]

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.

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

yeah that could be true but if users are always using the raw api to 
access sqlite they wouldn't need libgda. It should be intended only for 
particular cases.

thanks and regards,
Massimo