commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Todd Carmichael <>
Subject RE: Prepared Statement pooling
Date Fri, 20 Feb 2004 17:16:14 GMT
If I am reissuing the same sql query many times, there will be a decent
payback to caching the preparedstatement object returned from the call to
preparestatement from the client (java and jdbc in our case).  If I do not
reuse the same preparedstatement object (i.e. not using preparedstatement
caching), I must reissue the call to preparestatement which I am sure at
minimum is a network round trip and other overhead.  If you look at
connection pooling provide by the major application servers like Sun ONE and
Weblogic, they all provide prepared statement caching.  

Our problem is that we are calling preparedstatement on queries that really
shouldn't be prepared because they are unique and will not occur again.
That is our problem that we need to fix at some point.  However, for right
now I searching for a workaround by limiting the amount of preparedstatement
objects we cache.  


-----Original Message-----
From: Holly, Michael [] 
Sent: Friday, February 20, 2004 7:21 AM
To: Jakarta Commons Users List
Subject: RE: Prepared Statement pooling

A prepared statement is usually cached on the DB. (depends on the DB) When a
prepared statement is created you are providing a template for some SQL with
placeholders for any variables.  This statement when handed to the DB is
parsed and resolved against the DB objects.  Then an execution plan is made.
This execution plan is what is cached.  For any subsequent queries with this
statement the DB does not have to do the parse, resolve, and create
execution plan steps. For the subsequent queries all the DB has to do is
fill the placeholders with the params and run the query.

Your connection pool should be maintaining an available connection for you
application to submit queries to the DB.

What makes running a query expensive on a DB is

1. Creating a new connection to the DB           (should be taken care
of if you are using a connection pool. I use DBCP.)
2. Generating the execution plan for the statement.   (should already be
done if it is a prepared statement)
3. Executing the execution plan for the query.
4. Returning the rows across the connection.

Based on this it doesn't make a lot of sense to cache these queries.

One of the biggest boosts you can give your application is correct
connection management

Hope this helps


-----Original Message-----
From: Todd Carmichael [] 
Sent: Thursday, February 19, 2004 11:57 PM
To: 'Jakarta Commons Users List'
Subject: RE: Prepared Statement pooling

Yes.  I don't have exact numbers but quite significant.  


-----Original Message-----
From: Bill Culp [] 
Sent: Thursday, February 19, 2004 8:09 PM
To: Jakarta Commons Users List
Subject: Re: Prepared Statement pooling

Just curious,
Have you seen measurable performance improvement caching PreparedStatements?

- Bill
Todd Carmichael wrote:

>I need different behavior from the Prepared Statement pooling that is 
>implemented in the DriverAdapterCPDS (at least I think I do).
>Basically I want the prepared statement pool to limit the number of 
>items pooled and if I am out of room, remove the least recently used 
>element to make room for the new one.  Perhaps that is not what a 
>'pool' is but that is our needs. We have issues with our prepared 
>statements in that many of them pass in constants that really should be

>parameters, so our cache grows very large. We know this is a problem in

>our code and will hopefully address this issue because it affects our
>app server and db server performance.  But for now, our project does 
>not have time for this kind of refactor.  The evictor thread does clean

>up those unused prepared statement, but I would like to set a hard
>limit on the number of statements and if I reached the limit, evict the

>oldest statement.  On a side note: the evictor thread on the prepared
>statement will create a thread per connection which seems like high 
>overhead.  Right now the GenericKeyedObjectPool is used for pooling 
>prepared statements and the values for the pool are taken directly from

>the values of the connection pool.  Is there a different pool
>implementation or settings I could set to get this LRU type behavior?
may change the code to use the commons collections LRUMap, that seems like
it would have less overhead.

To unsubscribe, e-mail:
For additional commands, e-mail:

To unsubscribe, e-mail:
For additional commands, e-mail:

To unsubscribe, e-mail:
For additional commands, e-mail:

To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message