Performance optimization in SSIS

Hi,

our package have design like this,

OLEDBSource à Derived Column à Lookup

|

Matching Records Un Matched Records

| |

OLEDBCommandOLEDBDestination

(Update)(Insert)

and our source & destination table are oracle. when we execute the package the performance is very low and some times its showing like processing ( yellow color) even for 1 hrs .what could be the problem.can any one help us.is there any reason like when we use orcale database this will slow down the performance of package

Jegan

There are plenty of areas which could cause performance problems, just work through them logically. From a pure SSIS perspective, lookups can be slow either building the cache, or when not using caching. The OLEDB Command can also be slow because it is row by row processing. The OLEDB Destination is not great for Oracle, because they don't have fast load support in OLE-DB. There is a third party driver which is I believe faster.

Saying all that, start with the basics. What networks are there between the Source Machine -> SSIS Machine -> Destination Machine, as the data will follow that path.

Isolate the components and test them individually to identify any bottle necks.

e.g.

Source -> Trash Destination - Is the source query, extract or network hop slow

Stage the source data in a raw file in the SSIS machine, then use a Raw File Source -> Lookup -> 2 X Trash Destinations, see if the lookup performance is slow.

etc

Trash Destination is just a freebie transform on http://www.sqlis.com, which we wrote to help build test scenarios faster, and it is then obvious this is not a "real" package, but you can use other transforms just don't add an output. Row Number or Union work quite well as they don't do much on their own.

|||

In addition to what Darren said you should try and apply the OVAL concept to investigatig performance problems. OVAL was introduced by Donald Farmer in a Technet webcast and I talk about it here:

Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)

I highly recommend you watch the webcast.

Also, the Oracle driver that Darren spoke of is provided by Persistent: http://www.persistentsys.com/products/ssisoracleconn/ssisoracleconn_features.htm

Also, Scott Barrett has lots of experience of using SSIS with oracle and irs worth checking out his blog: http://microsoftdw.blogspot.com/

-Jamie

|||

Jegant wrote:

Hi,

our package have design like this,

OLEDBSource à Derived Column à Lookup

|

Matching Records Un Matched Records

| |

OLEDBCommand OLEDBDestination

(Update) (Insert)

and our source & destination table are oracle. when we execute the package the performance is very low and some times its showing like processing ( yellow color) even for 1 hrs .what could be the problem.can any one help us.is there any reason like when we use orcale database this will slow down the performance of package

Jegan

Looking only at the SSIS side, here is my bet: the OLE DB Command. How many rows are you processing? and how many of those are going to the update pipeline?

As a general, and very personal, practice I always replace the OLEDB Command by an OLE DB destination pointing to a temporary table that is empty at the beginning of every execution. Then back in the control flow I use an 'Execute SQL Task' to perform a one time update using the content of the temp table. I had similar performance issues using the approach you are describing in your post and this simple change made a huge difference. Other suggestion is to try to isolate the issue; for example replace the OLE DB command by a RowCount transformation and measure your execution time; then try the same with the OLE DB Destination. This paper has very good tips on Performance Tuning Techniques:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

Rafael Salas