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.