Re: [GENERAL] Prepared statement performance...

Aaron Mulder wrote:
> You may be able to configure your app server database pools to
> cache PreparedStatements. Some version of JBoss and WebLogic support
> this, at any rate. The idea is that just like connections aren't really
> closed when you call close (just returned to the pool), PSs aren't really
> closed when you call close (just kept in a cache for the connection).
> This would let you take advantage of server side PSs in an app server
> environment.
> The danger is that if each connection has a high PS cache size,
> you can run into problems like "too many open cursors" on Oracle (when 50
> connections each try to cache 50 PSs or whatever).

Oracle's max open cursors is per connection. So as long as it is set
higher than the size of the statement cache you should be ok. I also
beleive that in recent versions of the oracle jdbc driver, the driver
does this statement caching automatically. It shouldn't be too
difficult to add statement caching to the postgres jdbc driver if we
thought it would be a good idea.

> I'm not sure whether
> PostgreSQL would complain or not. Does it support multiple open
> PreparedStatements per Connection? And if so, are there any backend
> limits to the total number of open server side PSs?
>

Yes it does support mulitple server side prepared statements. There
isn't any limit on the backend (other than available memory) on the number.

> Aaron
>

--Barry