How do I know Connection Pooling and Prepared Statements are working?

Question

I have been developing a web application for almost a year which I had started in college. After finishing, I made some changes to the database code that I had written during college. I used simple JDBC when I was in college, without the use of connection pooling and prepared statements. In the past month, I have realized the potential, and need to use connection pooling, and prepared statements because of the increase in data being queried and used.

I have implemented the connection pooling, but have not really noticed an increase in performance. If there was an increase in performance, then my question would be answered, but how can I validate that connection pooling and prepared statements are being used properly?

Answer 1

I think prepared statement is more important from a security than a performance standpoint. If you are using them cool, you really should be. As long as 100% of your SQL is a prepared statement, you are "using them right"...

Connection pooling is something you won't notice under light load. 1 or 2 or 10 users, you will likely see 0 or near 0 difference. You would need to create some kind of load testing test to simulate 100s of simultaneous users, then compare performance with and without pooling.

Answer 2

You won't see a performance improvement if opening and closing connections wasn't rate-limiting. Try looking at your database's profiling tools for a list of open connections. If you see open connections from your process when you have ostensibly closed them, the interface layer is doing connection pooling.

See also this other SO question on forcing a pooled connection to drop.

(Caveat:I use ADO and ADO.NET; JDBC might behave differently.)

Answer 3

You ask the connection pool for statistics to see if it works. If the connection pool library does not provide statistics, find a better library.