-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1

Jeff Mathis wrote:

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

Only client-side prepared statements can currently be cached. The driver will call clientPrepareStatement() (and thus cache if it's enabled) when it detects it connected to something older than MySQL-4.1, or if the user has explicitly disabled server side prepared statements by setting 'useServerPrepStmts=false' in their URL.

Eventually the server will cache prepared statements, which will help some. You could also look at a connection pool that would cache any type of prepared statement, such as c3p0 (http://sourceforge.net/projects/c3p0). Later versions of DBCP also have this functionality, but it appears Kevin is having issues with their implementation under load (which I'd like to hear more about).

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

I've heard good things about both c3po and proxool.

-Mark

- -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com

MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBKnMitvXNTca6JD8RAlTcAKCu9DvjJ/cxfjLTcy7NIvCPKhTG9QCghHem d3mH7yzJwD/nMLUplS8KN2k= =ctai -----END PGP SIGNATURE-----