Optimizing Lookup’s in XI
This weblog explains how to optimize database lookup’s when dealing with
Lots of integration scenarios require lookup’s during a mapping process.
When dealing with large messages, this might become a bottleneck because
each field that uses a lookup needs also to run a query.
In order to improve performance in these situations, I would like to
propose the following solution:
- Make just one query for all the information needed
- Assign the information individually for each mapping
First, we’ll initialize the connection in the following way:
Depending on the JDBC you choose you might need to add some JAR files under
Imported Archives. To get around this problem it might be a good idea to
access the database through a communication channel. Refer to
to get a deeper explanation.
Next we’ll have to run the complete query before assigning the
mapped values to the fields. By defining a function “setlookup” and
linking it to the parent node of the elements which we need to map
using a lookup, we can take for granted that the function
is going to be called before the mapping of those fields. The mapping
is shown bellow:
The function “setlookup” will run the query and store it
in the HashMap that we have previously declared. The general idea is to
prepare the query string using the child nodes that need to be mapped with
the lookup function, execute the query and store the results in an efficient
structure (HashMap) to query those values individually later.
The function should look like:
The code in our “lookup” function should look as simple as: