We are updating 8lakh records based on 4 keys. we are also using a lookup and then updating into the target. Sofar the records are getting updated at a rate of 13 rows/sec.
In order to increase the performance we have created an index with those 4 keys. yet the performance is too slow (13 rows/sec).
JOB DETAILS :
Source:Oracle
Transformer
Target:Oracle.
Lookup:Oracle
Performance upgradation needed in datastage 7.X
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 26
- Joined: Tue Dec 18, 2007 6:31 am
Adding indices usually increases query performance but slows down insert/update operations.
If you remove the lookup operation what does your performance do?
If you remove the lookup operation what does your performance do?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Pre-load the lookup information into a hashed file. Only load the rows and columns that will actually be needed. Cache the hashed file in memory.
Create separate links for insert-only and update-only operations. Consider using bulk loading for the insert-only stream.
Purchase a more powerful server.
Stop using rows/sec as a performance metric. It is almost totally meaningless.
Create separate links for insert-only and update-only operations. Consider using bulk loading for the insert-only stream.
Purchase a more powerful server.
Stop using rows/sec as a performance metric. It is almost totally meaningless.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
You should avoid using anything but a hash file for lookups. Since you are using Oracle as a lookup, a separate SELECT query is done for each and every lookup. So you should actually consider yourself very lucky getting 13 queries returned every second. That's not 13 rows from a query that's 13 separate queries.
Instead - create a hash file of the Oracle table and if the table is very large you will need to constrain the query which builds it some how. There are many techniques which I haven't the time to go into.
Instead - create a hash file of the Oracle table and if the table is very large you will need to constrain the query which builds it some how. There are many techniques which I haven't the time to go into.
Michael Favero
2852 Humboldt Ave So
Minneapolis, MN 55408
2852 Humboldt Ave So
Minneapolis, MN 55408
After doing what was suggested above you still are not getting good performance, consider extracting the data to a sequential file and then load that as opposed to a server to server connection. Sometimes, part of what is slowing things down is network traffic.
Just a thought.
Merry Christmas!
Just a thought.
Merry Christmas!
Bestest!
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"