SQLite write (insert) performance doesn't seem very good to me so I'm
trying to find ways to improve it in my application. I had this idea
so I figured I'd run it past you folks who likely better know how the
db works under the hood.
My thought is that perhaps the write performance suffers because the
data is being written into the data file in some particular sorted
order. Thus it needs to re-order what is already there to insert
content into the middle of the file at times rather than being able
to just append new content onto the end.
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. Would I get a performance improvement if I gave my table an
auto_increment integer primary key and just added an index to the
column I'm currently using as my primary key instead? The thinking
is that this would then mean that the data would be inserted and thus
sorted within the data file in numerical order based on the
auto_increment column and thus the data would be much more often
appended at or near the end of the data file which is a much faster
operation than trying to put something into the middle of a large
(and growing) file.
Does that make any sense at all or am I reaching too far here? :-)
l8r
Sean
