DSXchange

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

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

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

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.

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.

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

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.

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

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?

What database type are you writing to?

Oracle? SQL srv? etc...

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

I would also do inserts then update
less scanning.

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.

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

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

Thanks all

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