Performance Issue

[poorna_76]

We have scenario where a particular job runs in 5 secs (stand - alone).
When the same job run through JobControl and/or Sequence,
runs for 2 hours.


We are passing the same parameters either when run individually(job alone) or through the jobcontrol and/or Sequence.

Job is reading from a Table A (SQL Server) does some processing and Updates the same Table A.


Any thoughts.

Thanks in Advance.

[ArndW]

Are you 100% certain that the job gets started the same way (i.e. same parameters, no reset-if-required switch) between the 5-second and the 2-hour run?

Check you 2-hour log - is there a large startup or finish time or do the rows process much slow?

DataStage jobs are started as background processes; once they start they really don't care who started them or how.

[poorna_76]

Are you 100% certain that the job gets started the same way (i.e. same parameters, no reset-if-required switch) between the 5-second and the 2-hour run?

Check you 2-hour log - is there a large startup or finish time or do the rows process much slow?

DataStage jobs are started as background processes; once they start they really don't care who started them or how.

Thanks ArndW.

Small correction , job runs for 5 minutes (when submitted alone)
Job runs for 2 hours (when run through JobControl and/or Sequence)

We switched on the trace at the Server Level to identify the issue.

The job issues a user-defined query and waiting for long time to get the results back from SQL server.
When we execute the same query in SQL Server QueryAnalyser,
it executes in less than 5 secs.

We are passing the same parameters,
when we run thorugh job alone and also while running through JobControl.

[ArndW]

So the difference lies in the time required to set up the query? I don't know what the difference could be. It must be something else that hasn't been identified yet - could there be anything else? Different userids on UNIX or in connecting to the DB?

[poorna_76]

So the difference lies in the time required to set up the query? I don't know what the difference could be. It must be something else that hasn't been identified yet - could there be anything else? Different userids on UNIX or in connecting to the DB?

We are on Windows Server.
SQL Server takes the NT userid/password for connecting to the database.
Same user has ran this job, with all different options.

[kcbland]

Is it 5 seconds to get the first row or the last row of data? Many people confuse the quick response of the first found rows as the indication the entire process takes a short time. Try using an ORDER BY in your SQL query tool to cause all rows to be computed first and see if the time changes.

[poorna_76]

Is it 5 seconds to get the first row or the last row of data? Many people confuse the quick response of the first found rows as the indication the entire process takes a short time. Try using an ORDER BY in your SQL query tool to cause all rows to be computed first and see if the time changes.

Thanks for the replies.

The overall time ,job takes to complete is 5 minutes(if run individually)

The overall time job takes to complete is > 2 hours (depends),
if run through JobControl and/or Sequencer.

[ray.wurlod]

Put in timing points in the job control code or in the job sequence (note: it's sequence, not sequencer). Use a Routine activity to do this in the job sequence. Find out where the delay is.

[kumar_s]

Hi Poorna,

What is the structure of your sequence, is that particular job called sequentialy (alone) or is it being called along with any other job which involves some SQL query?

Again the Arnds query, What is the startup time for your job when running on the sequence?

-Kumar