How to increase ETL performance in Informatica for Netezza as a source and SQL Server as a target? What settings or configuration on the Informatica server, in the Informatica software itself, or on the database servers can be changed to increase Informatica ETL throughput?  What are some benchmarks we can set to troubleshoot performance?  We are specifically using Netezza as a source and SQL Server as a target.

Please exclude multi-threading and Informatica partitioning from this question.

This we've done in the past:


restart servers every so often
remove indexes on target tables in SQL Server before ETL load
increase commit level
My experience with Informatica accessing both SQL Server and Netezza can be summed up to this:


Reading is equally fast (100,000 rows/sec; sometimes twice that speed), provided that the select SQL is simple enough, that is:


no joins;
no group by;
no sort;
where clauses only against the cluster key of the SQL Server table.


In all other cases Netezza will beat SQL Server.
Inserts with PowerCenter are of the array type, and SQL Server can usually receive 2000 to 4000 rows per second. 

Inserts into Netezza run at 80,000 to 200,000 rows per second if PowerCenter is not the bottleneck.
Updates/deletes with PowerCenter have been notoriously slow on all databases for years, since the SQL executed is singletons and not an array. Therefore, a session typically drops to ~500 rows per second with SQL Server as a target, and a mere 8 rows per second with Netezza.

Due to that, we have defined staging tables per PowerCenter target in Netezza and write all updates/deletes to those tables while the session executes. These changes are then applied in bulk as a target post SQL.
This scales well since all write operations now run at similar speeds to insert.


The best solution for the notorious writer bottleneck with SQL Server as a target is to spend a lot of time inside PowerCenter comparing source with target and only write whatever difference you detect. That will carry you a long way but not scale.

My final question is, why move data from Netezza to SQLserver? If your business has requirements that truly cannot be met on Netezza, please specify which, perhaps they could be addressed.