Thread: About Connections and PreparedStatements

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? Or it doesn't matter if I close the connection, I'm using PooledConnection provided by my container (Orion), 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? I mean, is not closing a PreparedStatement a way to increase performance?

Well, thanks everyone.

Jeziel.

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

If you need to execute the same query many times, then keep the
preparedStatement open and just keep assigning parameters and
(re)executing it.

To get the best performance out of PostgreSQL, you'll need to be running
a relatively recent PostgreSQL version (7.4 or later, I think) along
with a newer JDBC driver, and you'll want to set the prepareThreshold to
something low, like 1 or 2.  See docs here:
http://jdbc.postgresql.org/documentation/80/server-prepare.html

-- Mark

On Wed, 2005-10-12 at 12:10 -0700, 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? Or it doesn't matter if I close the connection, I'm
> using PooledConnection provided by my container (Orion), 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? I mean, is not closing a PreparedStatement a way to increase
> performance?
>
> Well, thanks everyone.
>
> Jeziel.