Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance difference between using a transaction and not using a transaction Expand / Collapse
Author
Message
Posted Saturday, August 24, 2013 11:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 1:00 PM
Points: 42, Visits: 96
One of our developers was trying to help me troubleshoot a query timeout problem and he ran into a problem like this...

He was testing a small SP that updates a sequence table and returns the next value. The test was done on a lab server that was not running anything else so there was no possibility of interference from other processes at all. He ran the following set of queries, 1000 lines in total.

update SeqTable set NextValue = 748661 where (SeqID = 142) and NextValue = 748660;
update SeqTable set NextValue = 748662 where (SeqID = 142) and NextValue = 748661;
update SeqTable set NextValue = 748663 where (SeqID = 142) and NextValue = 748662;
update SeqTable set NextValue = 748664 where (SeqID = 142) and NextValue = 748663;
update SeqTable set NextValue = 748665 where (SeqID = 142) and NextValue = 748664;
...

The whole bunch of queries lasted 88 seconds, which translates to about 90ms per update.

However, if the whole 1000 lines were enclosed in a 'Begin Transaction' and 'commit', each update averages less than 1ms. The SeqTable itself has three columns, 200+ rows and a primary key on SeqID.

My guess is that the whole table was on a one or two memory pages. When 1000 queries needs to update the single page, each query will have to acquire a lock and it adds to the overhead. But wouldn't the same set of queries in a transaction have to do the same thing?

Thanks.








Post #1488138
Posted Saturday, August 24, 2013 1:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
Someone could explain this better than I but, at a 100,000ft view, when you put all of these queries in a single transaction, the data isn't actually written as you would think until the transaction is committed. When that happens, it's done as a single transaction from the Log file to the table. It's a tried and true method to make WHILE loops run a bit faster although it does extend the time for locks taken. Generally, such single-ton updates should be avoided.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1488147
Posted Saturday, August 24, 2013 2:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 1:00 PM
Points: 42, Visits: 96
Hi Jeff,

We did this test to check the overall response time on high number of consecutively updates, which is similar to the pattern the table is used in production.

We thought that the semicolon at the end of each line will cause an implicit commit. Are we wrong in such an assumption?

What is the difference between running a 1000 update as in the first example and wrapping it in a transaction?

BY the way I checked the Implicit Transaction for both SQL server and my query sessions and it is OFF.

Thanks.
Post #1488150
Posted Saturday, August 24, 2013 4:15 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 756, Visits: 631
Jan Arnoldus (8/24/2013)What is the difference between running a 1000 update as in the first example and wrapping it in a transaction?


In the first case, each statement commits separately, and the log record must be flushed to the transaction log on disk before execution can continue. In the second case, the log records do not have to be flushed until the entire transaction commits.

By the way, the semicolons has nothing to with it. They are just syntax.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1488161
Posted Saturday, August 24, 2013 4:26 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
Jeff Moden (8/24/2013)
the data isn't actually written as you would think until the transaction is committed.


In both cases the data changes are made to pages in memory and those changes will be written to disk later. Later is not at the point the transaction is committed, it's any time before or after that. There's no difference here between individual statements and an explicit transaction.

When that happens, it's done as a single transaction from the Log file to the table.


Nothing moves from the log file to the table when a transaction commits.

What happens when a transaction commits is that the log records describing that change are written to disk. For individual statements, that's one log write after each one. For a single explicit transaction, that's a log write at the commit with maybe a couple more during the operation, depending on the size of the log records and other factors.

The explicit transaction will have much fewer log writes, so much less time in writing to the log file on disk (viewable as writelog waits). That's why it's faster.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1488164
Posted Saturday, August 24, 2013 4:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 1:00 PM
Points: 42, Visits: 96
Hi Gail,

That makes lot of sense now. Thanks.

I'm a big fan of your blogs. I'm up to Chap 7 of SQL Server Transaction Log Management as we speak.

Have a great weekend.
Post #1488168
Posted Saturday, August 24, 2013 8:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
GilaMonster (8/24/2013)
Jeff Moden (8/24/2013)
the data isn't actually written as you would think until the transaction is committed.


In both cases the data changes are made to pages in memory and those changes will be written to disk later. Later is not at the point the transaction is committed, it's any time before or after that. There's no difference here between individual statements and an explicit transaction.

When that happens, it's done as a single transaction from the Log file to the table.


Nothing moves from the log file to the table when a transaction commits.

What happens when a transaction commits is that the log records describing that change are written to disk. For individual statements, that's one log write after each one. For a single explicit transaction, that's a log write at the commit with maybe a couple more during the operation, depending on the size of the log records and other factors.

The explicit transaction will have much fewer log writes, so much less time in writing to the log file on disk (viewable as writelog waits). That's why it's faster.


And that, ladies and gentlemen, is the "someone" I was talking about. Thanks, Gail.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1488184
Posted Sunday, August 25, 2013 11:27 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:44 PM
Points: 8,286, Visits: 8,736
Jan Arnoldus (8/24/2013)
Hi Jeff,

We did this test to check the overall response time on high number of consecutively updates, which is similar to the pattern the table is used in production.

We thought that the semicolon at the end of each line will cause an implicit commit. Are we wrong in such an assumption?

What is the difference between running a 1000 update as in the first example and wrapping it in a transaction?

BY the way I checked the Implicit Transaction for both SQL server and my query sessions and it is OFF.

Thanks.

Implicit Transactions have to committed explicitly, so the first DML (or DDL, with some exceptions) after implicit transactions is switched on or a previous implicit transaction is committed or rolled back starts a new implicit transaction and subsequent statements are part of that same transaction until it is ended either by an explicit commit or rollback or by an error that forces rollback.
When implicit transactions is off and there is no current transaction, a statement that would otherwise have begun an implicit transaction becomes a transaction on its own - it starts the transaction and also either commits it or rolls it back.
Rotten terminology, really - both the single statement transaction when implicit transactions is set off and the multi-statement when it is on are actually begun implicitly; the single statement one is also terminated implicitly, so it actually seems more entitled to the designation "implicit transaction" than what you get when implicit transactions is switched off. It's not at all surprising that people sometimes are confused about what "SET IMPLICIT_TRANSACTIONS" means.


Tom
Post #1488219
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse