[jira] Updated: (DERBY-822) Client driver: Pre-fetch data on executeQuery()

["Knut Anders Hatlen (JIRA)" <de...@db.apache.org>]

     [ http://issues.apache.org/jira/browse/DERBY-822?page=all ]

Knut Anders Hatlen updated DERBY-822:
-------------------------------------

    Attachment: DERBY-822-v1.diff
                DERBY-822-v1.stat

I have attached a patch (DERBY-822-v1.diff) which implements
prefetching on executeQuery(). Derbyall (including JCC tests) passed
on Solaris 10 x86/Sun JVM 1.4.2 (a couple of failures, but they are
also seen in Ole's nightly regression tests).

I hope the size of the patch doesn't scare off potential
reviewers. The actual code changes are very small. 95% of the patch is
just updating the master files for the Wisconsin test.

Below is a description of the changes.

Thanks,
Knut Anders



MOTIVATION

Derby (in client/server mode) needs more round trips to the server
than other databases to execute select operations. While other
database drivers prefetch data when calling
PreparedStatement.executeQuery(), Derby does not fetch data until
ResultSet.next() is called. If data is sent in the reply to
PreparedStatement.executeQuery(), one round trip is saved per select
operation and the performance will increase.

PROPOSED SOLUTION

The DRDA protocol lists some command sequences that require query data
(QRYDTA) to be returned together with the reply to an open query
(OPNQRY) command. None of these command sequences are currently in use
by the Derby client driver or JCC, and the network server does not
support them.

Additionally, the DRDA protocol specifies command sequences for which
the server can choose whether it includes QRYDTA in the reply or
not. These sequences are used by the client driver, but the network
server chooses not to send QRYDTA.

What I propose, is that we change the network server's behaviour in
the optional case, so that it includes QRYDTA when it replies to an
OPNQRY command. I do not propose to implement support for the required
case, since the clients do not currently send those commands to the
server.

IMPLEMENTATION

The client driver (and JCC) already supports OPNQRYRM messages with
QRYDTA attached, so only the network server needed to be modified.

In the section of DRDAConnThread.processCommands() that processed
OPNQRY commands, this comment was found:

  // We could send QRYDTA here if there's no LOB data
  // in the result set, and if we are using LMTBLKPRC, as
  // allowed by drda spec, as an option.

At that spot I inserted code for writing QRYDTA similar to what
processCommands() does for CNTQRY:

  if (stmt.getQryprctyp() == CodePoint.LMTBLKPRC) {
      // The DRDA spec allows us to send
      // QRYDTA here if there are no LOB
      // columns.
      DRDAResultSet drdars =
          stmt.getCurrentDrdaResultSet();
      try {
          if (drdars != null &&
              !drdars.hasLobColumns()) {
              writeQRYDTA(stmt, true);
          }
      } catch (SQLException sqle) {
          cleanUpAndCloseStatement(stmt, sqle,
                                   writerMark);
      }
  }

All other changes to the code were just minor adjustments to make
writeQRYDTA() and writeFDODTA() work with OPNQRY (they contained some
code which assumed that they were responding to a CNTQRY).

REGRESSION TESTING

No explicit regression tests were added, since the amount of
regression test failures caused by this change was so huge that a
regression back to the old behaviour will not likely go unnoticed. For
instance, the protocol test (derbynet/testProtocol.java) had to be
changed to expect QRYDTA when it sends OPNQRY to the server, and it
will fail if the network server changes are reverted.

Below is a list of changes that had to be made to the regression
tests:

* jdbcapi/parameterMetaDataJdbc30.java *

This test tried to compile an expression containing "WHERE x LIKE ?
ESCAPE ?", set the escape sequence to an empty string and execute the
query, but it did not call ResultSet.next() to fetch data. An empty
string is not a valid escape sequence, so when prefetching was enabled
the test failed. Failure was fixed by calling ResultSet.next() to
ensure that the invalid escape sequence would be caught in all
frameworks, and a message will be printed if we do not get the
expected exception.

* jdbcapi/setTransactionIsolation.java *

Some test cases where a statement was executed without calling
ResultSet.next() failed because the statement execution plan text
differed. The difference was related to the prefetching which would
cause a larger number of rows and pages seen. The execution plan did
not change.

Additionally, some two test cases failed because of a lock
timeout. This was expected and caused by the prefetching. (You do not
get a lock timeout until you actually try to fetch the data.)

Fixed by updating master files for DerbyNet and DerbyNetClient.

* jdbcapi/resultset.java *

This test failed because some column headers were not printed. The
failure was caused by the prefetching. Exceptions that were expected
when calling ResultSet.next() were thrown when calling
Statement.executeQuery() instead, leading to slightly different
output. Updated master files for DerbyNet and DerbyNetClient.

* derbynet/testProtocol.java *

Test failed because it just expected OPNQRYRM and QRYDSC in response
to OPNQRY with LMTBLKPRC and no LOBs. Had to add QRYDTA to the
expected response in values1.inc.

* lang/scrollCursors1.sql *

One statement execution plan text changed because of prefetching (more
rows seen). Updated master files for DerbyNet and DerbyNetClient.

* lang/supersimple.sql *

In queries that were expected to fail, column headers were not printed
because prefetching caused the query to fail earlier. Updated master
files for DerbyNet and DerbyNetClient.

* lang/wisconsin.java *

Many statement execution plan texts had changed. The plans still were
the same, but the number of rows/pages seen increased since
prefetching caused data to be fetched even though the test closed the
cursors without reading data. Updated master files for DerbyNet and
DerbyNetClient.

* lang/forupdate.sql *

Two occurrences of this error message:

  ERROR 42X23: Cursor SQL_CURLH000C1 is not updatable.

were replaced with this message:

  ERROR 42X30: Cursor 'SQL_CURLH000C1' not found. Verify that
  autocommit is OFF.

The failure was caused by prefetching causing all data from the
forward-only/read-only cursor to be fetched, combined with implicit
closing of the cursor on the server side. The failure is expected.

Fixed by updating master file for DerbyNetClient.

> 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 Client, Network Server, Performance
>     Versions: 10.2.0.0
>     Reporter: Knut Anders Hatlen
>     Assignee: Knut Anders Hatlen
>     Priority: Minor
>      Fix For: 10.2.0.0
>  Attachments: DERBY-822-v1.diff, DERBY-822-v1.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:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira