On Tue, Jun 16, 2009 at 10:57 AM, Jens Páll Hafsteinsson<j...@lsretail.com> 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). >
Using SQLite 3.6.13, Perl 5.8.8, on a Mac OS X Leopard Macbook with 4 GB RAM and 320 GB 7200 RPM Seagate drive, I get [11:27 AM] ~/Projects/sqlite_test$perl db_slows.pl 1. the code took: 8 wallclock secs ( 6.83 usr + 0.30 sys = 7.13 CPU) 2. the code took: 9 wallclock secs ( 6.78 usr + 0.34 sys = 7.12 CPU) 3. the code took: 8 wallclock secs ( 6.80 usr + 0.34 sys = 7.14 CPU) 4. the code took: 8 wallclock secs ( 6.78 usr + 0.34 sys = 7.12 CPU) 5. the code took: 8 wallclock secs ( 6.78 usr + 0.33 sys = 7.11 CPU) 6. the code took: 8 wallclock secs ( 6.81 usr + 0.33 sys = 7.14 CPU) 7. the code took: 8 wallclock secs ( 6.80 usr + 0.34 sys = 7.14 CPU) 8. the code took: 9 wallclock secs ( 6.80 usr + 0.35 sys = 7.15 CPU) 9. the code took: 8 wallclock secs ( 6.79 usr + 0.34 sys = 7.13 CPU) 10. the code took: 8 wallclock secs ( 6.90 usr + 0.34 sys = 7.24 CPU) Pretty linear performance. As stated in the OP, I 1. Insert 1000 records (an integer and a 100 random char string; 2. Commit; 3. Repeat 1 one hundred times; 4. DELETE all records; 5. Repeat 1-4 x 10. The table is CREATE TABLE foo (id INTEGER PRIMARY KEY, num INTEGER, desc TEXT); There are no indexes. > 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." > > -- > Matthew L. Creech > _______________________________________________ > 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 > -- Puneet Kishor http://www.punkish.org/ Carbon Model http://carbonmodel.org/ Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/ Science Commons Fellow, Geospatial Data http://sciencecommons.org Nelson Institute, UW-Madison http://www.nelson.wisc.edu/ ----------------------------------------------------------------------- collaborate, communicate, compete ======================================================================= _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
