Daniel John Debrunner <[EMAIL PROTECTED]> writes: > Dag H. Wanvik wrote: > >> Hi, >> >> >>>>>>>"Knut" == Knut Anders Hatlen <[EMAIL PROTECTED]> wrote: [...] >> Knut> My initial thought is that we should try to: >> Knut> >> Knut> 1) Pre-fetch rows in PreparedStatement.executeQuery(), saving one >> Knut> round trip per select statement. >> Knut> >> Knut> 2) Delay ResultSet.close() and send it piggybacked on the next JDBC >> Knut> call, saving one round trip per select statement when autocommit >> Knut> is off. >> >> If the next JDBC communication won't take place for a while, this >> second optimization can significantly delay the release of result set >> locks, so I would be hesitant to recommend this optimization (as a >> default behavior, anyway), since other transactions might block, >> without the user realizing the cause of it. > > Can we take advantage of any knowledge of how the embedded engine is > closing ResultSets automatically, at least for forward only result sets? > From the 50,000 foot level it seems to me that for a forward only result > set that has been exhausted (rs.next() returned false) the close on the > client should not need to send any communication to the server
Yes, I think we could close exhausted forward only result sets on the server. The DRDA protocol actually has an optional parameter for this, OPNQRY.qryclsimp, which "controls whether the target server implicitly closes a non-scrollable query upon end of data (SQLSTATE 02000) in association with the cursor type." It is not used by the client driver, and the server default is NO. If we enable this in the client, we could avoid a round trip in many cases and we wouldn't get the locking issues Dag described. OPNQRY (which is the command sent to the server in executeQuery()) also has another parameter, qryrowset, which the DRDA spec says "specifies whether a rowset of rows is to be returned with the command. This is only honored for non-dynamic scrollable cursors (...) and for non-scrollable cursors conforming to the limited block query protocol." If I have understood this correctly, it means that we can open a result set and request data in the same DRDA message, which would save one round trip on the first call to next(). Unless someone says it's a very bad idea, I will file two separate JIRA issues saying we should enable these features. -- Knut Anders
