> ----- Original Message ---- > From: Joe Wilson <[EMAIL PROTECTED]> > To: sqlite-users@sqlite.org > Sent: Monday, June 11, 2007 8:36:32 PM > Subject: Re: [sqlite] Database replication question > > > Large bulk inserts with more than one index (implicit or explicit) > is not SQLite's strong suit. > > If you search the mailing list archives you'll find a few suggestions: > > - "BEGIN EXCLUSIVE" (or is it "BEGIN IMMEDIATE"?) on the > database file and then copy the file over - fastest way
What do you mean by "copy the file over"? A straight copy of the binary content of the file? If so, I can't really do that because the version of sqlite are potentially different on the two machines. > > or > > - increasing cache sizes > - pre-sorting the data in index order prior to bulk insert > - creating the other indexes after all the data is inserted > > If you do not require a live backup you could use the copy trick > and augment that with a daily archive via > > sqlite3 file.db .dump | gzip etc... > > in case the database file becomes corrupted. If the performance problem is with the seconday index, is there a way to "pause" indexing before a large bulk insert and then "resume" it later without rebuilding the entire index (to avoid doing: drop index + inserts + create index)? Maybe it's a stupid question, but I am guessing that there is some sort of version number for the rows in the db, so playing "catchup" on an index could work? Nicolas
