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

Performance of SSIS Vs Other ETL Tools

Hi All,

I'm working on a conversion project and I'm trying to compare performance of SSIS with Other ETL Tools, especially Informatica PowerCenter. Which one do you think is better ETL performer, when source and destination being SQL Server databases. Is there any benchmark available?

Thanks.

I'm sure that you have seen this, but for future reference, here is a link to jamie's article regarding this question....

http://blogs.conchango.com/jamiethomson/archive/2007/08/21/Informatica-_2D00_vs_2D00_-SSIS-pipeline-throughput.aspx

|||I have used Informatica, SSIS, ETI, and CloverETL.

All the above use Microsoft technologies to connect to SQLServer. Some use ODBC, others use ADO.NET and OLEDB. There are ways with a few of the technologies to configure a 'bulk load' process as an ETL target.

Since the ETL tools all use common Windows technologies for connections, there is very little difference in performance for simple data movement.

Data movement with transformations, sorting, aggregation, filtering, insert/update logic, and other 'intermediate actions' is where you see large differences. There is no overall winner on performance for these types of ETL actions in my opinion. The technology used to for each action dictates how fast things go. Also, if the ETL process uses a bulk read or load process, this can assist with performance increases, though it is only for the initial read and/or final load and not the intermediate actions.

Some examples:
ETI outputs source code you compile into a conversion. So, if you have a complex conversion with SQLServer you are going to compile and deploy a C/C++ program which will usually be much faster than any GUI/Engine-based ETL process. Its not really 'faster' than other tools, its that the approach of using a small C program to move the data is just inheriently faster on average.
CloverETL has a few screaming-fast transformation features, and they are probably fast due to the use of a RegEx engine. Having used JDBC though, I would estimate that DB reads and Writes are faster with ADO/OLEDB. This is not a knock, but a comparison of the technologies used.

It should be noted that I have used Informatica and ETI to create conversions so complex that they should not even be attempted in SSIS. This is where I believe that other tools can outshine SSIS - handling complexity with multiple databases for source and destination, lookups, and hundreds of intermediate actions in-between.

The term 'performance' also needs to be defined a bit here.
We test performance of SSIS based on the time for a Package to run.
If you run a package in the BIDS it usually will run a little slower than if you configure a job or execute DTEXEC.
If you run on 64-bit, it will tend to run a little faster than 32-bit.
Since there are a number of ways to execute a SSIS package, there are a number of ways one can perceive the performance.

Performance of SSIS Vs Other ETL Tools

Hi All,

I'm working on a conversion project and I'm trying to compare performance of SSIS with Other ETL Tools, especially Informatica PowerCenter. Which one do you think is better ETL performer, when source and destination being SQL Server databases. Is there any benchmark available?

Thanks.

I'm sure that you have seen this, but for future reference, here is a link to jamie's article regarding this question....

http://blogs.conchango.com/jamiethomson/archive/2007/08/21/Informatica-_2D00_vs_2D00_-SSIS-pipeline-throughput.aspx

|||I have used Informatica, SSIS, ETI, and CloverETL.

All the above use Microsoft technologies to connect to SQLServer. Some use ODBC, others use ADO.NET and OLEDB. There are ways with a few of the technologies to configure a 'bulk load' process as an ETL target.

Since the ETL tools all use common Windows technologies for connections, there is very little difference in performance for simple data movement.

Data movement with transformations, sorting, aggregation, filtering, insert/update logic, and other 'intermediate actions' is where you see large differences. There is no overall winner on performance for these types of ETL actions in my opinion. The technology used to for each action dictates how fast things go. Also, if the ETL process uses a bulk read or load process, this can assist with performance increases, though it is only for the initial read and/or final load and not the intermediate actions.

Some examples:
ETI outputs source code you compile into a conversion. So, if you have a complex conversion with SQLServer you are going to compile and deploy a C/C++ program which will usually be much faster than any GUI/Engine-based ETL process. Its not really 'faster' than other tools, its that the approach of using a small C program to move the data is just inheriently faster on average.
CloverETL has a few screaming-fast transformation features, and they are probably fast due to the use of a RegEx engine. Having used JDBC though, I would estimate that DB reads and Writes are faster with ADO/OLEDB. This is not a knock, but a comparison of the technologies used.

It should be noted that I have used Informatica and ETI to create conversions so complex that they should not even be attempted in SSIS. This is where I believe that other tools can outshine SSIS - handling complexity with multiple databases for source and destination, lookups, and hundreds of intermediate actions in-between.

The term 'performance' also needs to be defined a bit here.
We test performance of SSIS based on the time for a Package to run.
If you run a package in the BIDS it usually will run a little slower than if you configure a job or execute DTEXEC.
If you run on 64-bit, it will tend to run a little faster than 32-bit.
Since there are a number of ways to execute a SSIS package, there are a number of ways one can perceive the performance.