On 5/18/06, Brannon King <[EMAIL PROTECTED]> wrote:
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?
see create index: http://sqlite.org/lang_createindex.html
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.
Do you have any other indices on the table you're inserting into? It's faster to do all the inserts and build the index later. Also, do a large number of inserts within a transaction. (1000-100,000 or so, not a billion ;) )
