Performance Issue Stored Procedure Vs Datastage
Posted: Tue Apr 09, 2013 11:03 am
Hi,
DS 8.7 on Windows Server 2008 R2 (Dual Core, 64 GB RAM)
Src and Tgt Database - SQL Server 2008
Currently we have a SP that inserts about 4.5 Million records everyday..It drops target table first, creates and inserts the data from a 3 table join query, then runs 3 different updates on this data (Inner joins)
then recreates the Primary Key on this table and send an email on completion..All this takes about 45 secs
I replicated the same process in Datastage(Verified the results, they are exactly same) but it takes about 9 mins to do this.
Using ODBC Connector stage..I only have inserts and No Updates(the 3 updates are implemented using 3 different join stages as reference data is big and hence not using LookUps) and finally a Transformer to derive certain fields based on join values
as done in the SP
What could be the reasons for such a drastic difference in performance?..
It is getting very difficult to justify the use of Datastage in this case
Thanks in advance
DS 8.7 on Windows Server 2008 R2 (Dual Core, 64 GB RAM)
Src and Tgt Database - SQL Server 2008
Currently we have a SP that inserts about 4.5 Million records everyday..It drops target table first, creates and inserts the data from a 3 table join query, then runs 3 different updates on this data (Inner joins)
then recreates the Primary Key on this table and send an email on completion..All this takes about 45 secs
I replicated the same process in Datastage(Verified the results, they are exactly same) but it takes about 9 mins to do this.
Using ODBC Connector stage..I only have inserts and No Updates(the 3 updates are implemented using 3 different join stages as reference data is big and hence not using LookUps) and finally a Transformer to derive certain fields based on join values
as done in the SP
What could be the reasons for such a drastic difference in performance?..
It is getting very difficult to justify the use of Datastage in this case
Thanks in advance