[SOLVED] SQLite is very slow when performing .import on a large table

SQLite is very slow when performing .import on a large table

I'm running the following:

  .mode tabs
  CREATE TABLE mytable(mytextkey TEXT PRIMARY KEY, field1 INTEGER, field2 REAL);
  .import mytable.tsv mytable

mytable.tsv is approx. 6 GB and 50 million rows. The process takes an extremely long time (hours) to run and it also completely throttles the performance of the entire system, I'm guessing because of temporary disk IO.

I don't understand why it takes so long and why it thrashes the disk so much, when I have plenty of free physical RAM it could use for temporary write.

How do I improve this process?

PS: Yes I did search for an previous question and answer, but nothing I found helped.

Solution

The following settings helped speed things up tremendously.

PRAGMA journal_mode = OFF
PRAGMA cache_size = 7500000
PRAGMA synchronous = 0
PRAGMA temp_store = 2