Re: statement caching proof of concept

Dave Cramer wrote:
> Other drivers use statement caching namely Oracle.

Are there any special things you have to do or care about to use cached
statements there? My experience with the oracle driver is limited.

>>> 1) What to do if there are multiple concurrent requests per
>>> connection for the same statement?
>>> 1) we could just allow it
>>> 2) we could return a non-cacheable preparedstatement
>>> 3) throw an exception
>> Should do whatever a normal prepared statement would do in this
>> situation, imho. That is, if i have code that uses normal prepared
>> statements, and is updated to use these cached statements, it
>> should not
>> fail in any new ways. Or either the different behaviour it must be
>> very
>> well documented.
>
> I'd agree the driver should not fail.

The current situation allows to use a prepared statement from several
threads at once, or more than once before reading the result in a single
thread. There is no way to get all the results back reliably in this
case. Since the JDBC specifications don't demand anything more (afaik),
solution 1 seems ok.

A slightly more advanced solution might be to create a map of lists of
prepared statements, instead of a map of prepared statements. If i have
a prepared statement "SELECT 1" and this is in high demand by hundreds
of threads (silly example), it would be possible to have more than one
prepared statement with the same sql. That would be more like solution
2, with better performance in concurrent applications.

I like Mark Lewis idea of caching the handles, but i have no real
knowledge about the postgres api, the overhead of creating a new
prepared statement object (could easily offset the saved memory in a
"heavy duty" application) and how well this would work; or how this
would influence the specific problem of multiple concurrent requests.
Certainly sounds a bit more difficult to implement.

How does it combine with methods like get*MetaData()?

And what happens with methods like setQueryTimeout()? They change the
behaviour of the cached statement, and don't get reset between use.
Especially interesting in the case of automatic caching of prepared
statements, where the user expects a new, "clean" statement. How does
the Oracle driver handle this, for example?

Ok, just looked at the source again. The method clearWarnings() is
called before returning the statement. There could be an extended
clearCachedStatement() method, or something like this to reset
everything neccessary.

Till