10

As a PreparedStatatement contains precompiled sql command(s), so when if we make a pool of this type in order not create and destroy this object too much(just like thread pool).
Does it make any sense? or I'm just so confused?

  • where pre-compiled sql commands are stored? – Braj Jul 27 '14 at 12:18
  • @user3218114 assume an application server(extreme work of course). –  Jul 27 '14 at 12:19
  • This depends heavily on the DBMS you are using (Postgres, Oracle, DB2, ...). Please add the appropriate tag. –  Jul 27 '14 at 12:22
  • @a_horse_with_no_name it's `Postgres` dude. –  Jul 27 '14 at 12:23
  • 1
    Then the driver can do that for you (but more efficiently on the server side): http://jdbc.postgresql.org/documentation/head/server-prepare.html –  Jul 27 '14 at 12:25
  • @a_horse_with_no_name just one question, the solution provided with your link, is it thread safe? and is it belong to all connections? or each connection has its own? –  Jul 27 '14 at 12:34
  • @user2889419 Each connection has its own, as prepared statements are per-session. Thread safety is as specified by PgJDBC. – Craig Ringer Jul 27 '14 at 12:41
  • @CraigRinger so for 128-len connection pool, each one will have its own prepared sql command. am I right? thanks. –  Jul 27 '14 at 12:47
  • **short answer is no** –  Oct 09 '14 at 00:57

6 Answers6

1

I think what you are looking for is caching of prepared statements. Some connection pools do that for you as an optional tuning parameter (Weblogic, I think JBoss too). Handy for situations where the same prepared statement will get used multiple times in a runtime session, not necessarily even in the same transaction. Your use of a static basically means you only think you'll have one of them instead of needing a cache for multiple statements, so theoretically it would work. The thing I'm not sure of is if the prepared statement cache can be shared across connections, or if it is connection-specific.

Amogh
  • 4,453
  • 11
  • 45
  • 106
0

No, because you have to bind them on the client side each time.

The server may cache the compiled PreparedStatement (e.g. parsed, checked to make sure table exist & columns are correct types, etc.), which is what you really want.

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • 4
    Yet, some connection pools and some JDBC drivers do cache prepared statements. Some databases cache them on the server itself. It all depends on the database. – JB Nizet Jul 27 '14 at 12:29
  • It can safe CPU (on client and server) and network roundtrips if you cache them. Sone JDBC libs do that themself, otherwise you would make your pool do it. – eckes Oct 09 '14 at 00:45
0

PreparedStatements are attached to connections, so even though it is a good idea to re-use them as much as possible, pooling them explicitly will not work because it may require you to have too many open connections. Another thing to keep in mind is that you can only have one ResultSet open per connection, so it's going to be difficult to manage which Statements can be attached to the same connection without understanding whether the app will need simultaneous ResultSets for that Statement.

Zeki
  • 5,107
  • 1
  • 20
  • 27
-1

Caching prepared statments makes sense if you run the same query multiple times from different places in your code. Since you're working with PostgreSQL, you don't need to implement that from scratch -- PostgreSQL JDBC connector already supports this feature (prepared statements are stored on server side), see here: http://jdbc.postgresql.org/documentation/head/server-prepare.html

I've personally seen it work and deliver +200% boost by just turning this cache on.

t7ko
  • 291
  • 2
  • 6
-1

Yes it speeds up the execution of statements. Connection pools does for that for you with max cache default value.

http://dev.mysql.com/doc/refman/5.6/en/statement-caching.html

Sandeep Vaid
  • 1,409
  • 11
  • 7
-1

Yes, it makes sense if you fire the same query often and re-use connections (via a connection pool). The "driver-side prepared statement cache" is available in the pgjdbc-ng JDBC driver for PostgreSQL. The caching is described in more detail in pull request 64.

Note that this is a (performance) optimization: you should not rely on it to increase the speed of the application. The application will spend a lot more time waiting on network latency and query results from the database, caching will not improve that. Tuning the database (schema) and improving the network will have a greater impact.

vanOekel
  • 6,358
  • 1
  • 21
  • 56