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


The power of batching Transactions


The power of batching Transactions

Author
Message
mlevtov
mlevtov
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 39
Comments posted to this topic are about the item The power of batching Transactions
kurt.yang
kurt.yang
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 302
I think the reason is that the batched transactions reduce the cost of getting and release locks compare to one transaction per statement...
sknox
sknox
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2659 Visits: 2833
This article leaves me with more questions than answers.

First off, there are no indexes on the table in the example. How would adding indexes and inserting non-sequential data affect the batch performance?

Second, where does this performance gain come from? Is it just from the reduced locking? Could it also be related to row size vs page size?

Is there a way, short of trial and error, to come up with at least a good starting point for an efficient batch size given a particular table schema?
tobyteel
tobyteel
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 51
Also, this only deals with 1 table. What about if you are inserting 1 record each into 10 tables (instead of 10 records into 1 table)?

I also guess that the 80 records performance boost has to do with the number of records in a page (although at 8k per page, that would mean a single record with an int column would be ~100 bytes, right?)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89435 Visits: 41144
Nicely written article by the author... can't take anything away from Manor for that.

I thoroughly understand the intent of the article... it's a method for making necessary RBAR faster. The problem is, it's still RBAR and there are a whole lot of folks that don't understand when RBAR is actually necessary. For example, it is patently not necessary to use any form of RBAR to generate random numbers... it can easily be done in a much higher performance set based fashion. All you have to do is spend about the same amount of time on one of the web search engines as what it takes to read this article and you'll find many ways to accomplish the task of generating random numbers in a set based fashion.

In situations where RBAR actually is necessary, you'll find that the necessary WHILE loop is NOT a performance problem because it's simply a vehicle for controlling multiple set based operations.

To wit, instead of folks spending time writing articles on how to make RBAR faster, I'd like to see them write articles on how to avoid RBAR in the first place. ;-)

--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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89435 Visits: 41144
Ah.... forgot to mention that if you absolutely must write RBAR (not likely if you learn to write nice simple set based code), you should also use SET NOCOUNT ON to improve the performance by not having to generate (250,000 in this case) "(1 row(s) affected)" messages. In the 2nd example given, SET NOCOUNT ON improved the performance from almost 18 seconds to just over 13 seconds on my box.

Of course, that's still a RBAR solution. Generating the same random numbers using one of the multiple methods to gen set based random numbers dropped the duration to less than 1.2 seconds to do the same thing. It's also easier on the I/O system...

The RBAR method generated more than 254,000 reads and an internal rowcount of 500,001 rows where the set based method generated less than 1000 reads and an internal rowcount of only 250,000 which is just what the good doctor ordered. (There is a slightly more complex method that will generate, get this, almost 0 reads... you've just got to look for it).

Heh... why don't I post that solution? I don't want to deprive you of having the fun of finding it and testing it on your own. ;-)

--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
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