We recently spotted something interesting and would like to see if someone had similar experience before, or any reference we could turn to.
When using ResultSetType = TYPE_SCROLL_INSENSITIVE vs. TYPE_FORWARD_ONLY (link more explanation on those two ResultSet attributes, used in prepareStatement(String sql, int resultSetType, int resultSetConcurrency) link), the same query picks up different access plans, leading to drastically different query time, 17 sec for TYPE_FORWARD_ONLY vs. 500ms for TYPE_SCROLL_INSENSITIVE, in our production-size db. All other jdbc attributes are kept the same. Query time difference becomes more obvious when we have larger filter range inside the query.
The access plan also looks quite different, at least visually, but i couldn't find much references to some of the components in the path (bunch of SYSIBM.TFSCROLL_LOOKUP, SCROLLDATATEMP, SCROLLDATATEMP, TFSCROLL_CORR). Also the STMTIDs are the same in two different access plan.
We could, but haven't tested the differences for more queries in our application. Question is, without digging much into db2 internals, nor exhaustively profiling all our queries response to different ResultSetType, could we get some guidelines on how to properly use ResultSetType?
Thanks for sharing your insights!
Rui Chen[Organization Members] @ Nov 16, 2017 - 02:35 PM (America/Eastern)