my job is taking 45 minits to load the data

pavankatra · Post by **pavankatra** » Tue Mar 29, 2011 4:26 am

Hi All,
My job design is like this
dataset----->transformer------->odbc connector

my job is having 169000 records,i am using update then insert to load the data.It is taking 45 minits to load the data.I am using HASH partitioning in transformer and odbc stages.

can anyone please suggest me how to improve the performance.

thanks for your help in advance

GJ_Stage · Post by **GJ_Stage** » Tue Mar 29, 2011 4:50 am

Do pre-Sorting in oracle itself before creating dataset and then try.

pavankatra · Post by **pavankatra** » Tue Mar 29, 2011 5:51 am

GJ_Stage wrote:Do pre-Sorting in oracle itself before creating dataset and then try.

Thanks for you reply

I have tried,still it is taking same time(45 minits) to load

GJ_Stage · Post by **GJ_Stage** » Tue Mar 29, 2011 6:01 am

Might be below reason:
1. Check TABLE is getting lock while loading in case.
2. Please provide what kind of operation performing in Transformer stage.

antonyraj.deva · Post by **antonyraj.deva** » Tue Mar 29, 2011 6:03 am

Could you post the logic used inside the Transformer? Maybe that's what is slowing down the job.

Also it would be better to look into the number of columns and data types.

pavankatra · Post by **pavankatra** » Tue Mar 29, 2011 6:06 am

antonyraj.deva wrote:Could you post the logic used inside the Transformer? Maybe that's what is slowing down the job.

Also it would be better to look into the number of columns and data types.

thank you tony

In transformer i am harcoded some columns and used typeconversion function

antonyraj.deva · Post by **antonyraj.deva** » Tue Mar 29, 2011 6:11 am

Hard coding of columns is not known to be a performance breaker but type conversions if not done with care will have an impact.

Share the column metadata which is used before and after type conversion.

GJ_Stage · Post by **GJ_Stage** » Tue Mar 29, 2011 6:13 am

Tony , You are correct. There is no other reason to make it slow.

chulett · Post by **chulett** » Tue Mar 29, 2011 7:01 am

GJ_Stage wrote:Tony , You are correct. There is no other reason to make it slow.

Sure there is. Do they have an index over the update key fields in the target?

PaulVL · Post by **PaulVL** » Tue Mar 29, 2011 9:17 am

What database type are you writing to?

Oracle? SQL srv? etc...

What has the job monitor shown you (during execution) ?

greggknight · Post by **greggknight** » Tue Mar 29, 2011 12:56 pm

I would also do inserts then update
less scanning.

ray.wurlod · Post by **ray.wurlod** » Tue Mar 29, 2011 4:31 pm

You can prove the location of the bottleneck by writing to a Sequential File rather than Oracle. If that's no faster, the problem is in DataStage. If it is markedly faster, then the problem is in Oracle. Updating tables where the columns mentioned in the WHERE clause are not indexed is notoriously slow in any database.

pavankatra · Post by **pavankatra** » Fri Apr 01, 2011 12:35 pm

Thanks ray,sorry for the late reply,i was on leave

i have done that,when i am loading into sequential file it is not taking time.
whenever i am trying to load the data into ORACLE i am getting this problem.

i think there may be some indexes on key columns,i will check and let you know guys.

thanks again

pavankatra · Post by **pavankatra** » Wed Apr 06, 2011 4:21 am

seems to be it is database issue,i will talk to my dba.

Thanks all

chulett · Post by **chulett** » Wed Apr 06, 2011 6:42 am

What kind of 'database issue'? Can you post what your actual resolution was?