Re: About Connections and PreparedStatements

Hector Rosas wrote:
> Hello, I have a doubt with connections and PreparedStatemens. If I open
> a connection to Postgres and then request a prepared statement, this
> will be compiled and reused as long as I don't close the connection
> right?

As long as you reuse the same PreparedStatement object, yes.

> Or it doesn't matter if I close the connection

If you close a Connection, all PreparedStatements created by that
connection are implicitly closed.

>, I'm using
> PooledConnection provided by my container (Orion),

I can't speak for that PooledConnection implementation.. it may do extra
statement caching, I don't know.

> I want to know if
> there's an advantage to keep a connections open, in order to reutilize
> PreparedStatement. Also, I'm closing PreparedStatement after every
> query, PreparedStatement need to be closed after every query right?

You do not need to close the PreparedStatement after each query.

> I
> mean, is not closing a PreparedStatement a way to increase performance?

If you close a PreparedStatement, the postgresql JDBC driver will free
any corresponding server-side resources such as server-side prepared
statements.

So if you want to avoid the parse/plan cost when reexecuting the same
query many times, you should reuse the same PreparedStatement object
without closing it between executions.

-O