Routine or derivation
Posted: Sun May 29, 2011 10:31 pm
Hello all,
I am analyzing a set of Data Stage server jobs for performance enhancement. The job reads a source file (Aproximately 338420 records), does 2-3 lookups and then writes to 2 Oracle tables (DRS stage is used).
It was noted that one of the jobs calls a routine for each record (Aproximately 338420 records) to write the current timestamp to output link .
The routine code is nothing but
OConv(Date(),"D-YMD[4,2,2]") : " " : OConv(Time(),"MTS")
Currently the job takes 40 minutes to complete and the bottleneck happens between the transformer where the above transformation happens and the inserts that happen to the tables. If I avoid the routine call and instead provide the derivation above for the column, will I get any significant improve in the job performance?
Also the current Array Size and transaction size are 1 and 0 respectively. If I raise them to 15000, can the job achieve a better performance?
Your inputs are highly appreciated.
I am analyzing a set of Data Stage server jobs for performance enhancement. The job reads a source file (Aproximately 338420 records), does 2-3 lookups and then writes to 2 Oracle tables (DRS stage is used).
It was noted that one of the jobs calls a routine for each record (Aproximately 338420 records) to write the current timestamp to output link .
The routine code is nothing but
OConv(Date(),"D-YMD[4,2,2]") : " " : OConv(Time(),"MTS")
Currently the job takes 40 minutes to complete and the bottleneck happens between the transformer where the above transformation happens and the inserts that happen to the tables. If I avoid the routine call and instead provide the derivation above for the column, will I get any significant improve in the job performance?
Also the current Array Size and transaction size are 1 and 0 respectively. If I raise them to 15000, can the job achieve a better performance?
Your inputs are highly appreciated.