db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-822) Client driver: Pre-fetch data on executeQuery()
Date Wed, 28 Jun 2006 15:02:30 GMT
    [ http://issues.apache.org/jira/browse/DERBY-822?page=comments#action_12418258 ] 

Knut Anders Hatlen commented on DERBY-822:

Kathey Marsden wrote:

>  1) Queries may fail earlier. Instead of failing on the ResultSet
>     next() call, they may now fail on execute()/executeQuery().
>     Applications need to be prepared to handle this.

This is correct. However, applications should already be prepared to
handle errors on execute()/executeQuery(), so I hope this shouldn't
cause too much trouble.

>  2) Prefetching may impact locking behavior. Locks may be acquired
>     earlier or may be acquired where they never were before,
>     promoted to table locks etc.

This is partly correct.

Locks for the first chunk of pre-fetched rows are aquired on
execute/executeQuery instead of on the first call to next().

The locks "may be acquired where they never were before", but that is
only the case when there is no call to next() after the
execute. Although a lot of our tests do this (in order to test that
the statement compiles or to print the query execution plan), I fail
to see why an application would execute a SELECT statement but not
read any of the data.

I do not believe locks will be promoted to table locks because of the
pre-fetching. The number of rows pre-fetched when invoking
executeQuery() is exactly the same as the number of rows that would be
pre-fetched on the first call to next() with an old server.

>  3) This introduces a new difference I think between embedded and
>     network behavior


> Does this sound right? If so I can take a stab and a release
> note.

Thanks, that would be great!

> Is there a workaround if prefetching causes users any trouble
> on upgrade?

The best work-around is: Do not execute SELECT statements just for
fun! :) Seriously, if a select statement is executed, but the results
are never read, it should probably not have been executed in the first
place. Also, it might help to move the executeQuery() closer to the
first call to next() (that is, don't do too much work between
executeQuery() and next()).

There is no simple way to disable the pre-fetching. The only ways I
know of are using an updateable result set or including a LOB column
in the query.

> Client driver: Pre-fetch data on executeQuery()
> -----------------------------------------------
>          Key: DERBY-822
>          URL: http://issues.apache.org/jira/browse/DERBY-822
>      Project: Derby
>         Type: Improvement

>   Components: Network Server, Performance
>     Versions:
>     Reporter: Knut Anders Hatlen
>     Assignee: Knut Anders Hatlen
>     Priority: Minor
>      Fix For:
>  Attachments: DERBY-822-v1.diff, DERBY-822-v1.stat, DERBY-822-v2.diff, DERBY-822-v2.stat,
DERBY-822-v3.diff, DERBY-822-v3.stat
> Currently, the client driver does not pre-fetch data when
> executeQuery() is called, but it does on the first call to
> ResultSet.next(). Pre-fetching data on executeQuery() would reduce
> network traffic and improve performance.
> The DRDA protocol supports this. From the description of OPNQRY (open
> query):
>   The qryrowset parameter specifies whether a rowset of rows is to be
>   returned with the command.  This is only honored for non-dynamic
>   scrollable cursors (QRYATTSNS not equal to QRYSNSDYN) and for
>   non-scrollable cursors conforming to the limited block query
>   protocol.  The target server fetches no more than the requested
>   number of rows. It may fetch fewer rows if it is restricted by extra
>   query block limits, or if a fetch operation results in a negative
>   SQLSTATE or an SQLSTATE of 02000.

This message is automatically generated by JIRA.
If you think it was sent incorrectly contact one of the administrators:
For more information on JIRA, see:

View raw message