Performance of datasets

go4it · Post by **go4it** » Wed Oct 28, 2009 10:48 am

Hi,

I have around 15 million records extracted and written to a dataset 1 and transformed the dataset 1 to dataset 2 ( not much changes, mostly one to one mapping), however the issue I have is it takes a while to write to the dataset 2 and takes much longer time to load the data from dataset 2 into the Oracle table.

Could you please advice on what best could be done to improve the performance on this partiuclar job to Extract, Transform and Load the data ?

Would using a Fileset be better opion ?

Pls advice

Mahesh

ray.wurlod · Post by **ray.wurlod** » Wed Oct 28, 2009 11:25 am

A File Set will always be slower than a Data Set. File Set uses export and import operator, while Data Set uses copy operator.

The slowness loading the Oracle table is something in Oracle. You can prove this by constraining the rows to pass zero rows to Oracle, thereby getting a measure of the raw read speed of a Data Set stage.

go4it · Post by **go4it** » Wed Oct 28, 2009 12:13 pm

Thanks ray, but the problem here is when I to write to another datasets.

Dataset1 -> xfm -> Dataset2

Takes more time and aborts.

chulett · Post by **chulett** » Wed Oct 28, 2009 1:32 pm

'Takes time'? 'Aborts'? Of course it takes time, you'd need to let us know how much and why you think it's too long. And post your abort message, of course.

go4it · Post by **go4it** » Thu Oct 29, 2009 7:03 am

Hi Ray/Craig,

I tried teh following and it worked, when running I changed the Config file and it worked fine, but when the next job to load the dataset in oracle was called it was taking a while and aborting after a while.

On discussions with the DBA, found that there was not enough space and the number of redo log files were increased.

Its working fine now.

Thanks for your help.