Search this site:

2009-12-28

Fast batch insert to SQLite database


Recently I am working with SQLite. Like many of you do, I sometimes need to insert large amount of data to the database. There may be thousands, or even more, of records waiting for you. Inserting them to a SQLite database could be a nightmare that it would work like forever. Asking Google about this you will be probably instructed to fine tune the SQLite parameters. Yes, this is one way but if you are not doing this too frequently, I may have another, somehow dirty, suggestion for you.

Here is what I did:

  1. Somehow you prepared the insert statements in text file (e.g. insert.sql). I got it from my colleague who dumped the whole database with tons of records into SQL format using the .dump command.
  2. Use sqlite3.exe to load your database, to which the data is inserting:
    C:\> sqlite3.exe mydb.db

  3. Backup the database using the .backup command into a file (e.g. original.db):
    sqlite> .backup original.db

  4. If the target tables are not empty, dump them into SQL format using the .dump command too (e.g. data.sql). Better dump other related tables if you have set certain kinds of constrains (e.g. foreign key) too. Then you may quit the utility:
    sqlite> .output data.sql
    sqlite> .dump table1 tabe2
    sqlite> .output stdout

  5. Run sqlite3.exe again without specifying the database file, so that a memory database will be used:
    C:\> sqlite3.exe

  6. Restore from your database backup (original.db in the example) using .restore command:
    sqlite> .restore original.db

  7. If you have done step 4, drop those target tables (and related ones). Load and execute the table data backup with .read command:
    sqlite> DROP TABLE1;
    sqlite> DROP TABLE2;
    sqlite> .read data.sql

  8. Now load and execute the insert statements with .read command. Hey, don't plan to go for tea, it will finish shortly:
    sqlite> .read insert.sql

  9. Backup the database using the .backup command:
    sqlite> .backup new.db

  10. Now you can use to restore the backup to anywhere you like. I restored to a new database file and then replaced my existing one.
As you can see, the idea here is actually very simple: insert to a memory database instead of a file, but this does improve the performance dramatically.

BTW, later I found out that it may be related to the transactions. Making the whole batch as a single transaction may help as suggested in the FAQ. Journal file may also take some time . So temporarily turning off the journal with the PRAGMA journal_mode = OFF; statement before inserting might actually help too. I didn't verified these... Feel free to try it out :p


11 comments:

  1. I dont have experience in SQLite, but here is some other ways to speedup inserts for common DBMS.
    1.Disable autocommit, perform inserts as a transaction, then commit

    2.Disable/drops all indexes on the target tables, perform inserts, recreate indexes

    Cheers,
    KK

    ReplyDelete
  2. How can I run the SQLite console on a mobile device (compact framework)

    ReplyDelete
  3. @pentium10, I guess you can't... What mobile device are you taking about? WM? How about copying the files back to PC to perform the deask?

    ReplyDelete
  4. This will be ongoing sync, so the large batch insert must be run frequently after syncing a large inventory, so it cannot be done offline as it not once.

    ReplyDelete
  5. how about making the insertion a single transaction? here says that it helps

    ReplyDelete
  6. Actually I am doing that already,
    I am using a insert into table1 select from table2 syntax to achieve this. See this: http://stackoverflow.com/questions/2121336/insert-takes-too-long-code-optimization-needed

    ReplyDelete
  7. sorry I was busy in previous days.

    I have done some testing on batch inserting with transactions. It really improves the speed a lot. You may check my another post for details. However, I don't know whether it applies to your case... Are you trying to inserting many data from a table to another as described in your links?

    ReplyDelete
  8. Yeah I want to transfer from one database to another some data... the source is sync database, the destination is the main database... the sync db holds the new changes...

    ReplyDelete
  9. > 1.Disable autocommit, perform inserts as a transaction, then commit

    Please tell us *HOW* to do that, not just "go do it".

    Ugh.

    What would the sql statements look like?

    ReplyDelete
  10. @Janice
    I found this : http://www.sqlite.org/c3ref/get_autocommit.html, which writes:
    Autocommit mode is disabled by a BEGIN statement. Autocommit mode is re-enabled by a COMMIT or ROLLBACK.

    So basically you just achieve that using transaction, e.g.
    BEGIN;
    -- YOUR SQL STATEMENTS HERE;
    COMMIT;

    ReplyDelete

HTML Tags allowed (e.g. <b>, <i>, <a>)

Disclaimer

ALL CONTENTS AND INFORMATION IN THIS WEB SITE ARE PROVIDED "AS IT" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED. THE ACCURACY AND AVAILABILITY OF THE CONTENTS, INFORMATION AND THE WEB SITE ITSELF ARE NOT GUARANTEED. THE AUTHOR TAKES NO RESPONSIBILITIES ON ANY COSTS OR DAMAGES (DIRECT OR INDIRECT) ARISING OUT OF OR IN CONNECTION WITH THE ACCESS, USAGE OR INABILITY OF USAGE OF THIS WEB SITE.