Thanks Dan this was very helpful it seems that I have a similar problem. Didn't try the patch yet but I find out a scenario to reproduce the problem.
1. Process A perform UPDATE on bunch of records (within transaction) 2. Afterward, process B perform the same UPDATE operation but only on a single record 3. The next time process A will try to perform the same UPDATE again (bunch of records) the results will be bad. Looking at the task manager I can see that process A doesn't consume any CPU usage just I/O. (the CPU should increase like in step 1) One question regarding the patch, should I add only the following line as suggested? Pcache1RemoveFromHash(pPage); Thanks, Ofir. -------Original Message------- From: Dan Date: 05/13/09 07:23:16 To: General Discussion of SQLite Database Subject: Re: [sqlite] Bad UPDATE performance On May 13, 2009, at 3:15 AM, Ofir Neuman 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 times, after a while everything will > become > slow. Maybe try this patch: http://www.sqlite.org/cvstrac/chngview?cn=6619 Dan. _______________________________________________ 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
