Skip to Content
Author's profile photo Former Member

Improved SQL and MDO query handling for MII transactions

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.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Soham Shah
      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.

      Author's profile photo Aravinth Anandhan
      Aravinth Anandhan

      Thanks for sharing

      Author's profile photo Former Member
      Former Member

      Hi, could you give an example of what we have to assign to the source of this actionblock?

      Author's profile photo Nicolas Joneitis
      Nicolas Joneitis



      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


      Author's profile photo Former Member
      Former Member

      Thanks for the information, I didn’t know about the “ResultSetMode” part. Could you explain what that mode does? I can’t find it in documentation and the tracer actionblock can’t show the resultset output.