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 Mon, 10 Apr 2006 20:46:59 GMT
    [ http://issues.apache.org/jira/browse/DERBY-822?page=comments#action_12373927 ] 

Knut Anders Hatlen commented on DERBY-822:

Thank you for looking at the patch, Bryan! Your comments are valuable,
as always. I have tried to answer your questions below.

> Bryan Pendleton commented on DERBY-822:
> ---------------------------------------

> 1) I don't really understand why we have to pass the "opnqry"
> argument down to writeQRYDTA and writeFDODTA. I read your comments
> and they make sense at a "detail" level, but I think I'm missing the
> bigger picture. It seems to me like writeQRYDTA and writeFDODTA
> should not have to care whether they were called in response to a
> CNTQRY or in response to a OPNQRY, and when I stare at the detailed
> changes to writeFDODTA I'm puzzled:
>   - when called during processing of an OPNQRY, why would it be
>     wrong to call positionCursor()

positionCursor() uses the value of QRYSCRORN, which is not part of the
OPNQRY command. After an OPNQRY, DRDAResultSet.qryscrorn is
unintialized (zero), and it is not accepted by positionCursor().

>   - when called during processing of an OPNQRY, doesn't
>     stmt.getQryrtndta() return false?

Yes, it does. And that is the problem. I realize now that my comment
wasn't quite clear. The comment said:

  	If we were asked not to return data (QRYRTNDTA) ...

It should have said:

    If we were asked not to return data (QRYRTNDTA false) ...

The original code has this assignment:

    boolean noRetrieveRS = (rs != null && !stmt.getQryrtndta());

Since qryrtndta is false when processing OPNQRY, noRetrieveRS is true,
and no results are returned. Therefore, the test for OPNQRY was needed
to make it work.

> I think what I'm trying to suggest is that it seems like it would be
> cleaner to make positionCursor() and stmt.getQryrtndta() "do the
> right thing" when called during OPNQRY, rather than passing the flag
> through, since that seems like it would make the code overall more
> robust and clear.

I agree. I think the best way is to set the values of
DRDAResultSet.qryrtndta and DRDAResultSet.qryscrorn to something
meaningful on OPNQRY. That should be safe, since the values are
overwritten when a CNTQRY is received.

> 2) I don't understand what's going on with the change to
> DRDAStatement.java. It seems like there are various configuration
> settings (qryrowset, blksize, maxblkext, etc.) which are being
> tracked both in the statement and in the result set, and you're
> changing things so that when these options are passed in an OPNQRY
> call, they will now affect not only the particular result set for
> that query, but also the overall statement?
> I think I'm just puzzled by this change. It doesn't seem to be
> directly related to the other work you're doing, except that it
> involves OPNQRY processing, and I'm concerned that I don't really
> understand the implications of setting these various variables on
> the statement object as opposed to on the result set object.
> Can you expand upon the reasoning behind the DRDAStatement change,
> and also help me understand why we have these variables in both the
> statement and the result set?

Right, I didn't explain that change very well...

First, I'll explain why these changes were needed:

The current implementation does not set default block size (and some
other options) for the DRDAStatement on an OPNQRY command. Actually,
the only command that sets these options is EXCSQLSTT. For the old
behaviour with no pre-fetching, this is okay since the block size for
the result set is set on each CNTQRY command. However, it is not okay
with pre-fetching. What happens is

  1) DRDAConnThread.processCommands() calls parseOPNQRY(), which sets
     the block size of the current DRDAResultSet to ~32K.

  2) DRDAConnThread.processCommands() calls DRDAStatement.execute(),
     which (indirectly) calls setRsDefaultOptions(). Since the default
     block size of the DRDAStatement is not set, the block size of the
     current DRDAResultSet will be reset to zero.

  3) When DRDAConnThread.processCommands() tries to pre-fetch rows it
     only fetches one row, since it thinks that the block is full.

If we set the default block size for the statement on OPNQRY, the
result set won't get its block size reset when the statement is
executed, and we can fill the entire block with pre-fetched rows.

Only the block size was necessary to get the pre-fetching working, but
I felt it was cleaner to update all the default values rather than
just one. (Actually, I see that I forgot one variable. Will fix that

Now, here's why I think this change is correct:

  a) There is never more than one open result set per statement, so
     changing the defaults for the statement does not affect any other
     open result set.

  b) The default values are only used in setRsDefaultOptions(). All
     code paths that end up calling setRsDefaultOptions() go through
     DRDAStatement.execute(). DRDAStatement.execute() is only invoked
     from within parseEXCSQLSTT() and after parseOPNQRY(). Since
     parseEXCSQLSTT() already updates the defaults, it won't see any
     changes made by OPNQRY.

You also asked why the variables were needed in both DRDAStatement and
DRDAResultSet. I don't see any good reason for having two copies of
these variables. I think the code would be cleaner if we got rid of
the ones in DRDAStatement. But that's another patch... ;)

> 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
> 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