DSXchange

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

Adding indices usually increases query performance but slows down insert/update operations.

If you remove the lookup operation what does your performance do?

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.

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.

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!