On 12 Jun 2017, at 3:53pm, Венцислав Русев <ven...@proxima-3.com> wrote:
> To migrate a DB from version 3 to version 7 the C program does the following: This migration is a one-time process, right ? Each customer has to do it only once, then never again. It not like they have to wait through it every day. You could just put up a "This will take a long time but just once." message. A lot of this comes down to how unacceptably slow your current method is. If it’s almost fast enough, then it’s probably not worth doing much work on it. If it’s so slow your customers are complaining, then it’s worth putting programming time into it. > 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); Speedup 1: If "several thousand" is more than ten thousand, try using several transactions limited to ten thousand rows each. Do you have lots of indexes ? When importing data it’s faster to do CREATE the tables (or copy a database which has empty tables in) INSERT the rows CREATE the indexes Than to do the INSERTing last. Please add the ANALYZE command after your existing VACUUM. This can noticeably speed up any future operations on your database. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
