Thread: Prepared Statements and Pooling

Prepared Statements and Pooling

From
Mister Junk
Date:
I'm working on a project for school that requires a simple java server (a ServerSocket creates Socket connections which I send JSON strings over) which then communicates with a database.  Obviously I have chosen PostgreSQL.

I'm using Apache DBCP for connection pooling.  The DataSource is handed off to each thread.  Each thread then makes a Connection and then creates statements, executes them, and then closes.

I'm using prepared statements to prevent SQL injection, but I have done some reading and it seems like using Prepared statements COULD improve performance.  I understand the concept, but I do not know how to implement this.  JDBC has PreparedStatements.  Do these correspond with the Prepared Statements at the database level in postgres?

I read about the postgres prepared statements that they only work while a connection is kept open.  But in my application, each thread makes it's own connection and then closes it.  So I wouldn't be able to capitalize on the prepared statement.  However, it has occurred to me that since I am using DBCP to pool connections, the Java Connection that I create is actually a connection that is being kept open by the pool.  Does the DBCP system (poorly documented) manage the prepared statements?

I have considered opening a single connection to the server and letting all threads execute statements through it, and use prepared statements (serviced by postgresql, not JDBC or DBCP).  Or simply opening 50 connections and establishing prepared statements and then handing them off to the threads.  Is there a reason I shouldn't do this?  I read that the JDBC driver for postgres is thread safe.

What's really frustrating is that I can't find anything to read about this.  I set up DBCP, but how can I be sure my connection pool is working.  How can I tell if prepared statements are really functioning by executing a prepared statement, as opposed to parsing and planning each time I run a query?

Where can I learn more about this?  Any direct answers are also very appreciated.

Re: Prepared Statements and Pooling

From
Andomar
Date:
> I have considered opening a single connection to the server and letting all threads execute statements through it,
anduse prepared statements (serviced by postgresql, not JDBC or DBCP).  Or simply opening 50 connections and
establishingprepared statements and then handing them off to the threads.  Is there a reason I shouldn't do this?  I
readthat the JDBC driver for postgres is thread safe. 

Connection pooling reduces the overhead for creating a new connection, and allows Postgres to reuse execution plans for
preparedstatements. Both things will gain you a few milliseconds per query. For a small or medium sized project that is
entirelyirrelevant. 

It is far more important to have stable and easy to understand code. To gain that, keep each connection open for as
shorta time as you can. Run a single query and then close it. 

When the time comes where the few milliseconds matter, it is easy to add connection pooling without changing a single
lineof code. Postgres offers pgbouncer and pgpool for that job, but if DBCP can do it, that’s fine too. 

Kind regards,
Andomar



Re: Prepared Statements and Pooling

From
Alban Hertroys
Date:
On 11 August 2015 at 06:44, Mister Junk <junkmail3568540@gmail.com> wrote:
> I'm using prepared statements to prevent SQL injection, but I have done some
> reading and it seems like using Prepared statements COULD improve
> performance.  I understand the concept, but I do not know how to implement
> this.

They could, they could also reduce performance.

Prepared statements store and reuse an earlier generated query plan
for a query, so that's the time you safe.

However, because the query planner can't make use of the actual query
parameter values (they are yet unknown at that point), it can only
generate a general query plan.

If your data is equally distributed for your query parameters, that
won't matter much, but if some values are far more common than others,
that can actually hurt performance.

In case of a normal statement, the query planner has enough
information to decide which plan would be more efficient. With a
prepared statement it has not.