Thanks for the info Konrad. This is very similar to what I'm doing so that might be the case. Just for curiosity's sake, I might try to drop the table in-between the runs to see what happens.
JP -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Konrad J Hambrick Sent: 16. júní 2009 17:43 To: General Discussion of SQLite Database Subject: Re: [sqlite] Database inserts gradually slowing down Jens -- I have seen Index and Record Fragmentation cause the kind of slowdowns you are describing. Especially when there are a lot deletes followed by a 'largish' (100,000-record) batch of inserts. I have found on occasion that the speed is more consistent if I can find a way to drop and create a 'fresh' table from time to time. The fresh table eliminates the fragmentation. Sounds like that won't work without a lot of effort since you have records remaining in the table. -- kjh On 06/16/2009 10:57 AM, Jens Páll Hafsteinsson wrote: > The key factor here is not the total time taken to perform these operations > but the fact that the time is *increasing* for each run. I am looking for > consistency in that I need to be able to let the application perform these > steps in constant time over a long period of time (months). > > Dropping the table is not an option since I always will have some data in the > table (even though I delete everything in my test). Maybe the culprit is that > I delete everything from the table? > > JP > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Matthew L. Creech > Sent: 16. júní 2009 15:51 > To: kon...@payplus.com; General Discussion of SQLite Database > Subject: Re: [sqlite] Database inserts gradually slowing down > > On Tue, Jun 16, 2009 at 11:31 AM, Konrad J Hambrick<kon...@payplus.com> wrote: >> Hmmm ... >> >> Is there a way to replace step 5 (delete everything from the table) >> with a System-Level rm / unlink and a sqlite CREATE TABLE ? >> > > This shouldn't be necessary unless the table has triggers. From > http://sqlite.org/lang_delete.html : > > "When the WHERE is omitted from a DELETE statement and the table being > deleted has no triggers, SQLite uses an optimization to erase the > entire table content without having to visit each row of the table > individual. This "truncate" optimization makes the delete run much > faster." > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
