My database connection option:
PRAGMA synchronous = NORMAL; PRAGMA journal_mode = OFF; PRAGMA auto_vacuum = 0; In windows process monitor ,it show 2.5M writing ,but the database file increase only 10k or so. So those IO writing is very abnormal IMO. At 2013-04-22 03:24:43,"Stephen Chrzanowski" <pontia...@gmail.com> wrote: >You know, this kind of "smells" like a full sync, non transactioned, single >inserts problem. Basically, you open the connection to the database, start >throwing inserts at it without a transaction around each insert. To make >matters worse (For time anyways) you've probably got full sync on, and >you're not using prepared statements. I think this because of the volume >of data you're writing versus the time it takes and the final size of the >file. 2 meg isn't even a snack by todays standards. Depending on the >wrapper you're using, synchronous can be turned on to FULL, or it could be >set to normal. The wrapper of my wrapper always turns Synchronous off. > >http://www.sqlite.org/pragma.html#pragma_fullfsync >http://www.sqlite.org/pragma.html#pragma_synchronous > >If this is the production method, since you're rebuilding the data every >time, I think you could pull all the safeties off in this case. Right >after you open the connection, execute these two PRAGMAs: >** >PRAGMA synchronous = 0 >PRAGMA fullfsync = 0 (Only if being used on a Mac) > >When you start inserting data, put them within a transaction. Personally, >I haven't figured out how to do prepared statements with the wrapper I >have, so I basically re-do my query every time. It IS a performance hit. >I get the concept, but the methodology seems to be odd. *shrugs*. >Basically, from what I've gathered, you write your SQL statement like: > >insert into TempTable (Field1, Field2) values (:x,:y) > >Then you tell SQLite to substitute the :x for one value, and ;y for >another. Then you loop through the bulk inserts updating only the fields >you substituted, not the entire query. > >The actual METHOD to do this depends on the wrapper and language you're >using, so a bit of homework is going to be needed. > >The other thing you could do is create a new database in memory. What you >do is create a second database connection but instead of opening a file, >open it to memory via *OPEN(':MEMORY:)* with the colons, and use the backup >API SQLite provides, work off the data in memory, then reverse the backup >and put it back to the disk. This, of course, is going to depend entirely >on how much memory you have to play with. The backup API copies the ENTIRE >database to the destination specified. So if you have a 12gig database in >a 4gig machine... .. well.... > > >** > > >On Sun, Apr 21, 2013 at 2:31 PM, Paolo Bolzoni < >paolo.bolzoni.br...@gmail.com> wrote: > >> FAQ 19 applies also to SAVEPOINT/RELEASE, not only to BEGIN/COMMIT, right? >> >> On Sun, Apr 21, 2013 at 9:35 AM, Kees Nuyt <k.n...@zonnet.nl> wrote: >> > On Sun, 21 Apr 2013 11:15:23 +0800 (CST), ?????? <yunjie....@163.com> >> > wrote: >> >> >> >> Ok,I do not make my means clearly. I mean 60 seconds after my >> >> program started,not token 60 seconds to load database file. >> >> >> >> Now, I got the reason of sqlite abnormal IO writing,it about >> >> batch insert. Here is my usecase: One table about 4 column and >> >> 500 row,the content of every row no exceed 100 byte, every time >> >> I update the whole table using batch query. >> >> >> >> It should take about one second and 100k IO writing on >> >> estimate,BUT it sustained about 20 second and wrote about 2.5M >> >> actually. >> >> >> >> Now,I modify the implement of batch query, it take about one >> >> second and 70k IO writing.So there are abnormal something in >> >> batch query indeed,Sqlite or Qt SQL module. >> > >> > Is this still about bulk INSERT or about a SEELCT query? >> > In which way did you modify it? >> > Perhaps http://sqlite.org/faq.html#q19 helps? >> > >> > -- >> > Groet, Cordialement, Pozdrawiam, Regards, >> > >> > Kees Nuyt >> > >> > _______________________________________________ >> > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
