SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance difference between using a transaction and not using a transaction


Performance difference between using a transaction and not using a transaction

Author
Message
RandomStream
RandomStream
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 130
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204474 Visits: 41951
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RandomStream
RandomStream
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 130
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.
Erland Sommarskog
Erland Sommarskog
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4942 Visits: 875
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212489 Visits: 46259
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, MVP, M.Sc (Comp Sci)
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


RandomStream
RandomStream
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 130
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204474 Visits: 41951
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25164 Visits: 12488
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search