JDBC/RFC lookup - single vs multiple lookups

[tobiasberneck]

Motivation

We have several scenarios running which are collecting HR master data from the central SAP HR system and providing this data to third party tools. One problem arising from these scenarios is the use of JDBC/RFC lookups. If such an interface is collecting the data for a big number of persons, the lookups have to be executed for each single person (e.g. you want to get from SAP HR the mail address from infortype 0105: inbound: personal ID -- JDBC-lookup in PA0105 --> outbound: mail address) and this can cause a very long runtime.

If the runtime is critical, an alternative approach has to be found.

Single Lookup instead of multiple calls

One solution can be to make one JDBC/RFC lookup call in the mapping of the target root element and put the results into a temporary HashMap. A prerequestite for this is, that all necessary values can be fetched in a single or at least small number of calls and the data volume of the result is not too big.

Implementation steps

• Create a function library
• In the "Functions and Java Areas" section "Attributes and Methods" add a line like the following (adjusted according to your needs):
  • HashMap<String,String>jdbchash_YourVariable = new HashMap<String,String>();
• In the mapping of the target root element add one or several JDBC/RFC lookup(s) which is collecting all necessary result values.
• Forward these result values to an UDF which is writing the values into the earlier defined HasMap:
  • jdbchash_YourVariable.put(PERNR[i],Mail_Address[i]);
• In the original target field mapping add an UDF which just reading the result values from the HashMap

Result

This is only one possible solution to improve the performance fo JDBC/RFC lookups.

In specific scenarios the performance gain can be impressive by this very fast and easy to implement solution.