[jira] Closed: (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 closed DERBY-3192.
---------------------------------


> 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-fup.v1.diff, derby-3192-mark2.v1.diff, derby-3192-mark2.v2.diff, derby-3192-mark2.v3.diff, derby-3192-mark2.v4.diff, derby-3192-mark2.v5.diff, derby-3192-mark2.v6.diff, derby-3192-mark2.v7.diff, derby-3192-mark2.v8.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.