[CentralOH] sqlite3

[Mark Erbaugh]

I'm having a performance issue with sqlite3.  I have a Python program that reads in an file output from another system over which I have no control, parses the records in that file and inserts the data as rows into a sqlite table.  The table has an INTEGER PRIMARY KEY field.  The input files have150k to 300k records.  When the records in input file are already in order by the primary key, the inserts are at least an order of magnitude faster. For example 10 seconds versus 200.

If I remove the PRIMARY KEY constraint, the insert speed improves. However, there can be duplicate keys in the input data which need to be dealt with (either ignored or replace the previous row).

The brute force solution would be to read the data into a list and sort the list before inserting it into sqlite, but I wonder if there is a sqlite setting I'm missing.

Thanks,
Mark