> 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).

It's pretty ingenuous in fact to silently assume that nobody wants to
do 50000 selects at once. Lower that to 5 (already a huge amount of
work for "one request to database" that blocks out any writers) or to
1 (the real number when somebody speaks about "difference of putting
select inside transaction") and you'll get a difference of 0.08 ms or
0.016 ms which is pretty small number to ignore when it's compared to
the time of actual reading from disk for the actual select.

Pavel

On Fri, Oct 2, 2009 at 3:40 PM, Mike Shal <mar...@gmail.com> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to