Skip to Content

I wanted to take some time to highlight a recently released note that can drastically improve performance and resource utilization for commonly found scenarios in MII implementations. It requires some work by MII developers to implement, but it can cut CPU/memory utilization in half and find a large decrease in processing time. In our testing, it was shown that execution times are reduced by half, memory utilization is reduced by ~66%, and CPU % is reduced by ~30%.

The applicable scenario is that you have a SQL or MDO query action that returns an XML document for the Results property, and this property is then iterated over by a Repeater action, with the repeater executing the xpath query /Rowsets/Rowset/Row and iterating over the results. The released note now provides a new way to handle this scenario. You can pass the ResultSetMode property to the query action with a value of false, and this returns a reference to the JDBC ResultSet object to the transaction engine. This reference can then be passed to a ResultSetIterator action and iterated over directly. The iterator then makes the current row available as the property CurrentItem, which is a map type. This implementation skips the ResultSet->XML->XPath query->iteration and allows MII developers to iterate over the result set directly, which is where the performance improvement comes from.

To use this, you will need to enclose the query in a SQL transaction to guarantee that the JDBC connection and statement objects are kept open during the iteration of the query. It is not suggested to be used in combination with query caching. And it will not work with queries that return multiple result sets.

This requires some work to implement but worth implementing for scenarios that are frequently executed or that handle large result sets. You can find more details in note 2583930.

To report this post you need to login first.

4 Comments

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

  1. Soham Shah

    Hi Michael. Thanks for the information. Really very helpful. Will it be possible for you to put some screenshots of the approach that you are talking about. Screenshots will give us better understanding and more clarity.

    (2) 
  2. Nicolas Joneitis

    Hello,

     

    I’ve tested a query with about 10000 datasets and it was about 8x faster.

    Here is what I have done, so you can try on your own.

    start the Query with the SQL Transaction Begin action

    • put your query in the next action block and put a reference to the SQL Transaction Begin
    • also set the property “ResultSetMode” to “true”

    • set the source for the ResultSetIterator to the ResultSet of the Query
    • End the Query with a SQL Transaction End

    The performance statistics was the following with a resultset

    To compare the performance statistics I have made the same query without a Resultset

    The performance statistics was the following without a resultset

     

    (0) 

Leave a Reply