SSIS – Avoid OLE DB Command

, 2009-11-07

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads