As I understand SQL, "alter table blah add primary key (blah1, blah2)"
should be how you do it. The sqlite documentation seems to say
otherwise. Actually, I cannot figure out from the documentation how to
add a primary key after the table is created and data is entered. How is
it done? Or is an Index sufficient?
Now that I've asked the question, here's some FYI from my experimentation.
I have a table that looks like this:
CREATE TABLE IF NOT EXISTS results (qi INTEGER NOT NULL, ri INTEGER NOT
NULL, run INTEGER NOT NULL,
drl INTEGER NOT NULL, score INTEGER NOT NULL, qis INTEGER NOT NULL, ris
INTEGER NOT NULL );
I originally had this line as well: , PRIMARY KEY (qi, ri, run)
I need to do billions (no joke) of consecutive inserts into this table.
Writing binary out with std::ofstream was using 26% of my code execution
time according to the profiler. I changed to sqlite3, and now the
sqlite3_step function uses 50% of my execution time. After reading the
news groups, I removed the primary key. That dropped it down to about
41%. That was significant. However, I was still significantly slower
than binary writes with ofstream. Then, I tried the PRAGMA temp_store =
2. That made absolutely no difference. I'll assume that's what it was to
begin with, though it reports 0. Also, from the profiler, it seems that
the step function does a lot of memory allocations and deallocations,
yet I cannot find them in the code.
If it helps, I was testing 600k inserts in transactions of 0xFFF inserts
and my current settings:
PRAGMA auto_vacuum = 0; \
PRAGMA case_sensitive_like = 1; \
PRAGMA cache_size = 32768; \
PRAGMA default_cache_size = 32768; \
PRAGMA count_changes = 0; \
PRAGMA synchronous = 0; \
PRAGMA page_size = 4096; \
PRAGMA temp_store = 2;
What else can I do to speed up my inserts?
Thanks,
Brannon
