Re: cache of PreparedStatements - Jeff Mathis - com.mysql.lists.java

12 messages in com.mysql.lists.javaRe: cache of PreparedStatements

From	Sent On	Attachments
Jeff Mathis	19 Aug 2004 16:11
Kevin A. Burton	19 Aug 2004 18:09
Shankar Unni	20 Aug 2004 10:04
Jeff Mathis	20 Aug 2004 10:31
Jeff Mathis	20 Aug 2004 13:59
Kevin A. Burton	20 Aug 2004 15:31
Jeff Mathis	20 Aug 2004 15:39
Mark Matthews	23 Aug 2004 15:43
Kevin A. Burton	23 Aug 2004 18:34
Jeff Mathis	24 Aug 2004 08:41
Mark Matthews	25 Aug 2004 12:48
Jeff Mathis	25 Aug 2004 12:54

Subject:	Re: cache of PreparedStatements
From:	Jeff Mathis (jmat...@predict.com)
Date:	08/20/2004 01:59:46 PM
List:	com.mysql.lists.java

in looking at the source, Connection.prepareStatement seems to always create a new one rather than consulting the cache. clientPrepareStatement seems to be the method to call, correct?

i'm getting the impression that caching prepared statements is really only used in the presence of connection pooling. i don't need a pool of connections -- i already have control over all that.

(lots of DOCUMENT ME statements in the docs. the connector-j documentation you pointed me to didn't seem to have what I'm looking for. still tempted at this point to write a little class to store and retrieve my own preparedStatements on a per-connection basis).

jeff

Kevin A. Burton wrote:

Jeff Mathis wrote:

Hello all,

After migrating to server version 4.3.1, I tried using PreparedStatements, and I'm seeing a good performance improvement compared with using regular Statements. So, I now need a mechanism to cache my PreparedStatements. Its possible I could have about 1000 or so distinct PreparedStatements during the lifetime of a connection.

I could write a little class to store these for me, but in looking through the docs, I see many references to this ability in ConnectionProperties. Can anyone give me a quick pointer on using whats available in the 3.1.3 CJ release?

Add these to your connection URL:

cachePrepStmts=true prepStmtCacheSize=4096 prepStmtCacheSqlLimit=4096

Which are documented here:

http://www.mysql.com/documentation/connector-j/#id2801179

I don't need to use a connection pool, although I could if necessary.

If you're writing multithreaded code with many connections and need performance you REALLY need a good connection pool.

For the record we've had a TON of problems with Jakarta DBCP under high load. I can only duplicate the code in production and I'm probably going to write my own (simple!!!!!!!!!!!!) connection pool from scratch so that its easy to maintain.

Can anyone recommend a good alternative JDBC connection pool?