Firebird 2.0 transaction SELECT performance

Question

In Firebird 2.0, is using an explicit transaction faster on a SELECT command than executing the command with an implicit one?

Answer 1

Usually transaction adds some overhead. However, you should be careful if you do not have some default transaction started when you connect to Firebird.

Answer 2

All SQL commands (SELECT, INSERT, UPDATE etc.) can be executed ONLY within some transaction. You cannot run a command with out transaction being started prior to it.

Answer 3

Firebird cannot execute SQL commands without a transaction.

PS: You get the best performance results if you commit transactions, rather than rolling them back. Even if you only called SELECT and changed nothing.

Answer 4

Besides what was already said, take into account that the transaction can be:

• Read-Write
• Read-Only

For a SELECT it would be best to use a Read-Only transaction

PS: There are other types of transactions but this two are the important ones for this topic.

Answer 5

Explicit and Implicit transaction are a feature of the component set you're using to access the database, not a feature of Firebird itself. As mentioned before, Firebird always does everything within a transaction. This has a couple of implications for you:

• Using a "Implicit" transaction can't be faster then using a "Explicit" transaction because from Firebird's point of view, a transaction is a transaction, doesn't matter who started it.
• Getting the best performance sometimes requires fine control over "Commits". While the "Implicit" transaction can't be faster then the "Explicit" transaction, the Explicit might be faster because you can control your StartTransactions and Commits. While you usually want to do all updates to a database within one transaction (so they all succeed or fail as a set) you sometimes want to split operations into multiple groups: If you need to bulk-insert many-many records, you probably want to Commit one every 1000 records or so.