24

I read that wrapping a lot of SELECT into BEGIN TRANSACTION/COMMIT was an interesting optimization.

But are these commands really necessary if I use "PRAGMA journal_mode = OFF" before? (Which, if I remember, disables the log and obviously the transaction system too.)

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Stef
  • 3,691
  • 6
  • 43
  • 58
  • Wrapping SELECT commands in BEGIN TRANSACTION/COMMIT does not have any effect on performance. Perhaps you meant INSERT? In any case, why don't you try it and see? – ravenspoint Sep 09 '11 at 12:52
  • Ok, before setting -1 read this:please...http://katastrophos.net/andre/blog/2007/01/04/sqlite-performance-tuning-and-optimization-on-embedded-systems/. (And no, I'm not talking about INSERT). I tried, and results are not so easy to interpret. Before reading the sqlite code, I was asking if someone already did it. Thanks – Stef Sep 09 '11 at 15:00

2 Answers2

25

Note that I don't agree with BigMacAttack.

For SQLITE, wrapping SELECTs in a Transaction does do something: It reduces the number of SHARED locks that are obtained and then dropped.

Reference: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg79839.html

So I think the transaction would also be beneficial even if you had journal_mode turned off, because there is still the locking overhead to consider.

Maybe read_uncommitted would be something you could consider - I would guess that it would disable the SHARED locking.

elegant dice
  • 1,307
  • 12
  • 19
19

"Use transactions – even if you’re just reading the data. This may yield a few milliseconds."

I'm not sure where the Katashrophos.net blog is getting this information, but wrapping SELECT statements in transactions does nothing. Transactions are always and only used when making changes to the database, and transactions cannot be disabled. They are a requirement. What many don't understand is that unless you manually BEGIN and COMMIT a transaction, each statement will be automatically put in their own unique transaction. Be sure to read the de facto SO question on improving sqlite performance. What the author of the blog might have been trying to say, is that if you plan to do an INSERT, then a SELECT, then another INSERT, then it would increase performance to manually wrap these statements in a single transaction. Otherwise sqlite will automatically put the two insert statements in separate unique transactions.

According to the "SQL as Understood by SQLite" documentation concerning transactions:
"No changes can be made to the database except within a transaction. Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect."

Lastly, disabling journaling via PRAGMA journal_mode = OFF does not disable transactions, only logging. But disabling the log is a good way to increase performance as well. Normally after each transaction, sqlite will document the transaction in the journal. When it doesn't have to do this, you get a performance boost.

UPDATE:
So it has been brought to my attention by "elegant dice" that the SQLite documentation statement I quote above is misleading. SELECT statements do in fact use the transaction system. This is used to acquire and release a SHARED lock on the database. As a result, it is indeed more efficient to wrap multiple SELECT statements in a single transaction. By doing so, the lock is only acquired and released once, rather than for each individual SELECT statement. This ends up being slightly more efficient while also assuring that all SELECT statements will access the same version of the database in case something has been added/deleted by some other program.

Community
  • 1
  • 1
BigMacAttack
  • 4,479
  • 3
  • 30
  • 39
  • 9
    I don't agree with this answer entirely. Each SELECT will be inside a transaction, whether you BEGIN/END manually or not. So if you are going to execute more than one SELECT statement, then you should wrap them both in a BEGIN/END transaction. If you do wrap, then there is 1 transaction, 2 selects. If you don't wrap, then there is 2 transactions, 2 selects. – elegant dice Oct 08 '13 at 05:51
  • 1
    @elegantdice It is my interpretation of the "SQL as Understood by SQLite" documentation, that since SELECT statements don't "change" the database, they do not use transactions. Are you reading somewhere else that SELECT statements do indeed require transactions? If so, please cite your source. Preferably a source from the sqlite.org website or mailing list. – BigMacAttack Oct 08 '13 at 16:25
  • 3
    hmm you are right, my bad, I misread this: https://sqlite.org/lang_transaction.html ... however if you want "consistent reads" (ie multiple SELECTS all giving the same results), then you'll need to wrap in a BEGIN/END. But if you are doing one-off SELECTs, or don't care about consistency, then true you don't need a transaction at all. I wonder how you can create a SHARE-ONLY lock in sqlite, without the overhead of transactions? – elegant dice Oct 11 '13 at 03:43
  • 5
    I followed up, and can confirm that you SHOULD wrap multiple selects in a transaction, for performance, because each SELECT obtains and then drops a SHARED lock. Wrapping everything in a transaction reduces the locking overhead. – elegant dice Oct 14 '13 at 04:35
  • The whole reason for transaction on read operations to get consistency across multiple independent select calls. This can only be done with using shared locks, so i have no idea why BigMacAttack's answer was selected. – Lothar Apr 15 '15 at 01:38
  • What if the shared cache is off, is a shared lock still acquired? – user1095108 Feb 19 '19 at 21:06
  • Compare with the quote from the same SQLite page, as of 2022: "*Any command that **accesses** the database (basically, any SQL command, **except a few PRAGMA** statements) will automatically start a transaction if one is not already in effect.*" – Maximko Apr 24 '22 at 11:23