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

The power of batching Transactions Expand / Collapse
Author
Message
Posted Wednesday, January 20, 2010 10:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 19, 2011 11:39 AM
Points: 0, Visits: 39
Comments posted to this topic are about the item The power of batching Transactions
Post #851002
Posted Wednesday, January 20, 2010 10:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:24 AM
Points: 11, Visits: 283
I think the reason is that the batched transactions reduce the cost of getting and release locks compare to one transaction per statement...
Post #851006
Posted Thursday, January 21, 2010 7:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:40 AM
Points: 1,297, Visits: 1,660
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?
Post #851244
Posted Thursday, January 21, 2010 10:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 15, 2011 2:07 PM
Points: 13, 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?)
Post #851401
Posted Thursday, January 21, 2010 6:35 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:19 PM
Points: 36,793, Visits: 31,251
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."

(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 #851681
Posted Thursday, January 21, 2010 6:53 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:19 PM
Points: 36,793, Visits: 31,251
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."

(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 #851683
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse