I got a problem with NHibernate in C#.
我在C＃中遇到了NHibernate的问题。
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
当它想要执行查询时，应用程序面临ADO超时错误，但是当我使用SQL事件探查器捕获查询，然后我在SQL Server的新查询中运行它时，它只需要2秒
Any ideas??
有任何想法吗？？
2 个解决方案
#1
9
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.
从SQL事件探查器捕获查询并在SSMS中运行时，是否将其作为sp_executesql查询运行？我使用NHibernate 2.1GA遇到了类似的问题，这个答案适用于那个版本，我还没有转换为NH3。 NH Profiler是一个很棒的工具，但它有助于将SQL提取为格式化查询，该查询不代表发送到服务器的实际查询。
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:
问题是NHibernate为sp_executesql提供字符串参数的方式。字符串参数的类型为nvarchar，其长度等于值的长度。例如，此查询分别限制两列varchar（4）和varchar（20）：
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'
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.
对此的查询计划使用了索引扫描并花了17秒。将nvarchar更改为varchar会生成一个使用索引查找并在<2秒内执行的计划。这在SSMS中是可重现的。
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.
根本原因是NHibnerate默认使用DbType.String而不是DbType.AnsiString用于varchar列。我的解决方案是添加一个Fluent NHibernate约定来将所有字符串映射更改为AnsiString，这导致NHibernate创建提供参数为varchar的查询。
#2
4
well i hv seen nhibernate timeouts occurring when you are dealing with a transaction that is not yet committed to the database and using a different transaction that operates on the same object.. so i would suggest look out for multiple sessions poen within your app and make sure that is not the case and use only 1..
好吧，当你处理一个尚未提交到数据库的事务并使用在同一个对象上运行的不同事务时，我发现nhibernate超时发生..所以我建议在你的应用程序中查找多个会话并制作确定不是这种情况，只使用1 ..
and also using nhibernate profiles is something that i would suggest too.. http://nhprof.com/ Its a cool tool to have.. it actually shows the query fired to the db and the rows retrieved and is very easy to use too..All you need to do is set the connection string to the dB that u r running the query against and voila u can see all ur queries and u can say good bye to SQL profiler.
我也建议使用nhibernate配置文件.http：//nhprof.com/它是一个很酷的工具...它实际上显示了向数据库发出的查询和检索到的行，也非常容易使用..你需要做的是将连接字符串设置为你运行查询的dB，并且你可以看到所有你的查询，你可以向SQL分析器说再见。
Hope that helps.
希望有所帮助。
#1
9
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.
从SQL事件探查器捕获查询并在SSMS中运行时，是否将其作为sp_executesql查询运行？我使用NHibernate 2.1GA遇到了类似的问题，这个答案适用于那个版本，我还没有转换为NH3。 NH Profiler是一个很棒的工具，但它有助于将SQL提取为格式化查询，该查询不代表发送到服务器的实际查询。
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:
问题是NHibernate为sp_executesql提供字符串参数的方式。字符串参数的类型为nvarchar，其长度等于值的长度。例如，此查询分别限制两列varchar（4）和varchar（20）：
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'
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.
对此的查询计划使用了索引扫描并花了17秒。将nvarchar更改为varchar会生成一个使用索引查找并在<2秒内执行的计划。这在SSMS中是可重现的。
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.
根本原因是NHibnerate默认使用DbType.String而不是DbType.AnsiString用于varchar列。我的解决方案是添加一个Fluent NHibernate约定来将所有字符串映射更改为AnsiString，这导致NHibernate创建提供参数为varchar的查询。
#2
4
well i hv seen nhibernate timeouts occurring when you are dealing with a transaction that is not yet committed to the database and using a different transaction that operates on the same object.. so i would suggest look out for multiple sessions poen within your app and make sure that is not the case and use only 1..
好吧，当你处理一个尚未提交到数据库的事务并使用在同一个对象上运行的不同事务时，我发现nhibernate超时发生..所以我建议在你的应用程序中查找多个会话并制作确定不是这种情况，只使用1 ..
and also using nhibernate profiles is something that i would suggest too.. http://nhprof.com/ Its a cool tool to have.. it actually shows the query fired to the db and the rows retrieved and is very easy to use too..All you need to do is set the connection string to the dB that u r running the query against and voila u can see all ur queries and u can say good bye to SQL profiler.
我也建议使用nhibernate配置文件.http：//nhprof.com/它是一个很酷的工具...它实际上显示了向数据库发出的查询和检索到的行，也非常容易使用..你需要做的是将连接字符串设置为你运行查询的dB，并且你可以看到所有你的查询，你可以向SQL分析器说再见。
Hope that helps.
希望有所帮助。