On Sun, Oct 11, 2009 at 11:49:57AM +0400, Alexey Pechnikov scratched on the wall: > Hello! > > On Sunday 11 October 2009 00:54:04 Simon Slavin wrote: > > > Using transactions speeds up a long series of SELECTs because it > > > eliminates the need to re-acquire a read-only file-lock for each > > > individual SELECT. > > > > > > Since in-memory databases have no file locks, I'm not sure that is > > > relevant to this specific case. > > > > I wasn't sure about that. It could still be slower > > You can check it very easy. > > In transactions: > ve...@veter-laptop:/tmp$ ./test.tcl > 19968119 microseconds per iteration > 25649514 microseconds per iteration > > Without transactions: > ve...@veter-laptop:/tmp$ ./test.tcl > 35586024 microseconds per iteration > 28630785 microseconds per iteration
A bit to my surprise, the difference is even more significant using prepared statements in a C program. For a half-million selects over a similar table in a :memory: database, there is a 20% speed-up by wrapping all the selects in a transaction (vs the 10% you're seeing). It's averaging about 4.3 seconds in auto-commit mode, and 3.4 seconds in an explicit transaction. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
