Thanks for the answer Pavel, I'm using synchronous = FULL as the data in my application is the most important part.
Changed the journal_mode to PERSIST same result the journal file is 3 times bigger than what it should be and the results are bad. Thanks, Ofir. -------Original Message------- From: Pavel Ivanov Date: 05/13/09 04:28:06 To: General Discussion of SQLite Database Subject: Re: [sqlite] Bad UPDATE performance Just a couple of questions that can clarify situation: Do you work with pragma synchronous = FULL? Does the behavior of the journal you have described remains if you change pragma journal_mode = PERSIST? Pavel On Tue, May 12, 2009 at 4:15 PM, Ofir Neuman <ofir.neu...@gmail.com> wrote: > Hi, > > Using version 3.6.10.0. > > I have a problem that my application becomes slow after awhile with no > specific reasonI got a chance to reproduce it on development environment and > have noticed that UPDATE of 200 records within transaction takes more than > 20 sec. > > When the application first run the same update for the same records takes > something like 1-2 sec. > > Since I had the problem while debugging I try to figure out the problem and > have noticed the following things: > > 1. When I suffer bad UPDATE performance the journal file is 3 times bigger > than on regular run (when UPDATE command takes 1-2 sec) > 2. Most of the time spent on the UPDATE itself and not on the COMMIT > operation. > 3. I tried to open new db connection while the application still running and > do the same operation and guess what... Everything works fine the > application is responsive and the same update took something like a second > > I didn't find a specific scenario but I can reproduce it if I do the same > operation on the DB multiple timesafter a while everything will become slow. > > It looks like the UPDATE operation doesn't run within a transaction even > though that I got a successful result after calling to BEGIN IMMEDIATE > TRANSACTIONand I can see that during the UPDATE operation the journal file > become bigger and bigger and removed after calling to COMMIT TRANSACTION. > > Is this a known bug? > > Should I close the DB connection after a while? > > I have not idea how to solve this issue without closing the DB connection > and open a new one. > > Thanks. > _______________________________________________ > 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
