Performance Improvement

svkworld · Post by **svkworld** » Wed Apr 05, 2006 7:11 am

Hi,

I have a job that reads from a table(partitioned),goes through a transformer and finally writes to the same table (update existing or insert new).My problem is it runs very slowly,about 92 rows/sec. This is a very large table and I need to updata atleast 5 million rows. Can anyone suggest a way to improve this, so the job will run faster?

Thanks!

jasper · Post by **jasper** » Wed Apr 05, 2006 7:18 am

most common suggestion on this forum: split the inserts and updates in your job and do them seperatly.

Offcource always important: check your indexes(mostly for the updates)

DSguru2B · Post by **DSguru2B** » Wed Apr 05, 2006 7:20 am

try splitting your data for updates and inserts. that will work much faster. Also, whats your target DB

svkworld · Post by **svkworld** » Wed Apr 05, 2006 7:23 am

Thanks for your replies. I use Oracle 8i. I will try to split the inserts and updates.

Thanks!

sjhouse · Post by **sjhouse** » Wed Apr 05, 2006 7:27 am

We have been able to increase performance by changing our initial select to have a where clause to loop through partition keys (PARTITION_KEY = #Counter#) and we disable indexes.

svkworld · Post by **svkworld** » Wed Apr 05, 2006 11:55 am

I tried to seperate the inserts and updates. Now I get the error:
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-00936: missing expression.

Can anyone tell me to get rid of this error?

Thanks!

ray.wurlod · Post by **ray.wurlod** » Wed Apr 05, 2006 4:15 pm

Inspect the SQL then supply the missing expression. It might be the WHERE clause in an UPDATE.

svkworld · Post by **svkworld** » Thu Apr 06, 2006 11:20 am

Hi,

I'm not sure what to supply in the where clause because I'm updating the table on a unique_id column but that column doesnot get populated in the end table. So I am not sure what to put in the where clause. Any suggestions, please?

DSguru2B · Post by **DSguru2B** » Thu Apr 06, 2006 11:43 am

So pass the key to your target stage where your updating the table. Write a user defined sql updating all the columns you want to be updated.
in that sql apply the where clause as

Code: Select all

where key = ?

I am not familiar with the Oracle sytax. Maybe its something else in place of ?.
But thats basically the dirction.

svkworld · Post by **svkworld** » Wed Apr 12, 2006 1:22 pm

Hi,

Now I am getting the error : Invalid Argument value
Can anyone tell me what is wrong with the below query.

UPDATE table SET a = ?, b = ?, c = ?, d = ?,
e= ?, ALL_AMT = ?, f = ?, g = ?, h = ?, i = ?, j = ?,
k = ? WHERE ((SUBSTR(a),1,11)||b||(TO_CHAR(c,'YYYYMMDD'))||0||d||(TO_CHAR(e,'YYYYMMDD'))=?);

ray.wurlod · Post by **ray.wurlod** » Wed Apr 12, 2006 4:56 pm

Does your job design have 13 columns, one of which is Key ?

ogmios · Post by **ogmios** » Thu Apr 13, 2006 3:58 am

For the original question on making it faster. I suppose you're using the ODBC stage (seeing the error messages)... switch to the oracle stage or the DRS one to see a significant speed increase.

For DB2 the speed difference between ODBC and the DB2 stage is usually not that big... Between ODBC and Oracle I've gotten huge performance increases in the past.

Ogmios

svkworld · Post by **svkworld** » Thu Apr 13, 2006 7:43 am

Hi Ray,

The combination of all the 5 columns in the where clause is what makes it unique.
Any help is appreciated.

Thanks!

kcbland · Post by **kcbland** » Thu Apr 13, 2006 7:44 am

Spool to a file in one job, transform in another splitting inserts from updates into separate files, then a job to apply inserts and another for updates.

Your fundamental problem is that you're querying the same table you are loading. You should NOT do this. You're using rollback to hold the entire query set, and then more rollback to insert/update rows.

svkworld · Post by **svkworld** » Thu Apr 13, 2006 2:18 pm

Thanks to all of you.

I was able to resolve the issue.