Re: statement caching proof of concept

On 19-Jun-06, at 6:53 PM, till toenges wrote:

> 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.

AFAIK Postgres doesn't allow concurrent statements on the same
connection, so I don't see how using multiple threads would work ?
>
> 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.
good point, however query timeout isn't used right now .

> 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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>