2 Replies Latest reply on Jan 2, 2013 1:49 AM by vladodias

    Transformation "lookup-table" & "query-database": functioning & performance

    rameshchandra
      Hi

      We are using database functions to look up from a table in a transformation activity using BPEL spec 1.1 in SOA 11g version. The transformation will be parsing an XML with multiple nodes (1000 to 15000 count).
      The look up function is using data source to connect to the database server, and there will be for-each construct to loop through the multiple nodes, we are observing performance issue in the transformation logic.

      We want to understand the behaviour of this look up function:
      *1. Does it fetch the connection from the pool and perform the look up operation and then release the connection back into the pool every pass of the loop.*
      *2. Is there a way we can optimize the function to cache the connection use it for the entire loop and once transformation is completed, only then connection is released.*

      we have tested the code using both with and without the lookup function as we observed a significant change the duration.
      Without the lookup the transformation takes around 20-30 secs but when we add the look up it takes around 6 mins for 1059 nodes in the source XML.

      It would be great if anyone can advice on above ...!

      Thanks for the help!

      Regards,
      Achal

      Edited by: rameshchandra on 28 Dec, 2012 7:31 AM