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.
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.
Thanks for sharing
Hi, could you give an example of what we have to assign to the source of this actionblock?
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
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
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.