从NHibernate的执行的查询速度慢，但是从ADO.NET快 [英] Query executed from Nhibernate is slow, but from ADO.NET is fast

问题描述

我有一个查询在我的MVC应用程序，大约需要20秒才能完成（使用NHibernate 3.1）。当我在管理工作室手工执行查询其取值为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.

我已经看到了关于一个与此相似的问题similiar问题上如此，所以我把我的测试又进了一步。

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.

这是我从探查得到的查询是这样的：EXEC sp_executesql的N'select ......

The query that i got from the Profiler is something like: "exec sp_executesql N'select...."

我的ADO.NET code：

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.

另外，我已经看到一些在探查很奇怪。查询，当距离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

reads: 333 writes: 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日期时间，@ p2的日期时间，@ P3位，@ 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不同的查询计划，而我sufering参数嗅探的NH版本的效果。为什么？因为我有previously制成的查询用小日期间隔，并且所存储的查询计划它被优化。

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

再快让我的查询。

made my query fast again.

我发现2种方法来解决这个问题：

I found 2 ways to solve this:

• 注入一个选项（重新编译）到查询，使用NH拦截
• 添加一个虚拟的predicate我NH Linq的前pression，如：查询= query.Where（真）时，预期的结果，集显小（日期间隔明智的）。这种方式将创建两个不同的查询计划，一个用于大型数据集和一个用于小集。

• 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.

我试了两个选项，两个工作，但选择了第二种方法。这是一个有点的哈克的，但确实很好我我的情况，因为数据是均匀分布的日期明智的。

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屋！