On 30 May 2010 18:06, Massimo Cora' <[email protected]> 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: > > pes...@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 _______________________________________________ gnome-db-list mailing list [email protected] http://mail.gnome.org/mailman/listinfo/gnome-db-list
