Paul Tomblin wrote:
> Is there a performance advantage for preparing a PreparedStatement and
> keeping it around and using it thousands of times rather than making a
> new Statement every time?
Yes.
> How big?
It depends. If your queries are not very complex, or the little bit of
extra CPU usage isn't a problem for you because the bottlenecks are
elsewhere, it might be completely insignificant for you. You would have
to test it with your application to known for sure.
> Back when I was doing Oracle Call Interface programming in C back in the
> mid to late 1980s, we were always told that pre-parsing a query was very
> expensive and so you tried not to do it very often, and once you'd done
> it, you stored them to reuse. As I try to switch this system over to
> using a connection pool, trying to store PreparedStatements for each
> connection is fairly complicated and I'm wondering if it's worth it.
Yeah, with a connection pool you can't really do it like that. You want
to use a technique called "statement caching", where the connection pool
/ driver keeps a cache of prepared statements, so that when you create a
new PreparedStatement and prepare it, it actually reuses an already
prepared one from the cache. Many if not most connection pool
implementations have a statement cache, but you might need to so
something to enable it; check the docs. There's also a stand-alone
statement cache implementation at
http://jdbccache.projects.postgresql.org/ which you can use.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com