Fastest way to insert 1M + items into Sequel (Sqlite) db?

Question

I have a Sqlite database that I'm using Ruby's Sequel to insert data into.

When attempting to insert 1M+ items it seems to be quite slow.

The code I'm using to do this is below.

DB[:my_table].multi_insert to_insert

Are there any faster ways to do this? Am I missing something?

Have you tried disabling some of the safety features temporarily? — Niklas B., May 11 '14 at 11:30
See http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite — Niklas B., May 11 '14 at 11:31
@NiklasB. I'm not sure how that link applies to this question because it doesn't have any Ruby code in it. I don't really know C. — Nathan McCallum, May 11 '14 at 11:39
The relevant parts are the sql pragmas and the suggestion to use prepared statements (which sequel probably does already) — Niklas B., May 11 '14 at 11:40

score 0 · Answer 1 · answered May 11 '14 at 12:03

0

I found that I got better performance wrapping the code in a transaction.

DB.transaction do
    DB[:my_table].multi_insert to_insert
end

I'm not satisfied that this is as fast as it can be, but it runs in just over 2 mins now, which should be Ok. Oh well, case closed.

answered May 11 '14 at 12:03

Nathan McCallum

1

You should definitely try `PRAGMA synchronous=off` and `PRAGMA journal_mode = MEMORY` in addition to that. Also, if your table has an index, consider removing it and recreating it *after* the bulk insert. You can read more about SQLite tuning in [the FAQ](http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html) – Niklas B. May 11 '14 at 12:07

1 Answers1