从 Nhibernate 执行的查询很慢，但从 ADO.NET 执行的查询很快 [英] Query executed from Nhibernate is slow, but from ADO.NET is fast
问题描述
我的 MVC 应用程序中有一个查询需要大约 20 秒才能完成(使用 NHibernate 3.1).当我在 Management Studio 上手动执行查询时，它需要 0 秒.
I have a query in my MVC application which takes about 20 seconds to complete (using NHibernate 3.1). When I execute the query manually on Management studio it takes 0 seconds.
我在 SO 上看到过类似的问题，与这个问题类似，所以我更进一步地进行了测试.
I've seen similiar questions on SO about problems similar to this one, so I took my test one step further.
我使用 Sql Server Profiler 拦截查询，并在我的应用程序中使用 ADO.NET 执行查询.
I intercepted the query using Sql Server Profiler, and executed the query using ADO.NET in my application.
我从 Profiler 得到的查询类似于:exec sp_executesql N'select ...."
The query that i got from the Profiler is something like: "exec sp_executesql N'select...."
我的 ADO.NET 代码:
My ADO.NET code:
SqlConnection conn = (SqlConnection) NHibernateManager.Current.Connection;
var query = @"<query from profiler...>";
var cmd = new SqlCommand(query, conn);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return RedirectToAction("Index");
这个查询也非常快，执行起来不花时间.
This query is also very fast, taking no time to execute.
另外，我在 Profiler 上看到了一些非常奇怪的东西.从 NH 执行的查询具有以下统计信息:
Also, I've seen something very strange on the Profiler. The query, when executed from NH, has the following statistics:
阅读:281702写:0
reads: 281702 writes: 0
来自 ADO.NET 的:
The one from ADO.NET:
阅读:333写:0
有人知道吗?我可以提供任何信息来帮助诊断问题吗?
Anyone has any clue? Is there any info I may provide to help diagnose the problem?
我认为可能与某些连接设置有关，但 ADO.NET 版本使用的是来自 NHibernate 的相同连接.
I thought it might be related to some connection settings, but the ADO.NET version is using the same connection from NHibernate.
提前致谢
更新:
我正在使用 NHibernate LINQ.该查询非常庞大，但属于分页查询，仅获取 10 条记录.
I'm using NHibernate LINQ. The query is enormous, but is a paging query, with just 10 records being fetched.
传递给exec sp_executesql"的参数是:
The parameters that are passed to the "exec sp_executesql" are:
@p0 int,@p1 datetime,@p2 datetime,@p3 bit,@p4 int,@p5 int
@p0 int,@p1 datetime,@p2 datetime,@p3 bit,@p4 int,@p5 int
@p0=10,@p1='2009-12-01 00:00:00',@p2='2009-12-31 23:59:59',@p3=0,@p4=1,@p5=0
@p0=10,@p1='2009-12-01 00:00:00',@p2='2009-12-31 23:59:59',@p3=0,@p4=1,@p5=0
推荐答案
我的 ADO.NET 和 NHibernate 使用了不同的查询计划，但我在 NH 版本上受到参数嗅探的影响.为什么?因为我之前做了一个小日期间隔的查询，并且存储的查询计划已经针对它进行了优化.
I had the ADO.NET and NHibernate using different query-plans, and I was sufering the effects of parameter sniffing on the NH version. Why? Because I had previously made a query with a small date interval, and the stored query-plan was optimized for it.
之后，当查询大日期间隔时，使用存储的计划并且需要很长时间才能得到结果.
Afterwards, when querying with a large date interval, the stored plan was used and it took ages to get a result.
我确认这实际上是问题所在，因为一个简单的:
I confirmed that this was in fact the problem because a simple:
DBCC FREEPROCCACHE -- clears the query-plan cache
再次快速查询.
我找到了两种方法来解决这个问题:
I found 2 ways to solve this:
- 使用 NH 拦截器向查询注入选项(重新编译)"
- 在我的 NH Linq 表达式中添加一个虚拟谓词，例如:
query = query.Where(true)当预期的结果集很小(日期间隔明智)时.这样就可以创建两种不同的查询计划，一种用于大型数据集，一种用于小型数据集.
- Injecting an "option(recompile)" to the query, using a NH Interceptor
- Adding a dummy predicate to my NH Linq expression, like:
query = query.Where(true)when the expected result-set was small (date interval wise). This way two different query plans would be created, one for large-sets of data and one for small-sets.
我尝试了两种选择，都有效，但选择了第二种方法.这有点hacky，但在我的情况下效果很好，因为数据按日期均匀分布.
I tried both options, and both worked, but opted for the second approach. It's a little bit hacky but works really well I my case, because the data is uniformly distributed date-wise.
这篇关于从 Nhibernate 执行的查询很慢，但从 ADO.NET 执行的查询很快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
