DSXchange

Hi,

I'm loading one Oracle table with around 19 million records. I was checking speed of the process by making changes in the job. My job had a fetch query and some sort stages, remove duplicates, joins, a transformer, a funnel and ultimately loads to an Oracle table.

This job was taking around 3 hrs among which 2 hr 15 min was just loading into the Oracle table. Then I split this job in two jobs by changing the Oracle connector load to a dataset and adding a second job moving the dataset to Oracle connector table load.

Both jobs in second case took 49 minutes with only 6 minutes for load to Oracle. I'm wondering why the second case the load took just 6 minutes in comparison with 2 hr 15 min in first case. Could you please advise reason for this?

Thanks

[Note - edited for clarity - Andy]

How did you determine that it was 2:15 for the oracle load aspect?

Take your original job, remove oracle target and replace with peek. That should give you the pure transform duration.

I removed oracle target and kept dataset in new job after split, will that not give same transform duration? Actually and also in my old job by monitoring link stats at run time the job was just working at load for 2:15 hrs thru. By using these two ways I determined that transform time was just 45 minutes. In both cases it was 45 minutes.

So you are saying that you chopped the job into two pieces by doing a dataset drop, then job number two had two stages... dataset->Oracle and the job #1 ran in 45 mins and Job #2 ran in 6 mins?

Yet you used the same stage properties on the Oracle connector?

I have an old topic (from a former user account that quit working) on this exact same type of issue that you may find interesting:

viewtopic.php?t=135492

Paul, yes, same stage properties

qt_ky

yes, it is exactly the same thing

Were you loading back into the same table you retrieved data from? (Note - this includes pulling from a view that references the target table).

I'm not loading into the same table.