Performance Improvement
Moderators: chulett, rschirm, roy
Performance Improvement
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!
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!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
I am not familiar with the Oracle sytax. Maybe its something else in place of ?.
But thats basically the dirction.
in that sql apply the where clause as
Code: Select all
where key = ?
But thats basically the dirction.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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'))=?);
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'))=?);
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
In theory there's no difference between theory and practice. In practice there is.
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.
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.
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