这是使用 Fluent NHibernate 完成的。
我有一个 NHibernate 查找，可以从一个表中检索数据。如果我获取生成的 sql 并通过查询分析器运行它，则运行大约需要 18 毫秒。
使用 NHProfiler，我得到这个查询的持续时间为 ~1800ms - 比 sql 长 100 倍！
Query duration
- Database only:1800ms
- Total: 1806ms
正在填充的对象包含一个子类，但是这个子类是从 NHibernate 二级缓存加载的。
返回的数据是分页的（每个查询 50 个），尽管据我所知，这不应该产生任何影响。差异
我还运行了一个计数，同样，这在查询分析器中花费了约 4 毫秒，根据 NHProfiler，花费了约 1800 毫秒。
NH Profiler 是否显示查询执行时间，或者检索、映射类和构造对象图的完整时间？如果是前者 - 为什么它比直接运行查询花费的时间长得多？
编辑：刚刚发现 Ayende 发表的关于 NH Profiler 中给出的查询持续时间值的文章：http://ayende.com/Blog/archive/2009/06/28/nh-prof-query-duration.aspx - 所以这绝对是数据库的查询需要很长时间
This is being done using Fluent NHibernate
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.
Using NHProfiler, i'm getting the duration of this query as ~1800ms - 100 times longer than sql !
Query duration
- Database only:1800ms
- Total: 1806ms
The object that is being populated contains a child class, but this child is being loaded from the NHibernate 2nd level cache
The data that is being returned is paged (50 per query) although as far as i can tell, this shouldn't make any difference
I've also got a count running, and again, this is taking ~4ms in query analyzer and ~1800ms according to NHProfiler.
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?
EDIT: 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
发布评论
评论（2）
终于成功找到了问题所在。
该对象的主键是数据库中的 varchar。 NHibernate 在运行查询时正在将该值转换为 nvarchar。不幸的是，在 NH Profiler 中查看生成的 sql 时，这一点并不明显。速度减慢是由 sql 将 nvarchar 转换回 varchar 引起的，
我已指定映射以使用自定义类型
，问题已解决，
为所有帮助人员干杯:)
Finally managed to track down the problem.
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
and the problem is solved
Cheers for all the help people :)
通常，这些问题可以解决您和数据库之间的网络问题。 QA 通常直接连接到数据库，它所需要发送的只是格式化后的原始数据。您的应用程序可能会将结果集转换为数据集或类似的构造。为了证明这一点，请更改一些代码（而不是整个数据层）以使用 SQL 数据读取器来读取数据。只需读取所有记录，而无需尝试解析所有列并保存数据。它的执行速度可能会与您的网络允许的速度一样快。
generally these problems resolve to the network between you and your data base. QA usually connects directly to the data base and all it has to send is the raw data back where its formatted. Your app is probably converting your result set into a data set or similar construct. To prove this, change a bit of code (not your entire data layer) to use a SQL Data Reader to read your data. Just read all of the records without trying to parse out all of the columns and save the data. It will likely perform as fast as your network will let it.