Cache session data in the client driver?

[Dy...@Sun.COM]

I'm wondering if it would be possible to cache session information in
the client driver. 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 see two ways in which one can attack this problem: 

1) Let the client "listen" to all statements being executed on the
   connection and invalidate its cache whenever anything that might
   invalidate it comes along.

2) Piggy-back the info that we would like to cache on the messages
   going back to the client.

For the second option I was wondering if it would be possible to use
the EXCSQLSET trick that Øyvind used for setQueryTimeout, in
reverse. 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,
but as far as I can tell, there doesn't seem to be any methods for
doing so on the server, nor do I see any methods for extracting the
values when parsing the reply on the client. But they would not be too
difficult to add, I think.

If this method can be made to work 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.

Any ideas/suggestions/opinions would be much appreciated. 

Thanks.

-- 
dt

Re: Cache session data in the client driver?

[Dy...@Sun.COM]

Dag.Wanvik@Sun.COM (Dag H. Wanvik) writes:

> Dyre.Tjeldvoll@Sun.COM writes:
>
>> For the second option I was wondering if it would be possible to use
>> the EXCSQLSET trick that Øyvind used for setQueryTimeout, in
>> reverse. 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,
>> but as far as I can tell, there doesn't seem to be any methods for
>> doing so on the server, nor do I see any methods for extracting the
>> values when parsing the reply on the client. But they would not be too
>> difficult to add, I think.
>>
>> If this method can be made to work 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.
>
> I read the standard the same way; the RTNSETSTT docs is pretty clear:
>
> "Return SET statement (RTNSETSTT) controls whether the target server
> must return one or more SQLSTT reply data objects, each containing an
> SQL SET statement for a special register whose setting has been
> modified on the current connection, upon successful processing of the
> command, if any special register has had its setting modified during
> execution of the command."
>
> I guess "special register whose setting has been modified on the
> current connection" would cover session state variables.

Thanks Dag. It is always nice when someone confirms your
interpretation of the DRDA spec (or any other spec for that
matter) :)

-- 
dt

Re: Cache session data in the client driver?

["Dag H. Wanvik" <Da...@Sun.COM>]

Dyre.Tjeldvoll@Sun.COM writes:

> For the second option I was wondering if it would be possible to use
> the EXCSQLSET trick that Øyvind used for setQueryTimeout, in
> reverse. 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,
> but as far as I can tell, there doesn't seem to be any methods for
> doing so on the server, nor do I see any methods for extracting the
> values when parsing the reply on the client. But they would not be too
> difficult to add, I think.
>
> If this method can be made to work 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.

I read the standard the same way; the RTNSETSTT docs is pretty clear:

"Return SET statement (RTNSETSTT) controls whether the target server
must return one or more SQLSTT reply data objects, each containing an
SQL SET statement for a special register whose setting has been
modified on the current connection, upon successful processing of the
command, if any special register has had its setting modified during
execution of the command."

I guess "special register whose setting has been modified on the
current connection" would cover session state variables.

+1

Dag