I have a problem similar to the on in this weeks podcast.
We have a Java application using hibernate with Sql Server 2005.
Hibernate is generating a Query for us that is taking nearly 20 minutes to complete.
If we take the same query using show_sql and replace the questions marks with constant value the answer is returned immediately.
I think we need option(recompile), but I can't figure out how to do that with HQL.
Please help!
You should post your mapping and HQL statement. If you are using "join" in your HQL, you might want to take a look what exactly is fetched by Hibernate. It might turn out that the request itself is simple, but Hibernate is fetching tons of data before the it gets to it.
In my experience, the main problem with complex queries in Hibernate is not the query itself, but rather the creation of all the objects representing the result set.
In my case at work, we had a very large domain model, with lots of couplings, so that even fetching one single object from the database was quite expensive because that object was linked to other objects, which in turn were linked to other objects and so on.
For us, more use of lazy loading solved at least parts of the problem. Smart caching helped even more. What I learned was that in the future, I'll allow more loose coupling between domain classes.
From the description of your problem, it sounds like you're running into parameter sniffing. Essentially, SQL Server is creating a query plan based on an older set of parameter values that were passed in and which do not create an effective execution plan for the currently running query.
Typically I resolve this issue by passing the parameter values into local variables and using those in my query or by using OPTION (RECOMPILE). However, since you are using Hibernate my usual solution isn't an option for you. As I understand it, the best option is going to be to use Hibernate to run a native SQL query using prepareStatement() or createSQLQuery() which, unfortunately, removes some of the benefits of using Hibernate.