NHibernate花很长时间运行查询 [英] NHibernate taking a long time to run query

问题描述

这是通过Fluent NHibernate完成的

This is being done using Fluent NHibernate

我有一个NHibernate查找，该查找从一个表中检索数据.如果我采用生成的sql并通过查询分析器运行它，则需要约18ms的时间来运行.

I've got a NHibernate lookup that is retrieving data from one table. If i take the generated sql and run it through query analyzer, it takes ~18ms to run.

使用NHProfiler，我得到的查询持续时间为〜1800ms-比sql长100倍！

Using NHProfiler, i'm getting the duration of this query as ~1800ms - 100 times longer than sql !

Query duration
 - Database only:1800ms
 - Total: 1806ms

正在填充的对象包含一个子类，但是该子对象是从NHibernate 2级缓存中加载的

The object that is being populated contains a child class, but this child is being loaded from the NHibernate 2nd level cache

将返回的数据进行分页(每个查询50个)，尽管据我所知，这没有什么区别

The data that is being returned is paged (50 per query) although as far as i can tell, this shouldn't make any difference

我也正在运行一个计数，同样，根据NHProfiler，这在查询分析器中花费了约4毫秒，而在1800毫秒中花费了约1800毫秒.

I've also got a count running, and again, this is taking ~4ms in query analyzer and ~1800ms according to NHProfiler.

NH Profiler是否显示 query 执行时间，还是显示检索，映射类和构造对象图的完整时间?如果是前者-为什么比直接运行查询要花这么长时间?

Is NH Profiler displaying the query execution time, or the complete time to retrieve, map the classes and construct the object graph? And if it's the former - why's it taking so much longer than running the query directly?

刚发现Ayende的这则帖子有关NH Profiler中给出的查询持续时间"值:

Just found this post by Ayende about the Query Duration value given in NH Profiler: http://ayende.com/Blog/archive/2009/06/28/nh-prof-query-duration.aspx - so it is definitely the query of the database that is taking a long time

推荐答案

最终设法找到了问题所在.

Finally managed to track down the problem.

对象的主键是数据库中的varchar. NHibernate在运行查询时将值转换为nvarchar.不幸的是，在NH Profiler中查看生成的sql时，这并不明显.变慢是由sql将nvarchar转换回varchar引起的.

The primary key for the object is a varchar in the database. NHibernate was converting the value to an nvarchar when it ran the query. Unfortunately this wasn't obvious when looking at the generated sql in NH Profiler. The slowdown was caused by sql converting the nvarchar back to a varchar

我已指定映射使用自定义类型

I've specified the mapping to use a custom type

map.Id(x => x.Id).CustomType("AnsiString");

问题解决了

为所有帮助人员欢呼:)

Cheers for all the help people :)

这篇关于NHibernate花很长时间运行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！