de tomKronieken

A few months ago, we encountered performance problems when accessing our DB. After some searching, we quickly found out that PreparedStatements actually are better than plain Statements (don't ask). But this only is the case if you can reuse the same PreparedStatement over and over again, on the same connection. Since we were using a Connection pool, the question was raised whether putting a Connection back in the pool wouldn't "erase" its PreparedStatement pool. Since we didn't find any answer to this question, we decided to take the safe way, and build our own PreparedStatementCache. Do I surprise you if I tell you that this isn't as easy as it seems? Especially if you start mixing in Threads to clean up statements and the like....

To make a long story short, the PreparedStatementCache kept causing headaches. Every now and then, we encountered closed PreparedStatements on places where they weren't meant to be. So yesterday, I weeded everything away again, replacing everything with a ConnectionPool from Oracle (instead of the single connection we were using before). The major question remained though: If we put Connections back into the pool, will their PreparedStatements be reusable?

And this morning, I googled to finally find this article. Praise the Lord. Months of headaches down the drain. I'm a happy man today.

Update: read this too. Very interesting.

Update 2: I still wasn't very happy with the solution we got, because it was very Oracle-dependent (and in fact even dependent on the version of classes12.zip you were using). Thanks to a comment from Wim, I found out that DBCP also does Statement caching. I had heard about DBCP before, and have investigated it for this, but didn't find the reference to Statement caching. Thanks Wim! However, according to Koen, c3p0 should do a better job than DBCP. And it also has the StatementCaching option, so we went for that one. Fingers crossed that it passes the real-life tests.