Slow running Job in Datastage

subbulakshmi_su · Post by **subbulakshmi_su** » Sat Jun 26, 2010 3:36 am

Hi,

I have a requirement where in a source file which has to be loaded into the oracle table. The table contains 7 primary keys. I need to check whether there is any duplicate data present in the promary key combination and if that is the case, it should be rejected to the error file.

I have designed a job like i have used Sort stage after sequential stage ( to read the source file) to sort all the primary keys and set flag in the transformer using stage variables and constraint will filter the duplicate data according to the flag set in the stage variable.

Since i am using Sort stage for sorting out 7 fields, its taking more time to sort for 24 lak records (nearly 3 hours). Can any suggest me to improve the performance?

antonyraj.deva · Post by **antonyraj.deva** » Sat Jun 26, 2010 3:59 am

Hi Subbulakshmi,

Welcome aboard. Sorting on 7 keys is for sure going to take sometime.

How many nodes are there in your environment and what partitioning method are you using?

chulett · Post by **chulett** » Sat Jun 26, 2010 6:26 am

The post is marked as a Server issue, even though we're in the PX forum, so I'm guessing there's no nodes involved here. Once confirmed, I'll move the post.

The Server sort stage is not known for its speed. A better alternative would be any kind of command line sort option and you could leverage that using the Filter option in the Sequential File stage.

antonyraj.deva · Post by **antonyraj.deva** » Sun Jun 27, 2010 4:53 am

Didn't notice the "Server" bit Craig.

ray.wurlod · Post by **ray.wurlod** » Sun Jun 27, 2010 1:00 pm

Use the UNIX sort command or a third-party sort utility such as CoSort or SyncSort.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Jun 28, 2010 2:24 am

It will be better to sort and locate duplicates prior to coming into DataStage.

This is to avoid committing records only to find a duplicate down the line.