Jeffrey --
Maybe so, but I should have looked further in Jen's reply at the built-in optimizations for DELETE on a Table without any filters and not having TRIGGERS ... sorry :( -- kjh On 06/16/2009 12:44 PM, Hoover, Jeffrey wrote: > Wouldn't a period VACUUMing of the database alleviate fragmentation? > > - Jeff > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Konrad J Hambrick > Sent: Tuesday, June 16, 2009 1:43 PM > 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
