java中queryrun的好处,Hibernate Query在系统中运行缓慢，但直接运行时速度很快

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!

解决方案

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.