在查询了NHibernate的超时错误，而不是在SQL Server [英] Query got timeout error in NHibernate but not In SQL Server

问题描述

我在C＃中有一个问题与NHibernate。

I got a problem with NHibernate in C#.

当它要执行一个查询应用面带ADO超时错误，但是当我使用SQL事件探查器捕获查询，然后我在SQL Server中的新的查询运行它，它正好需要短短2秒。

When it wants to execute a query the application face with a ADO timeout error, but when I use SQL Profiler to capture the query, and then I run it in new query of SQL Server, it happens to take just 2 seconds

任何想法??

推荐答案

当您捕获SQL事件探查器查询并在SSMS运行它，你运行它作为一个sp_executesql的查询？我跑进使用NHibernate 2.1GA一个类似的问题，这个答案适用于该版本，我还没有转化为NH3呢。 NH Profiler是一个伟大的工具，但它有益提取SQL成并不代表发送到服务器的实际查询格式化的查询。

When you capture the query from SQL Profiler and run it in SSMS, are you running it as an sp_executesql query? I ran into a similar problem using NHibernate 2.1GA and this answer applies to that version, I haven't converted to NH3 yet. NH Profiler is a great tool but it helpfully extracts the SQL into a formatted query that doesn't represent the actual query sent to the server.

这个问题是这样的NHibernate耗材字符串参数以使用sp_executesql。串参数类型为等于该值的长度的长度nvarchar的。例如，下面的查询仅分别为两列是VARCHAR（4）和varchar（20）：

The problem is the way NHibernate supplies string parameters to sp_executesql. String parameters are typed as nvarchar with a length equal to the value's length. For example, this query restricts two columns that are varchar(4) and varchar(20) respectively:

exec sp_executesql N'SELECT this_.Column0, this_.Column1 FROM MySchema.MyTable this_ WHERE this_.Column0 = @p0 and this_.Column1 = @p1',N'@p0 nvarchar(4),@p1 nvarchar(7)',@p0='Val0',@p1='Value01'

此查询计划使用索引扫描，并花了17秒。更改为nvarchar为varchar生成用于索引查找和上述<执行的计划; 2秒。这是在SSMS重现性。

The query plan for this used an index scan and took 17 sec. Changing the nvarchar to varchar generated a plan that used an index seek and executed in < 2 sec. This was reproducible in SSMS.

的根本原因是NHibnerate使用DbType.String而不是DbType.AnsiString为VARCHAR列在默认情况下。对我来说，解决办法是增加一个功能NHibernate约定改变所有字符串映射AnsiString类型造成NHibernate的创建提供的参数为varchar查询。

The root cause was the NHibnerate uses DbType.String instead of DbType.AnsiString for varchar columns by default. The solution for me was to add a Fluent NHibernate convention to change all string mappings to AnsiString which caused NHibernate to create queries that supplied parameters as varchar.

这篇关于在查询了NHibernate的超时错误，而不是在SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！