commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Holly, Michael" <>
Subject RE: Prepared Statement pooling
Date Fri, 20 Feb 2004 15:21:04 GMT
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

- 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:

View raw message