Olav Sandstå <[EMAIL PROTECTED]> writes:
> On 1/9/06, Kathey Marsden <[EMAIL PROTECTED]> wrote:
>> Are the additional round trips just on the first execution of a prepared
>> statement?
>
> No, it is on every execute of the query/transaction. The numbers are
> average of several thousand transactions, thus the preparing of the
> initial statement should not influence much :-)
>
>> One difference between our client and the DB2 Universal JDBC Driver is
>> that JCC "defers prepares"
>> which means that it waits to prepare the statement until it is first
>> executed, so can save a single round trip on the first statement
>> execution. This optimization was the source of many bugs, complexity in
>> the code, and differences in embedded and client behaviour and not
>> really that helpful in the common scenario where a statement is prepared
>> once and executed many times.
>
> Knut Anders actually commented on this when he studied the messages
> that different drives send and he could not see that the JCC driver
> sent the prepare operation in a separate message. We both agreed that
> this likely was an optimization, but did not think that for an
> application using prepared statements "correctly" this would have much
> impact (but I have seen automagically generated code using prepared
> statements, one execute of it and then a new prepare of the same
> statements.... :-( ).
I have monitored the network activity while using a debugger to step
through the top-level JDBC calls of a single-record select transaction
with different JDBC drivers. The table below shows which calls that
are generating activity on the network.
| Derby | DB2 univ | Postgres | MySQL
---------------------------------------------------------------------
DriverManager.getConnection | X | X | X | X
Connection.setAutoCommit(false) | | | | X
Connection.prepareStatement | X | | | X
---------------------------------------------------------------------
PreparedStatement.executeQuery | X | X | X | X
ResultSet.next | X | X | |
ResultSet.close | X | X | |
Connection.commit | X | X | X | X
The first three methods are not candidates for optimization, in my
opinion, since a well-written application won't call them
frequently. But I think we should do something with the last four
methods.
My initial thought is that we should try to:
1) Pre-fetch rows in PreparedStatement.executeQuery(), saving one
round trip per select statement.
2) Delay ResultSet.close() and send it piggybacked on the next JDBC
call, saving one round trip per select statement when autocommit
is off.
Should we file these as JIRA enhancement issues?
--
Knut Anders
