Performance tips on SSIS2008 (Parallelism and ADO.Net vs OLEDB connections)

I was preparing a benchmark about “ColumnStore-Index VS VertiPaq” but first I would like to “speed-up” little bit more SSIS2008 with some little tips and trying to be a “mythbuster” with the difference between using a ADO.Net connection managers against OLEDB ones.

 

PARALLELISM on SSIS2008

That could be some of the best performance tips in the new SSIS2008 that I could mention. It is very simple, if you have a bunch of processes to run in your Control Flow and there is no difference between launch them in order, then you can use the “normal way” in a sequential way or try to use the parallelism.

 

Not Using Parallelism In SSIS2008

Not Using Parallelism In SSIS2008

or

 

Using Parallelism In SSIS2008
Using Parallelism In SSIS2008

As you can see it will process many of them at the same time (depending on your machine capabilities) or just one by one. The result is obvious, with Parallelism you will get it done in 2x/3x faster. Then in order to check how faster can be, I prepared 4 scenarios:

 

PARALLELISM with OLEDB and ADO.Net connection Managers

 

Without PARALLELISM with OLEDB and ADO.Net connection Managers

With these 4 scenarios I was calling in each SQL Task a Stored Procedure that has to write on a table 10.000 times some sentence then I tried to measure the difference not only using or not the Parallelism, but using the ADO.Net and OLEDB connections for the same things, in order to know the gap between them.

First of all I should say that the theory is clear in this matter, OLEDB is more native connection and ADO.Net is using an additional layer (code) behind, then ADO.Net should be slower than OLEDB, but… how much?

 

Mythbuster

The results was a surprise, cause I know that OLEDB should be faster, but I was trying to know the gap between them. And then I got this results:

With Parallelism ADO.Net was 1.2s faster than OLEDB
Without Parallelism ADO.Net was 0.97s faster than OLEDB

I took care about buffers, my CPU, services and many other chaotic possibilities. My conclusion is, if there is a speed difference between them is just as small as you cannot get any real difference, and from my point of view working with Stored Procedures with ADO.Net is a pleasure (and with OLEDB is the opposite).

 

UNKNOWN PERFORMANCE TIP

As I should say “with SSIS, is all about the buffers”. Then I read a article from Mr Rob Farley, where you can see how true is that sentence. He found out that using the OPTION (FAST 10000) is not really good when you check your Query’s execution plan, actually is a disandvantge. But then as he explains in his blog, it is not about how fast can be your query (optimizing and optimizing as most of us where are always doing) is about the buffers in SSIS and how this “OPTION” can help us in the performance.

I wanted to explain with my own example, but he did a good work on his blog and I encourage you to visit and read:

http://sqlblog.com/blogs/rob_farley/archive/2011/02/17/the-ssis-tuning-tip-that-everyone-misses.aspx


Did you like this? Share it:

Leave a Reply

*