[jira] Updated: (DERBY-3192) Cache session data in the client driver

["Dyre Tjeldvoll (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-3192?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dyre Tjeldvoll updated DERBY-3192:
----------------------------------

    Attachment: derby-3192-mark2.v1.diff

I'm attaching a completely reworked
patch (derby-3192-mark2.v1.diff) based on the new idea of adding
product-specific code points. All tests pass. 

I plan to update the writeup on the Wiki as well, but here is a
short summary of the new patch:

* Adds a new method (getCurrentSchemaName()) to EmbedConnection
  and EngineConnection that the NetworkServer can use to find out
  what the current schema is.

* Adds functionality to the Database class that keeps track of
  the latest isolation level and schema which have been
  sent to the client, and whether the values have changed
  since the last piggy-backing.

* Adds three product-specific code points, PBSD, PBSD_ISO and
  PBSD_SCHEMA as well as a method (DRDAConnThread.writePBSD)
  which adds a PBSD containing one or both of PBSD_ISO and PBSD_SCHEMA
  to outgoing messages when piggy-backing is needed.

* The reply to EXCSQLIMM, EXSQLSTT, OPNQRY and CNTQRY will
  include session data when needed. There is no additional data
  being transmitted when session attributes are unchanged.

* The corresponding parse methods for the replies on the client
  have been augmented to handle the new code points and invoke a
  callback on the client which updates (syncs) the cached session
  data.

* am.Connection on the client side is modified to use its cached
  copy of the session attributes, unless they have a
  special "unknown" value which will force a fetch from the
  server (as before).

* Joining/leaving an XA transaction is handled by dropping the
  cached values (by assigning the special "unknown" value).

* A new variable and access method for the schema name has been added
  to am.Connection. This is primarily intended for use by the new
  StatementCache

> Cache session data in the client driver
> ---------------------------------------
>
>                 Key: DERBY-3192
>                 URL: https://issues.apache.org/jira/browse/DERBY-3192
>             Project: Derby
>          Issue Type: Improvement
>          Components: JDBC, Network Client, Network Server, Performance, SQL
>    Affects Versions: 10.3.1.4
>            Reporter: Dyre Tjeldvoll
>            Assignee: Dyre Tjeldvoll
>         Attachments: derby-3192-mark2.v1.diff, derby-3192-test.fup1.diff, derby-3192-test.fup2.diff, derby-3192-test.v1.diff, derby-3192-test.v1.stat, derby-3192.prelim1.diff
>
>
> The reason for doing this is to avoid a rather
> substantial performance hit observed when the client driver is used
> together with an appserver that uses connection pooling. There are two
> problems:
> 1) The connection pool will compare the isolation level it has
> stored for the connection with the value returned from
> Connection.getTransactionIsolation() each and every time someone
> requests a new connection from the pool.
> 2) The users of the connection pool (ab)use it to avoid having to keep
> track of their current connection. So each time a query needs to be
> executed a call to the connection pool's getConnection() method is
> made. Getting a connection from the connection pool like this also
> means that a new PreparedStatement must be prepared each time.
> The net result is that each query results in the following sequence:
> getConnection()
> getTransactionIsolation() --> roundtrip + lookup in server's statement cache
> prepareStatment()         --> roundtrip + lookup in server's statement cache
> executeQuery()            --> roundtrip
> Arguably this is a "user error" but when suggesting this I'm kindly
> informed that this works "just fine" with other datbases (such as
> PostgreSQL and ORACLE). 
> The reason why it works is that these databases do statement caching
> in the driver. I've tried to implement a very (too) simple statement
> cache in Derby's client driver and to re-enable caching of the
> isolation level (see
> https://issues.apache.org/jira/browse/DERBY-1148). With these changes
> I observe a marked performance improvement when running with appserver
> load. 
> A proper statment cache cannot be implemented without knowing what the
> current schema is. If the current schema has changed since the
> statement was prepared, it is no longer valid and must be evicted from
> the cache.
> The problem with caching both the isolation level and the current schema in
> the driver is that both can change on the server without the client
> detecting it (through SQL and XA and possibly stored procedures).
> I think this problem can be overcome if we piggy-back the information we would 
> like to cache on messages going back to the client. This can be done by
> utilizing the EXCSQLSET DRDA command. According to the DRDA spec (v4, volume 3, 
> page 359-360) it is possible to add one or more SQLSTT objects after SQLCARD in the reply,
> I think it would be possible to cache additional session information when this becomes relevant.  It
> would also be possible to use EXCSQLSET to batch session state changes
> going from the client to the server.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.