Sqlite> create table x(x text primary key not null) This is extremely faked, but notice the difference between in-order insertion and out-of-order insertion: >sqlite sample.db In my case PRIMARY KEY is a string (used to be 38 digit integer, too large for SQLite as I understand). Maybe my two-step process is wrong and there is a better way? I know PRAGMA ignore_check_constraints could turn off CHECK. If I am correct, is there a way to disable or otherwise speed up the second step? I know FOREIGN KEY constrain can be turned off (and I keep it off). Plus, I know there is PRAGMA integrity_check that can be used to verify at the end after everything is inserted. Since I know the data source is a database, I do not need to enforce the check on the fly. I suspect but not sure the slowdown is caused by enforcing uniqueness of the PRIMARY KEY. The second step is slow and it gets slower as database size grows. Create table according to schema with PRIMAMRY KEY and other constraints and INSERT from QQQ into the final destination. This makes all columns TEXT since no type is available. import of CSV file into a table QQQ that will be deleted once all finished. I do this in two steps (all within sqlite3 shell): I therefore have to re-create schema, clean up (NULL versus empty string, convert to real, etc). These are dumps from existing database (unfortunately). I have large CSV files (30GB, ~100M records ).
0 kommentar(er)
