Skip to Content

Optimizing Lookup’s in XI


   

This weblog explains how to optimize database lookup’s when dealing with
large messages.

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:


        image
     


    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
    Sivas weblog
    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:


        image
     

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:


        image
     
        image
     

The code in our “lookup” function should look as simple as:


        image
      </td></tr>
    </table>

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply