Обсуждение: Prepared Statements and Pooling
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.
> 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
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.