2 Replies Latest reply: Jan 1, 2013 7:49 PM by vladodias RSS

    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
        • 1. Re: Transformation "lookup-table" & "query-database": functioning & performance
          Arik
          Achal,

          Using the "lookup-table" & "query-database" from the transformation will enforce you to fetch, for each loop the connection.
          Do you have to call the DB with different parameters (from the source record) for each loop?
          If the parameters(for the DB call) from the source record are the same for each loop, you can just use a parameter in the target variable (I can write an example if it will help you).
          Another option is to send the source records to a DB procedure(as a table of records), and perform the mapping there (Sorry I don't know you entire requirement) .


          Arik
          • 2. Re: Transformation "lookup-table" & "query-database": functioning & performance
            vladodias
            Hi,

            I believe the problem is not the connection but the query itself, you can mitigate the performance issues by creating and index on the table for example...

            Other option would be to bring the entire table to memory using a DbAdapter call and passing the result as a parameter to your xslt template...

            Hope this helps...

            Cheers,
            Vlad