I've been inserting data into a table with a non-integer primary
key. The data does not come in in an ordered fashion. My thought is
that perhaps performance suffers because it needs to rewrite huge
chunks of the data file in order to slot the new data into its
correct position within the file based on the sorting of the primary
key.
No. SQLite uses the B-Tree algorithm. Inserting a new value in
the middle of a table involves rewriting 4 or 5 pages in the worst
case. The usual case is to rewrite just the one page where the value
is being inserted.
That makes sense, okay. Perhaps my problem is that my data is too
big. Is a page around 1k? I often insert blob data in the 64k
range. Perhaps that is my problem.
l8r
Sean
