Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

SSIS – Avoid OLE DB Command

I decided to do a little performance testing on some common SSIS “Tricks” that increase speed and effectiveness of SSIS packages.  I wanted to define to what extent just one simple change could make to a package. 

So in this test I decided to show the difference in using a staging table vs. using the OLE DB Command in a SSIS package that uses a change data capture technique.  Many people have the impression that landing data in a staging table is bad for some reason, but in almost all cases I have found that it’s worth using a staging table for your changed data vs. using the OLE DB Command to update your data.  Once you’ve landed your data in a staging table simply us a Execute SQL Task to update the destination table and truncate the staging table after the update completes.

Sounds like your doing essentially the same thing doesn’t it?  Well, yes and no.  The end result is the same but the speed at which it gets there drastically different.  I updated 74,575 rows and here is the performance difference.

00:00:11.887 with staging table
02:02:30.393 with OLE DB Command

So, 12 seconds using a staging table and a little more than 2 hours with the OLE DB Command!  I couldn’t believe that simple change made that much of a difference.  So I ran them both again and got essentially the same result.

Here’s the reason why.  The OLE DB Command runs insert, update or delete statements for each row, while the Execute SQL Task does a Bulk Insert in this instance.  That means every single row that goes through your package would have an insert statement run when it gets to an OLE DB Command.

So if you know you are dealing with more than just a couple hundred rows per run then I would highly suggest using a staging table vs. the OLE DB Command.

Comments

Posted by Me on 5 May 2009

sample code?

Posted by Anonymous on 13 July 2009

This is part 3 of my 29 part series called Better Know A SSIS Transform. Hopefully you will find the

Posted by marees.inspire on 26 July 2010

Hi Devin,

Can you please provide the link for the below:

"This is part 3 of my 29 part series called Better Know A SSIS Transform"

Posted by knight_devin@hotmail.com on 26 July 2010

Strange i'm not sure what that comment was referring to but it sounds like this link www.sqlservercentral.com/.../better-know-a-ssis-transform-conditional-split.aspx

Posted by shannonholck on 6 August 2012

Devin, did the time you posted for "with staging table" take into account the time to insert the rows into the staging table? (if you can remember 4 years back)

Posted by Devin Knight on 7 August 2012

That's right it actually runs faster to insert all those rows and then do a bulk update instead of using the OLE DB Command to handle all updates.  

Posted by nirajk-1156894 on 4 June 2013

Hi Devin,

I realy agree with your comment i tried with first option in my test level only for 1235 records and for updating it OLEDB command took 00:01:28:825 i.e close to 1 and half min , after chnging the approach and  using a staging table for my changed data it took only 00:00:01:235 i.e 1 sec.

and good thing is in PRD also it works fine.

Thanks,

niraj

Leave a Comment

Please register or log in to leave a comment.