Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Faster bulk inserts in sqlite3?

I have a file of about 30000 lines of data that I want to load into a sqlite3 database. Is there a faster way than generating insert statements for each line of data?

The data is space-delimited and maps directly to an sqlite3 table. Is there any sort of bulk insert method for adding volume data to a database?

Has anyone devised some deviously wonderful way of doing this if it's not built in?

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

like image 205
scubabbl Avatar asked Dec 12 '08 20:12

scubabbl


People also ask

Does SQLite support bulk insert?

SQLite doesn't have any special way to bulk insert data. To get optimal performance when inserting or updating data, ensure that you do the following: Use a transaction. Reuse the same parameterized command.

Why bulk insert is faster than insert?

In case of BULK INSERT, only extent allocations are logged instead of the actual data being inserted. This will provide much better performance than INSERT. The actual advantage, is to reduce the amount of data being logged in the transaction log.

Is sqlite3 faster than MySQL?

SQLite 2.7. 6 is often faster (sometimes more than twice as fast) than MySQL 3.23. 41 for most common operations. SQLite does not execute CREATE INDEX or DROP TABLE as fast as the other databases.


2 Answers

  • wrap all INSERTs in a transaction, even if there's a single user, it's far faster.
  • use prepared statements.
like image 52
Javier Avatar answered Sep 23 '22 23:09

Javier


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 loading command is not SQL but rather a custom feature of SQLite. As such it has a weird syntax because we're passing it via echo to the interactive command line interpreter, sqlite3.

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

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

One last thing: remember to be careful with the value of .separator. That is a very common gotcha when doing bulk inserts.

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

You should explicitly set the separator to be a space, tab, or comma before doing .import.

like image 33
ramanujan Avatar answered Sep 22 '22 23:09

ramanujan