How to Tuning of the server JOB which is taking 4hrs ?

[Rubu]

HI All,

Please let me know how to bring down the timing for the job run . To load the data it is taking 4 hr . There are 3 lookup table in the mapping which is haivng 20 lacs of record .The source of the mapping is having 8lacs . There are 8lookups.I have used link partitioner and collector .I have set the properties of this partioner to 'Hash' and collector as 'sort/merge' .the buffer size i have incresed to 512 and timout to 500.

Please suggest how to improve the performance using this server edition.

Thanks
rubu

[dsdesigner]

Hi Rubu
You say that you have three lookups, that have 20lacs records in each??

In this case this is the beginning of your problems. A lookup is not meant to be used against 20 lacs records.

You should do better joning the source to your lookups in SQL or you can use the mergr stage.

Also it will benefit to set the Inter processing buffering on

Thanks

HI All,

Please let me know how to bring down the timing for the job run . To load the data it is taking 4 hr . There are 3 lookup table in the mapping which is haivng 20 lacs of record .The source of the mapping is having 8lacs . There are 8lookups.I have used link partitioner and collector .I have set the properties of this partioner to 'Hash' and collector as 'sort/merge' .the buffer size i have incresed to 512 and timout to 500.

Please suggest how to improve the performance using this server edition.

Thanks
rubu

[wilcywilliam]

Change the lookup tables to hashed file stages. Use the inter-process (IPC) stage which provides a communication channel between DataStage processes running simultaneously in the same job. It allows you to design jobs that run on SMP systems with great performance benefits.

[Sainath.Srinivasan]

Take a copy of the job and try reducing the number of lookups. By that you can identify the one that is causing the most impact. You then need to identify any alternate method to resolve it.