Forum Stats

  • 3,816,029 Users
  • 2,259,131 Discussions
  • 7,893,366 Comments

Discussions

queryDatabase in Transformations Running Very Slowly

Hoang Tong
Hoang Tong Member Posts: 55
edited Oct 26, 2016 2:26AM in SOA Suite Discusssions

Hi Oracle Community,

I have a xslt transformation in a SOA composite where it queries an Oracle database using the queryDatabase component/adaptor. The query itself it very basic, just returns a person_id from an employee_number.

This runs fine for a small number of records but when I try to process 500 records, the transformation takes 10-15 minutes to finish.

For the data source used, I have increase the maximum capacity from 30 to 3000 connections but there were no performance improvements.

Is there anyway I can improve the performance without changing the design of the composite?

I am using Middleware 12c.

Hoang.

Tagged:

Best Answer

  • Martien van den Akker
    Martien van den Akker Member Posts: 2,776 Bronze Crown
    edited Oct 24, 2016 3:30AM Answer ✓

    Hi,

    It happens to that I gave an answer this morning on another question about the query-database function.

    You have to keep in mind that for each query-database function call a connection has to be fetched from the datasource.  I assume your xslt has an xml with the 500 persons as input and then, probably using a for-each, processes them all. That means that for all the 500 persons more or less sequentially the query-database is called. So increasing the connection-pool wouldn't (as you found out) have any effect.

    You probably generate a query like concat ('select person_id from persons where person_number = ', $person_number) , where $person_number refers to the xpath expression of the number of the particular person in the input xml.

    What this does is to feed the database with 500 different queries, that have to be checked if the database already has cached them, concluding that it has not and thus need to be parsed and executed. See also this article: https://blogs.oracle.com/sql/entry/improve_sql_query_performance_by . So the statement cache of Weblogic's datasource connection pool wouldn't help a thing either.

    What you can do?  A few options:

    • Try the cursor_sharing or Adaptive Curson Sharing options of the database.
    • Create a custom xpath that takes the person_number and executes the same query using jdbc and bindvariables.
    • create a pl/sql procedure that takes a collection of person_number and returns a collection with oracle types containing the person_numbers mapped to the person_Id's. See for examples: Darwin-IT: Object Oriented Pl/Sql . Call that procedure with the database adapter feeding it with the list of person_id's and use the output as an extra lookup parameter for your XSLT.

    Depending on your Java and/or Pl/Sql skills I'd opt for one of the latter 2. Those would give you the best performance gain. Personally, I think the third option would probably the most labour intensive to implement, but I expect the best performance gain. In the case of the second option you still have 500 calls to the database. You could solve that (partly) by creating a caching, read-ahead, version of the function.

    So, as you might notice: I'm not so fond in doing direct database lookups from an XSLT. It might feel convenient and is quick to implement. But don't expect it to become a performance effective solution. Or maybe I over-look something...

    Regards,
    Martien

Answers

  • Martien van den Akker
    Martien van den Akker Member Posts: 2,776 Bronze Crown
    edited Oct 24, 2016 3:30AM Answer ✓

    Hi,

    It happens to that I gave an answer this morning on another question about the query-database function.

    You have to keep in mind that for each query-database function call a connection has to be fetched from the datasource.  I assume your xslt has an xml with the 500 persons as input and then, probably using a for-each, processes them all. That means that for all the 500 persons more or less sequentially the query-database is called. So increasing the connection-pool wouldn't (as you found out) have any effect.

    You probably generate a query like concat ('select person_id from persons where person_number = ', $person_number) , where $person_number refers to the xpath expression of the number of the particular person in the input xml.

    What this does is to feed the database with 500 different queries, that have to be checked if the database already has cached them, concluding that it has not and thus need to be parsed and executed. See also this article: https://blogs.oracle.com/sql/entry/improve_sql_query_performance_by . So the statement cache of Weblogic's datasource connection pool wouldn't help a thing either.

    What you can do?  A few options:

    • Try the cursor_sharing or Adaptive Curson Sharing options of the database.
    • Create a custom xpath that takes the person_number and executes the same query using jdbc and bindvariables.
    • create a pl/sql procedure that takes a collection of person_number and returns a collection with oracle types containing the person_numbers mapped to the person_Id's. See for examples: Darwin-IT: Object Oriented Pl/Sql . Call that procedure with the database adapter feeding it with the list of person_id's and use the output as an extra lookup parameter for your XSLT.

    Depending on your Java and/or Pl/Sql skills I'd opt for one of the latter 2. Those would give you the best performance gain. Personally, I think the third option would probably the most labour intensive to implement, but I expect the best performance gain. In the case of the second option you still have 500 calls to the database. You could solve that (partly) by creating a caching, read-ahead, version of the function.

    So, as you might notice: I'm not so fond in doing direct database lookups from an XSLT. It might feel convenient and is quick to implement. But don't expect it to become a performance effective solution. Or maybe I over-look something...

    Regards,
    Martien

  • Hoang Tong
    Hoang Tong Member Posts: 55
    edited Oct 26, 2016 2:21AM

    Thankyou Martien for your reply and explanation. Looks like nothing can be done to the configuration of the Middleware to improve performance.

    I was thinking option 3 but since we have several interfaces with this issue, I didn't want to resort to a code change as this will be a big body of work.

  • Martien van den Akker
    Martien van den Akker Member Posts: 2,776 Bronze Crown
    edited Oct 26, 2016 2:26AM

    You're welcome,

    You can also combine the two and create a custom xpath function that calls the pl/sql function...

    But basically it's about using bindvariables.

    Regards,
    Martien

This discussion has been closed.