Performance issue of a job

[aakashmalu]

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

[kcbland]

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.

[MaheshKumar Sugunaraj]

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

[Neil C]

ps:Target table has indexes.

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.

Can the update statement actually use the index that you think it should, or is it doing a TS scan to update 1 row.