Data Stage Designer Performance Tuning in Server jobs

1. In derivations, instead of calling routines, implement the logic in the derivation. This eliminates the overhead of the procedure call.
   
2. Implement the logic in a stage variable and then point the stage variable to the actual field.
   
3. Use Transforms rather than using routines.
   
4. While using the ODBC stage adjust the rows per transaction setting. Try setting to 1000, 5000, or 10000.
   
5. Adjust the array size setting. Try setting to 10, 100, or 1000.
   
6. If output rows are Inserts or Appends and not Updates, consider using a native bulk loader.
   
7. Eliminate unused columns.
   
8. Eliminate unused references.
   
9. Minimize using the stages like SORT, AGGREGATE which minimizes the performance of the job.
   
10. If more transformer Stages are used in sequence in a job, Enable the inter process buffering in the job properties or use the InterProcess Stage between Transformers which improves the performance.
    
11. Direct output to a sequential file compatible with the bulk loader. Then invoke the bulk loader using an after-job subroutine. The bulk loader for Oracle is SQLLDR.
    
12. Avoid using 'like' operator in user defined queries in ODBC stages
    
13. Avoid using stored procedures until and unless the functionality cannot be implemented in Data Stage jobs.
    
14. Tips while creating routines
15. Use variables in the routines.
16. Assign empty values to the variables before using them.
17. Routines will return Ans as return value. Instead of using ANS multiple times, use a variable .Implement the logic in that variable and assign that variable to ANS.
18. For Example: Ans = ''
    If ( Len(Trim(Name)) > 45) Then Ans = Ans : ',' : '24356' End
    Ans = Ans
    The above logic can be implemented using
    ErrStr = ''Ans = ''
    If ( Len(Trim(Name)) > 45) Then ErrStr := ',24356'End Ans = ErrStr