Hi,

I'm using Hibernate with MSSqlServer2005 and the jTDS project. When looking at the queries, a lot of sp_prepare statements are generated. In general these queries are re-used for a limited time and then recreated.

Is there a way to control the lifetime - i.e. let SQLServer re-use the sp_prepare statement for a longer period - of these stored procedures ?

Regards, Stefan Lecho.

Hibernate version:
3.2.5.ga

jtds version:
1.2

One way is to use a connection pool that caches prepared statements. JBoss AS's pool offers this, as does c3p0. Check the docs for whatever pool you're using.

We're using C3PO for the Connection Pooling. I suppose that the maxStatements property could be used for this purpose, but I do not completely grasp the exact meaning of it.

Does this parameter specifies the (maximum) number of sp_executes that can be called for 1 sp_prepare statement ?

I can't speak to the implementation of that particular driver but think of how JDBC works. You have a Connection, and you use that Connection to prepare a statement. The PS is tied to the Connection and can be reused over and over again. When you pool Connections, you can quite easily pool/cache the PreparedStatements along with it and keep using them. c3p0 helpfully allows you to configure a max per Connection as well as a global max, if I read the documentation correctly.

You seem to be pretty familiar with what's a happening on the wire so why don't you give it a whirl and let us know what you find?

-Chris