How to improve performance of job(having one insert and one

[pagerakesh]

Hi friends

I have a job, inwhich i pull around 71k records through two transformers, each having one lookups and one lookup creates a hash file, if i start the job, initially it runs at 130 record per sec but gradually dcreases to 4 rec/sec, from the second transformer i insert aswell as update to a table having around 160 columns,
please help me in resolving this issue, this job takes only 10 min in one server but takes 12 hrs in other server..

thnaks
rakesh

[ArndW]

Rakesh,

although you haven't specified your source or target, when jobs slow down over time it is most often due to the output stage. If you are writing to a database, it might be that there are indices being built in the background or activities like buffering or rollback/before images.

Try using a sequential file as a test output stream and monitor performance.

[ray.wurlod]

this job takes only 10 min in one server but takes 12 hrs in other server..

What's different between the servers?

[vikasjawa]

What's different between the servers? ...[/quote]

Hi,
The difference may be one of them:
1) Hardware configuration: The other server would have less disk/swap space/processing power.
2) Other Jobs may be running at the same time,resulting in resource sharing.

[chulett]

We know all kinds of things that may be different. The hope here is that Rakesh puts some effort into determining what is different betweeen his environments. This is not something we can do for him other than to guide. However, if causes can be determined we can help with the resolution.

And if a change of 'server' also means a change in database instances - for example, from a development instance to a test instance - that opens up even more areas to investigate.

Rakesh - you need to determine the bottle-neck in your job, what 'section' or portion of the job is the culprit here. As Arnd notes, that's typically the target database and it's easy enough to check. Replace your target stage with a Sequential File stage and see what happens.

[asitagrawal]

Hi Rakesh,

Just re-iterating what Ray and chulett have said,

1. replace the output with the Seq File and then use the LOAD for loading the target database tables.
2. put @FALSE constraint in the output, to determine the perfromance of fetching from the input.

Analyse the above factors and then see the factors for improvements.

HTH,
Asit

[thamark]

Try writing into sequential file instead of database Insert/Update(Upsert), since upsert is costlier operation to do.

If this method runs in minutes then you can try to identify operation type and do it using individual stage.