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 Michael -----Original Message----- From: Todd Carmichael [mailto:[EMAIL PROTECTED] 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. ToddC -----Original Message----- From: Bill Culp [mailto:[EMAIL PROTECTED] 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? I may change the code to use the commons collections LRUMap, that seems like it would have less overhead. > >Thoughts? > > >ToddC > > > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
