"many (sometimes several thousand) statments" sounds like it could be heavy on memory requirements.
Are you inserting one row per statement or all rows in one statement? The latter would be really hard on memory because SQLite will have to parse the whole statement and generate a gigantic SQL program all in one go, instead of row by row. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von ????????? ????? Gesendet: Montag, 12. Juni 2017 16:53 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [sqlite] Can I increase the performance of sqlite3_exec() or maybe change it to prepare+step? Hello, I am using sqlite C API to migrate a database. Migration consists of many SQL statements that are known in advance. To migrate a DB from version 3 to version 7 the C program does the following: 1. disable foreign_keys (PRAGMA foreign_keys = OFF); 2. open transaction (BEGIN TRANSACTION); 3. execute bunch of statements that migrates the DB to the next version using *sqlite3_exec(db, migrate[version], NULL, NULL, &errMsg)*; migrate[version] is consisting of many (sometimes several thousand) statements; 4. check for foreign_keys inconsistencies (PRAGMA foreign_key_check); 5. commit transaction (COMMIT TRANSACTION); 6. enable foreign_keys again (PRAGMA foreign_keys = ON); 7. vacuums db file (vacuum); I've realized that using the command line tool the migration takes around 8 minutes, but the C program takes around 20 minutes. This time is consumed in point number 3 in the previous list. How can I increase the performance of my program so that it reaches the performance of the command line tool? My first bet is to prepare each individual statement and then execute it. Should it be faster than sqlite3_exec? _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
