Faster bulk inserts in sqlite3?

Question

I have a file of 30,000 rows of data that I want to upload to sqlite3 database. Is there a faster way than creating insert statements for each row of data?

Data is separated by spaces and displayed directly in the sqlite3 table. Is there any volume insertion method to add volume data to the database?

Has anyone come up with any tricky way to do this if it's not built in?

I should preface this by asking if there is a C ++ way to do this from the API?

Answer 1

You can also try setting a few parameters to get extra speed. In particular, you probably want PRAGMA synchronous = OFF; .

Answer 2

• to wrap all INSERT in a transaction, even if there is one user, it is much faster.
• Use prepared instructions.

Answer 3

You want to use the .import command. For example:

 $ cat demotab.txt 44 92 35 94 43 94 195 49 66 28 135 93 135 91 67 84 135 94 $ echo "create table mytable (col1 int, col2 int);" | sqlite3 foo.sqlite $ echo ".import demotab.txt mytable" | sqlite3 foo.sqlite $ sqlite3 foo.sqlite -- Loading resources from /Users/ramanujan/.sqliterc SQLite version 3.6.6.2 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select * from mytable; col1 col2 44 92 35 94 43 94 195 49 66 28 135 93 135 91 67 84 135 94 

Note that this bulk load command is not SQL, but a custom SQLite function. Thus, it has a strange syntax because we pass it through echo to the interactive command line interpreter, sqlite3 .

In PostgreSQL, the equivalent of COPY FROM : http://www.postgresql.org/docs/8.1/static/sql-copy.html

In MySQL, this is LOAD DATA LOCAL INFILE : http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Last: don't forget to be careful with the .separator value. This is a very common problem when doing bulk inserts.

 sqlite> .show .separator echo: off explain: off headers: on mode: list nullvalue: "" output: stdout separator: "\t" width: 

You must explicitly set the delimiter as a space, tab, or comma before executing .import .

Answer 4

• Increase PRAGMA default_cache_size to a significantly larger number. This will increase the number of cached pages in memory.

• Wrap all inserts in one transaction, not one transaction per line.

• Use compiled SQL statements for insertion.
• Finally, as already mentioned, if you want to refuse full ACID compliance, set PRAGMA synchronous = OFF; .

Answer 5

RE: "Is there a faster way to create insert statements for each row of data?"

First: reduce it to two SQL statements using the Sqlite3 Virtual table API , for example.

 create virtual table vtYourDataset using yourModule; -- Bulk insert insert into yourTargetTable (x, y, z) select x, y, z from vtYourDataset; 

The idea here is that you implement a C interface that reads your source dataset and presents it in SQlite as a virtual table, and then you copy SQL from the source to the target table at a time. It sounds more complicated than it really is, and I measured huge speed improvements this way.

Second: use the other recommendations presented here, that is, pragma settings and the use of a transaction.

Third: Perhaps see if you can do away with some indexes in the target table. This way sqlite will have fewer indexes to update for each row inserted.

Answer 6

There is no way to insert an insert, but there is a way to write large chunks in memory and then transfer their database. For the C / C ++ API, simply do:

sqlite3_exec (db, "START TRANSFER", NULL, NULL, NULL);

... (INSERT instructions)

sqlite3_exec (db, "COMMIT TRANSACTION", NULL, NULL, NULL);

Assuming db is your database pointer.

Answer 7

A good compromise is to wrap your INSERTS between BEGIN; and the end; ie keyword:

 BEGIN; INSERT INTO table VALUES (); INSERT INTO table VALUES (); ... END; 

Answer 8

Depending on the size of the data and the amount of RAM available, one of the best performance gains will be by installing sqlite to use the all-in-memory database, rather than writing to disk.

For in-memory databases, pass NULL as an argument to the sqlite3_open file name and ensure that TEMP_STORE is defined appropriately

(All of the above text comes from my own answer to a separate sqlite related question )

Answer 9

If you just paste once, I may have a trick for you.

The idea is simple, first inserted into the memory database, then backed up and finally restored to the original database file.

I wrote detailed steps to my blog . :)

Answer 10

I found this a good mix for one-shot imports.

 .echo ON .read create_table_without_pk.sql PRAGMA cache_size = 400000; PRAGMA synchronous = OFF; PRAGMA journal_mode = OFF; PRAGMA locking_mode = EXCLUSIVE; PRAGMA count_changes = OFF; PRAGMA temp_store = MEMORY; PRAGMA auto_vacuum = NONE; .separator "\t" .import a_tab_seprated_table.txt mytable BEGIN; .read add_indexes.sql COMMIT; .exit 

source: http://erictheturtle.blogspot.be/2009/05/fastest-bulk-import-into-sqlite.html

Additional information: http://blog.quibb.org/2010/08/fast-bulk-inserts-into-sqlite/