Best practice for applying set based updates

Question

Post reply

Best practice for applying set based updates

Stuartb113

SSC-Addicted

Points: 440
More actions
November 8, 2007 at 7:12 am

#120951

Hi,
Could someone please give me some pointers in the method of applying set based updates to a destination OLEDB after I have prepared my temporary tables containing the records to be updated.
Many thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Sure... how many rows in the temp table?

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stuartb113 SSC-Addicted Points: 440 More actions · Answer 2

somewhere between 1000 and 2000 records. tests has shown the set based update to complete quicker than the OLE DB Command but is there any known disadvantage of using set based over the OLE DB Command in SSIS? i.e. logging or error exception trapping?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Given that SQL server is essentially a large set processor, in my mind, no - there really aren't a lot of disadvantages. Or rather - since SQL is so optimized towards set-based operations - it leaves you some wiggle room to deal with other things.

A lot of the "disadvantages" some folks have advanced to me can be handled through better code. Mostly - the "fight" seems to be around the amount of control the programmer/designer has over the process. As one of the other posters eloquently described it - in procedural programming, you tell the application WHAT you want, and HOW you intend on getting it. In set-based programming - you describe WHAT you want, but mostly, you leave the HOW up to the set processor (the database engine). So - when you come from a procedural background, that feels unnatural.

Bottom line though - the appropriate set-based update will likely process 2000 rows faster than the SQL server can measure time, or at least sub-second. The typical iterative process will still likely hold up to human standards (say - 10 seconds), but still - light years slower than what you'd get if you "let the machine do its thing".

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Stuartb113 SSC-Addicted Points: 440 More actions · Answer 4

thanks yes I agree...i've already decided in this instance set based update is the appropriate method

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Matt is correct... even if you're using SSIS, 2000 rows should be processed in the proverbial blink of an eye... even faster if you use Bulk Insert, instead 😉 Fastest I've ever seen Bulk Insert run is 5 million 10 column rows in 60 seconds flat.

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Welch SSC Veteran Points: 255 More actions · Answer 6

Using a set based update in SSIS is considered a best practice by most of the experts I know of. The OLE DB Command is painfully slow on large recordsets. Personally, I've been using set based updates since RTM of SSIS and been very happy with it.