Performance tuning in Server Job (part of a sequence)
Posted: Fri Apr 23, 2010 7:16 pm
Dear all,
I'm trying to do performance tuning for a Job Sequence which has about 25 SERVER jobs mainly involving 3 types of jobs (Version 8.1)
1.Conversion
2.Look up
3.Load
First I ran the sequence and made a note of the stats (Time taken) for each job to complete, I thought in order to improve the performance first it would be less invasive to tweek the resources and enabled In Process Row buffering especially for the lookup jobs containing Hash files, there was an improvement by about 25% (for the individual jobs) as far as run time is concerned.
My next step was I looked into a LOAD job of the sequence which has 3 stages Seq File, Transformer and ORAOCI
The job is pretty simple which just transfers data from the Seq File to the ORacle DB (Seq File --> XFM --> ORAOCI) with 3 columns added in the transformer with very simple transformations, no stage variables have been used.
I ran this job for a file of about 2 Million records and it took almost 2 hours for it to complete.
I then added two IPC stages in this job one in between the Seq File and the Transformer and the other in between the Transformer and the ORAOCI (Seq File --> IPC --> XFM --> IPC --> ORAOCI)
I ran the job now and WOW!!!!.... The Job Finished in about 7 mins as opposed to around 2 Hrs earlier.
But there are two problems which I encountered.
1. In the ORAOCI stage the condition given is "Clear table and then insert".... But when I tried to run the modified job again the job aborted after loading initial few rows, I suspect because the table was not cleared of the earlier data, so by adding the IPC stage before the ORAOCI stage did it create any kind of a problem for the table to be cleared? (It later worked when I manually cleared the table)
2. When I tried to run entire sequence (after clearing the table) with this modified job in it, the sequence aborted when it came to this job (There is a lookup job before this job), because in the director it said that this modified job aborted, is it because of the "Time Out" factor in the IPC stage?
If any of you DS Pundits could help me on this I would really appreciate it.
Also if you could let me know if there are any risk factors associated with using the IPC stage (Do's and Dont's of IPC) and typically what kind of performance tuning methods should be used for a Look UP and Load Jobs (SERVER JOBS) that would be great.
Sorry for the long story but I had to explain in detail to give a clear picture, Any help would be highly appreciated.
Regards
I'm trying to do performance tuning for a Job Sequence which has about 25 SERVER jobs mainly involving 3 types of jobs (Version 8.1)
1.Conversion
2.Look up
3.Load
First I ran the sequence and made a note of the stats (Time taken) for each job to complete, I thought in order to improve the performance first it would be less invasive to tweek the resources and enabled In Process Row buffering especially for the lookup jobs containing Hash files, there was an improvement by about 25% (for the individual jobs) as far as run time is concerned.
My next step was I looked into a LOAD job of the sequence which has 3 stages Seq File, Transformer and ORAOCI
The job is pretty simple which just transfers data from the Seq File to the ORacle DB (Seq File --> XFM --> ORAOCI) with 3 columns added in the transformer with very simple transformations, no stage variables have been used.
I ran this job for a file of about 2 Million records and it took almost 2 hours for it to complete.
I then added two IPC stages in this job one in between the Seq File and the Transformer and the other in between the Transformer and the ORAOCI (Seq File --> IPC --> XFM --> IPC --> ORAOCI)
I ran the job now and WOW!!!!.... The Job Finished in about 7 mins as opposed to around 2 Hrs earlier.
But there are two problems which I encountered.
1. In the ORAOCI stage the condition given is "Clear table and then insert".... But when I tried to run the modified job again the job aborted after loading initial few rows, I suspect because the table was not cleared of the earlier data, so by adding the IPC stage before the ORAOCI stage did it create any kind of a problem for the table to be cleared? (It later worked when I manually cleared the table)
2. When I tried to run entire sequence (after clearing the table) with this modified job in it, the sequence aborted when it came to this job (There is a lookup job before this job), because in the director it said that this modified job aborted, is it because of the "Time Out" factor in the IPC stage?
If any of you DS Pundits could help me on this I would really appreciate it.
Also if you could let me know if there are any risk factors associated with using the IPC stage (Do's and Dont's of IPC) and typically what kind of performance tuning methods should be used for a Look UP and Load Jobs (SERVER JOBS) that would be great.
Sorry for the long story but I had to explain in detail to give a clear picture, Any help would be highly appreciated.
Regards