Eric Smith <eas....@...> writes: > > Jay A. Kreibich wrote: > > > Try getting rid of the PK definition and see how much that buys you. > > It might be worth it, or it might not. > > and Simon Slavin wrote: > > > We know you are doing a huge amount of writing to this database. Are > > you also reading it frequently ? If not, then it might be worth making an > > index on that primary key only when you're about to need it. ... > I tried removing the PK definition as you both suggested, and the > journal stays fixed at less than 20Kb, even against a db size of (at > the moment) 37Gb. My insert batch run times are improved by a factor of > ~2.5 and seem to be O(1). > > So, bingo. :)
Here's a trick that might help you: Since your primary key is two integers, you can combine them into one integer and use them as the primary key, without requiring a separate index: create table x ( pkey integer primary key, val text); a = (value 0-2M) b = (32-bit integer) pkey = a<<32 | b insert into x values (pkey, data) This shift assumes that b is a 32-bit integer. Since a is limited to 2M, which requires only 21 bits, b can be up to 43 bits, or 8796093022207. Also, I think you mentioned that you are inserting records in this order: a=0 b=0 a=1 b=0 ... a=2M b=0 then a=0 b=1 a=1 b=1 ... a=2M b=1 To insert records in order, you should insert them as: a=0 b=0 a=0 b=1 a=1 b=0 a=1 b=1 Jim --- HashBackup: easy onsite and offsite Unix backup http://sites.google.com/site/hashbackup _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
