db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Message round-trips between Derby client and server
Date Wed, 11 Jan 2006 15:11:14 GMT
Daniel John Debrunner <djd@apache.org> writes:

> Dag H. Wanvik wrote:
>> Hi,
>>>>>>>"Knut" == Knut Anders Hatlen <Knut.Hatlen@Sun.COM> 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

View raw message