Tuning Data stage jobs for performance
Posted: Wed Jul 20, 2011 2:27 pm
Hi all,
I am analyzaing a server job for performance issues. This is a server job which uses 5 look ups (Uses DRS stage). Each of the stage executes SQL queries that has multiple joins on tables (some of the tables have close to 300,000 records). In a lower environment the job completes in 4 minutes whereas in Production environment it takes close to 4 hours!!. The job runs in both environments at the same time, so not suspecting anything with table contention...etc
I am trying to see the query plan and also would be checking the stale statistics on the tables at the time of run.
I would like to explore at Data stage level, whether setting any environment variables at project / job level can better the performance. Could you please advise.
I am analyzaing a server job for performance issues. This is a server job which uses 5 look ups (Uses DRS stage). Each of the stage executes SQL queries that has multiple joins on tables (some of the tables have close to 300,000 records). In a lower environment the job completes in 4 minutes whereas in Production environment it takes close to 4 hours!!. The job runs in both environments at the same time, so not suspecting anything with table contention...etc
I am trying to see the query plan and also would be checking the stale statistics on the tables at the time of run.
I would like to explore at Data stage level, whether setting any environment variables at project / job level can better the performance. Could you please advise.