Datastage Loading in Oracle table Speed
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 43
- Joined: Wed Feb 12, 2014 12:34 pm
- Location: Hyderabad, India
Datastage Loading in Oracle table Speed
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]
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]
Last edited by syedmuhammedmehdi on Fri Oct 20, 2017 7:09 pm, edited 1 time in total.
SyedMuhammadMehdi
-
- Participant
- Posts: 43
- Joined: Wed Feb 12, 2014 12:34 pm
- Location: Hyderabad, India
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.
SyedMuhammadMehdi
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
viewtopic.php?t=135492
Choose a job you love, and you will never have to work a day in your life. - Confucius
-
- Participant
- Posts: 43
- Joined: Wed Feb 12, 2014 12:34 pm
- Location: Hyderabad, India
-
- Participant
- Posts: 43
- Joined: Wed Feb 12, 2014 12:34 pm
- Location: Hyderabad, India
-
- Participant
- Posts: 43
- Joined: Wed Feb 12, 2014 12:34 pm
- Location: Hyderabad, India