Performance in a simple mapping

[JDAEMS]

Dear,

We've got some serious performance issues in a very simple mapping. According to the Oracle DBA, there is nothing else he can do for us and the problem lies within the mapping.

This is what the mapping does:

It is a mapping to fill up a currency-rate table:

- It checks if the date for which there is a currency, exists in the dates table (28.000 records), to check if it is a valid date. We use a look-up with caching enabled.
- It checks if the currency TO and the currency FROM exist in the currency table (52 records), to check if it is a valid currency. We use two look-ups with caching enabled.
- After that, we check if the record already exists in the currency rate table (the target) with a look-up (caching disabled). We do this, because we are handling an incremental file. we get a flag, that says if it is an update, delete or insert. If it is an update, then the record should already exist and we need to get the primary key from the table.
- And then we write to the target.

This is all the mapping does. o do you have some suggestions on how to make this more performant.

Now it's doing from Oracle to Oracle 100 rows/second.

Thank you.

Jelle

 

[blom0344]

question is whether 100 rows/ second is such a poor performance at all.
How big is your session log? I have seen serious decrease in performance when verbose logging was activated in mappings (you know forgotten to reset to normal after testing). This will cause the server to use CPU time to write the logging, slowing everything down.

How did your DBA divide the resources for applications?

Consider experimenting joining the dates table instead of using it as a lookup...

T. Blom
Information analyst
tbl@shimano-eu.com