DML in transactions

NeophyteSQL 241 Reputation points
2021-03-03T19:21:11.95+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,268 questions
{count} votes

4 answers

Sort by: Most helpful
  1. NeophyteSQL 241 Reputation points
    2021-03-03T20:51:34.717+00:00

    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


  2. Tom Phillips 17,721 Reputation points
    2021-03-03T22:18:07.84+00:00

    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.

    0 comments No comments

  3. Erland Sommarskog 106K Reputation points MVP
    2021-03-03T22:46:17.903+00:00

    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.

    0 comments No comments

  4. CathyJi-MSFT 21,121 Reputation points Microsoft Vendor
    2021-03-04T06:32:04.07+00:00

    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.

    0 comments No comments