Re: Does "preparing" a PreparedStatement really help?

["Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>]

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