Hi Friends
I did a job in which i have to either update the table or insert into the table(Oracle). My job has 3 hashed files. First Hashed file does the lookup and based on it, it either updates or inserts in to the same hashed file. The other two hashed files does the look up and if not found it just inserts.
Here is my problem, when i run the job it is so slow that it is taking too long to finish. The performance is really bad. I dont have any clue
Can anybody help me on this
ps:Target table has indexes.
Does anybody have any suggestions.
thanks
regards
Aakash
Performance issue of a job
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 24
- Joined: Sun Nov 13, 2005 10:59 pm
You need to benchmark how long pure inserts take and how long pure updates take. Spool your inserts to a text file, spool your updates to another. Write a job that just reads the inserts text file and inserts into the target database. Copy that job and have it read the updates text file and change the INSERT SQL to UPDATE existing rows only SQL.
Now you will see how fast DS can create the INSERTS and the UPDATES files within waiting on the database to do its work. Also, you'll see how fast your rows of data can be inserted into the database without waiting for transformations to occur. In addition, you'll see how slow updates are considering all of the ramifications of updating data.
Leave your jobs broken down like this, you'll be able to tune each one independently.
Now you will see how fast DS can create the INSERTS and the UPDATES files within waiting on the database to do its work. Also, you'll see how fast your rows of data can be inserted into the database without waiting for transformations to occur. In addition, you'll see how slow updates are considering all of the ramifications of updating data.
Leave your jobs broken down like this, you'll be able to tune each one independently.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 84
- Joined: Thu Dec 04, 2003 9:55 pm
Hi,
I did experience the same issue in my previous project, as the Guru suggested you can have two Sequential files, one for for Inserts and One for Updates and then using the Oracle Bulk Stage you could Insert the data and then in the next you could do your Updates.
As suggested "Leave your jobs broken down like this, you'll be able to tune each one independently."
With Regards
Mahesh
I did experience the same issue in my previous project, as the Guru suggested you can have two Sequential files, one for for Inserts and One for Updates and then using the Oracle Bulk Stage you could Insert the data and then in the next you could do your Updates.
As suggested "Leave your jobs broken down like this, you'll be able to tune each one independently."
With Regards
Mahesh
Re: Performance issue of a job
Did you get this sorted? You say that the target table has indexes. How many indexes? Do they need to reorged? This would slow down the inserts.aakashmalu wrote: ps:Target table has indexes.
Can the update statement actually use the index that you think it should, or is it doing a TS scan to update 1 row.
Neil Courtney
"MG - Life's too short not to"
"MG - Life's too short not to"