Hibernate version:

3.2.0.ga

Name and version of the database you are using:

MS SqlServer 2000

My DBAs are reporting the following issue with use of parameterised queries as produced by Hibernate. Sorry that isn't directly relevant to Hibernate but I know of no other forum where I can find a community with same skills and experience. Unfortunately if I can't get around this problem our adoption of Hibernate will be bought into question.

A fairly complex Criteria query in Hibernate is producing SQL something like the following:



What they're telling me is that due to the way the SqlServer cost-based optimiser is working:

1. The sp_prepare creates an execution plan best suited to execute the query based upon the statistics present for the tables but the plan will not be optimal for all combinations of parameters
2. sp_execute executes the sql with the supplied parameters but the plan is not optimal and is not recompiled.

This is due to un-even distribution of data in the parameterised columns.

Execution times are varying between 3 seconds to 3 minutes compared to the stored-proc equivalents.

Any ideas on how I can influence SqlServer to do the right thing? I.e. reconstruct the query plan based on the parameters that are passed without having to resort to throwing out Hibernate and using Stored Procs instead?

Thanks!

If anyone is interested it seems that we were suffering from a widely known (in the SqlServer community) problem called the "Parameter Sniffing Problem" - Google for more info.

Our not particularly elegant work-around is to use Expression.sql() to construct the affected bits of the where clause using String concatentation instead of the normal method which results in parameterised queries.

PM me for further info if you have this problem.

The issue I originally posted about is occurring more frequently and it's looking like we need to find a way to default to un-parameterised parameter bindings.

E.g. rather than:

Restrictions.eq("myProperties", someValue)

we need:

Restrictions.sqlRestriction("{alias}.myPropCol = '" + someValue + "'")

Does anyone know a way of implementing this as default behaviour when Hibernate constructs the SQL?

Even as I type this I realise that equality is only the simple case and "in" clauses would be more complicated and datatype specific.

Another solution seems to be use a MSSQLServer hint "option(recompile)" to force reevaluation of the query plan for each query. In our case the performance benefits would outweigh the costs as counter-intuitive as this sounds. However I can't see how to use sql query hints in the generated SQL, any ideas?

Looking forward to any responses!

Matt.