Hibernate Query在系统中运行缓慢，但直接运行时速度很快 [英] Hibernate Query runs slow in the system, but fast when run directly
问题描述
我们有一个Java应用程序，在Sql Server 2005中使用hibernate。
Hibernate正在为我们生成一个查询，耗时将近20分钟。
如果我们使用show_sql进行相同的查询并替换问题用常数值标记，答案立即返回。
我认为我们需要选项（重新编译），但我无法弄清楚如何使用HQL来完成这项工作。 / p>
请帮忙！
听起来像你正在进入参数嗅探。本质上，SQL Server根据传入的一组较旧的参数值创建查询计划，这些参数值不会为当前运行的查询创建有效的执行计划。
通常，我通过将参数值传入本地变量并使用查询中的或通过使用OPTION（RECOMPILE）来解决此问题。但是，由于您正在使用Hibernate，我通常的解决方案不适合您。据我所知，最好的选择是使用Hibernate来运行使用prepareStatement（）或createSQLQuery（）的本地SQL查询，不幸的是，它消除了使用Hibernate的一些好处。
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.
这篇关于Hibernate Query在系统中运行缓慢，但直接运行时速度很快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
