In step 5 I execute "delete from t1" without any where clause. I haven't monitored the disk space used (does sqlite use temporary files beside the database file?) but the database file itself has been fixed in size at around 12MB (12.461.056 bytes) the whole time.
The load on the disk is minimal (sustained 1MB/s) and the CPU load is about 6%, which is a bit surprising since I thought I would be putting a huge load on the computer running a loop like this. I'm not at all happy to see these low load numbers given how the test is programmed (it should practically own the machine). The database should utilize the computer much better than this. I've been running the test now for about 10 minutes using 3.6.15 and it 'seems' to be behaving as before, slowly increasing in execution time. I want to run this a bit longer to be absolutely sure and will let you know. JP -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov Sent: 16. júní 2009 12:15 To: General Discussion of SQLite Database Subject: Re: [sqlite] Database inserts gradually slowing down How do you do step 5? Like "delete from table" or "delete from table where ..."? Do you see any degrade in disk space used by database along with slowness? Pavel On Tue, Jun 16, 2009 at 8:06 AM, Jens Páll Hafsteinsson<j...@lsretail.com> wrote: > Hi > > I've been running some tests against sqlite and have found that inserts are > gradually slowing down. Since I'm new to sqlite I might be doing something > wrong and thought I'd ask here if anyone has seen this before or know what > might be causing this. > > The test I'm running creates a database containing a single table (int, int, > varchar(100)) along with an index (not unique) on the first field. I then > perform the following operations (all records have unique data in the first > field): > > > 1. start a transaction > > 2. insert 1000 records > > 3. commit > > 4. repeat steps 1-3 100 times > > 5. delete everything from the table > > 6. Start again at step 1 > > The time taken to execute steps 1-4 increases gradually from taking around 16 > seconds the first time to just over 28 seconds after about 2400 iterations. > To me, this is alarming since this time increase seems not to be asymptotic > (not sure though, there is a slight curve on the graph and I haven't done any > curve fitting) and I fear that I will end up with an unusable slow database > after some time of use. Even if I shut down the test application and start > again (on the same database and without re-creating the table), it just > continues running as before it was stopped, that is, taking 28 seconds (and > increasing) if I stop it after 2400 iterations. > > At first I ran this for shorter periods without an index and think I saw the > same behavior, but I have to verify that to be sure. > > I'm using sqlite version 3.6.14 on Windows XP, compiled using Visual Studio > 2008. > > If anyone is interested I can e-mail the collected data and the test program > I'm using. > > Any pointers appreciated. > > Thanks, > JP > > --------------------------------------------------------------------- > "What you take away is more important than what you add." Chuck Moore > > _______________________________________________ > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
