DML in transactions

Question

do transactions speed up DML queries

for instance

we have databases in full recovery model
if the update is included in transactions, would the logging be reduced and only committed for the entire transaction.

Answer 1

if there is a simple sql command

update foo
set col A = null where col A = Test

begin tran

update foo
set col A = null where col a = test

commit

the second sql should take much less time because the commit is performed after all the rows are updated not every row is committed. the first sql query commits very row and is slower, is my understanding correct

Answer 2

Transactions are always used in SQL Server. You cannot stop them from being used. This is inherent in how SQL Server and most RDBMS work. Everything you do is written to the log file (in some fashion) and then committed to the data file. The recovery model does not affect (except the bulk logged for specific functionality) the log file usage. It affects when the DATA in the log file can be overwritten by new transaction data.

Some commands generate "implicit" transactions, and auto-commit when the statement ends.

The main reason to use an "explicate" transaction, using BEGIN TRAN/COMMIT, is when you must have multiple commands in sync.

In the example you gave above, they are exactly the same.

Answer 3

f there is a simple sql command

>

update foo
set col A = null where col A = Test

>

begin tran

>

update foo
set col A = null where col a = test

>

commit

The explicit transaction in the second case does not make the UPDATE faster. As others has pointed out, the UPDATE statement is a transaction of its own.

However, here is a difference:

UPDATE ...
UPDATE ...
...
UPDATE ...

It is a lot faster if you do:

BEGIN TRANSACTION
UPDATE ...
UPDATE ...
...
UPDATE ...
COMMIT

Because when each statement is its own transaction, SQL Server must wait for the transaction log to be hardened for each statement, but with an explicit transaction enclosing the statements it can jog along, and only has to wait at the COMMIT.

Answer 4

Hi @NeophyteSQL ,

>the second sql should take much less time because the commit is performed after all the rows are updated not every row is committed. the first sql query commits very row and is slower.

You can using SET STATISTICS TIME (Transact-SQL) to display the query execution time.

SET STATISTICS TIME ON;  

SET STATISTICS TIME OFF;  

I test this in my environment.

For the difference between implicit and explicit transaction, suggest you read the blog SQL Server - Implicit vs Explicit Transaction.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.