question

NeophyteSQL avatar image
0 Votes"
NeophyteSQL asked ·

DML in transactions

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.

sql-server-general
· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm not sure I exactly understand the question. Transactions are used, regardless of type of query, they neither speed them up nor slow them down... but if you have an example that you can share that'd be great :)

0 Votes 0 ·

Any update for this thread?

0 Votes 0 ·
NeophyteSQL avatar image
0 Votes"
NeophyteSQL answered ·

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

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

No - the first one uses an implicit transaction, the second one is an explicit transaction. The first one does not commit row-by-row, it either commits everything or rolls back everything - the same as the second one with the explicit transaction. Both will perform the same.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered ·

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.

74119-screenshot-2021-03-04-140258.jpg

74174-screenshot-2021-03-04-140329.jpg

74135-screenshot-2021-03-04-141443.jpg

74136-screenshot-2021-03-04-141530.jpg

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.



·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.