使用动态表达式的含义< Func< T，X>在NHibernate [英] Implication of using dynamic Expression<Func<T,X>> in NHibernate

问题描述

  
表达< Func< T，bool>> expr = PredicateBuilder.True< T>（）; 
 expr = expr.And（b => b.Speed> = spec.SpeedRangeFrom&&& b.Speed< = spec.SpeedRangeTo）; 
 ... 
  

它倾向于以多个或和和条件。

一旦我完成了表达式的构建，我将其传递到我的存储库，如下所示：

  var results = Session.Query< T>（）。其中​​（expr）.ToList（）; 
  

这个问题是花了很长时间才能让我结果。我也注意到表达式越长，返回结果集所需的时间越长。

我还使用NHibernate profiler来分析生成的sql语句。在SQL Server Studio上分别运行sql语句，只需要不到1秒。

在我看来，大部分时间都用于构建或将表达式转换为sql

有没有办法解决这个问题？

我没有很多经验使用NHibernate。如果有人可以在这上面放一些灯，那将是很棒的。

提前谢谢你花时间阅读。

解决方案

超过返回的行数，表中的行总数是重要的。

在SQL Server Studio上单独运行sql语句，
只需少于1秒。

以这种方式进行测试可能会导致误导。第二次运行的查询从先前编译的SQL语句，先前计算的查询执行计划，先前填写的数据缓冲区中获益。
此外，通过直接在sql中替换所有参数名称及其值的测试将导致不同的执行计划。

首先要做的是检查索引的错误使用（缺少相关索引或过时的统计信息）

可以通过查看估计的查询执行计划来完成。
关于统计资料，请查看： https://dba.stackexchange.com/q/12004

我想这也应该是有用的：从Nhibernate执行的查询速度很慢，但是从ADO.NET快速

有关清理环境的SQL-SERVER语句测试，请参见下文。

为每个查询运行此批次以比较执行时间和统计结果（不要在生产环境中运行）：

  DBCC FREEPROCCACHE 
 GO 
 
 CHECKPOINT 
 GO 
 
 DBCC DROPCLEANBUFFERS 
 GO 
 
 SET STATISTICS IO ON 
 GO 
 
 SET STATISTICS TIME ON 
 GO 
 
  - 您的查询
 GO 
 
设置统计时间OFF 
 GO 
 
 SET STATISTICS IO OFF 
 GO 
  

Basically in my application, I am using the following code to build my expression depending on the selection on the page.

Expression<Func<T, bool>> expr = PredicateBuilder.True<T>();
expr = expr.And(b => b.Speed >= spec.SpeedRangeFrom && b.Speed <= spec.SpeedRangeTo);
...

It has the tendency to end up with a long expression with multiple "or" and "and" conditions.

Once I have finished building the expression, I passed it on to my repository which looks like the following:

var results = Session.Query<T>().Where(expr).ToList();

The problem with that is it took so long to return me the result. I have also noticed that the longer the expression is, the longer it takes to return me the result set.

I have also used NHibernate profiler to analyse the sql statement generated. Running the sql statement separately on SQL Server Studio, it took only less than 1s.

It seems to me most of the time was spent in building or converting the expression to sql statement.

Is there a way to get around this problem?

I don't have many experience in using NHibernate. If anyone can shed some lights on this, that would be great.

Thank you in advance for taking the time to read this.

解决方案

More than the number of returned rows, it is the total number of rows in your table which is important.

Running the sql statement separately on SQL Server Studio, it took only less than 1s.

Testing this way can be misleading. The second run of the queries benefits from the previously compiled SQL statement, the previoulsy calculated query execution plan, the previously filled-up data buffers. Also, testing by replacing, directly in the sql, all parameters names with their values would lead to a different execution plan.

First thing I would do would be to check for wrong usage of the indexes (missing relevant indexes or obsolete statistics)

This can be done by looking at the estimated query execution plan. As to statistics, have a look at : https://dba.stackexchange.com/q/12004

I guess this should be useful too : Query executed from Nhibernate is slow, but from ADO.NET is fast

See below for testing a SQL-SERVER statement with a somewhat cleaned-up environment.

Run this batch for each query to compare execution time and statistics results (Do not run it on a production environment) :

DBCC FREEPROCCACHE
GO

CHECKPOINT 
GO

DBCC DROPCLEANBUFFERS 
GO

SET STATISTICS IO ON
GO

SET STATISTICS TIME ON
GO

-- your query here
GO

SET STATISTICS TIME OFF
GO

SET STATISTICS IO OFF
GO

这篇关于使用动态表达式的含义< Func< T，X>在NHibernate的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！