Edit Question - KnowledgeHub

Title Body

I am using SQLite3 in one of my projects and I need to ensure that the rows that are inserted into a table are unique with regard to a combination of some of their columns. In most cases the rows inserted will differ in that respect, but in case of a match the new row must update/replace the existing one. The obvious solution was to use a composite primary key, with a conflict clause to handle collisions. Thefore this: CREATE TABLE Event (Id INTEGER, Fld0 TEXT, Fld1 INTEGER, Fld2 TEXT, Fld3 TEXT, Fld4 TEXT, Fld5 TEXT, Fld6 TEXT); became this: CREATE TABLE Event (Id INTEGER, Fld0 TEXT, Fld1 INTEGER, Fld2 TEXT, Fld3 TEXT, Fld4 TEXT, Fld5 TEXT, Fld6 TEXT, PRIMARY KEY (Fld0, Fld2, Fld3) ON CONFLICT REPLACE); This does indeed enforce the uniqueness constraint as I need it to. Unfortunately, this change also incurs a performance penalty that is way beyond what I expected. I did a few tests using the sqlite3 command line utility to ensure that there is not a fault in the rest of my code. The tests involve entering 100,000 rows, either in a single transaction or in 100 transactions of 1,000 rows each. I got the following results: | 1 * 100,000 | 10 * 10,000 | 100 * 1,000 | |---------------|---------------|---------------| | Time | CPU | Time | CPU | Time | CPU | | (sec) | (%) | (sec) | (%) | (sec) | (%) | --------------------------------|-------|-------|-------|-------|-------|-------| No primary key | 2.33 | 80 | 3.73 | 50 | 15.1 | 15 | --------------------------------|-------|-------|-------|-------|-------|-------| Primary key: Fld3 | 5.19 | 84 | 23.6 | 21 | 226.2 | 3 | --------------------------------|-------|-------|-------|--

Tags (comma-separated) Cancel