On 10/2/09, Pavel Ivanov <paiva...@gmail.com> wrote: > > Does anybody know why just adding the begin/commit here improves > > performance? If I have to do a large number of selects like this in my > > application, should I always wrap it in a transaction? > > > This looks like some overhead of your file system. When you don't put > begin/commit around selects then every select is a different read-only > transaction. So before each select SQLite takes read lock on database > and then after executing select SQLite releases the lock. If you put > begin/commit around all selects then SQLite will take read lock only > once at the beginning and release lock once at the end. So you have > just found how long will it take to acquire/release read lock on > database 50000 times. So if your application indeed needs to do so > many selects and it needs to do it in minimum amount of time then > beginning transaction could be indeed a good solution. But be aware > that by doing this you're blocking any other instance of your > application which wants to write to database. It will not be able to > do it until you make commit in your read-only transaction. If it's > okay for you then why not.
Ahh, ok - that makes sense. Does this locking overhead not occur on other platforms (I've only tried linux -- gentoo and ubuntu), or if sqlite is built differently? It seems a bit disingenuous to claim there will be no performance gain by putting selects in a transaction, when sqlite clearly does less work with the transaction (in the form of not getting the read lock multiple times). Thanks, -Mike _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
