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.

[anand_shankar10]

Hi Tobias,

I have used another trick in past to make multiple lookups in a single call.

While selecting the source fields in the RFC/JDBC lookup node... apart from selcting the source field, select the root element of that field too which is occuring multiple times. This helps to get all the lookup values in the single call.

In the mapping add the source filed and the root filed to the JDBC/RFC lookup node and it works fine. This has helped to increase the performance.

Refer to the Blog from Sabarish: http://scn.sap.com/community/pi-and-soa-middleware/blog/2010/01/28/graphical-rfc-lookup-and-how-you-...

[tobiasberneck]

Hi,

Thanks for the hint.

I know this blog already. But as I said it depends on your individual scenario, wether my proposed solution is useful or not. For example in case of an HR masterdata scenario you have dependencies between several target fields (e.g. some target values are dependend from the org unit which has to be calculated first). Additionally you don't have to take care about the context during the lookup. Sometimes this can help a lot.

[anand_shankar10]

Thanks Tobias,

We always get to learn something at every step :smile:

Regards

Anand

[Former Member]

Nice one.

Infact we used the same trick in one of the scenario.

Thanks for sharing.

--Divyesh