查询在 NHibernate 中遇到超时错误，但在 SQL Server 中没有
我在 C# 中遇到了 NHibernate 的问题。
当它想要执行一个查询时，应用程序面临 ADO 超时错误，但是当我使用 SQL Profiler 捕获该查询，然后在 SQL Server 的新查询中运行它时，它恰好只需要 2 秒
有什么想法吗？
I got a problem with NHibernate in C#.
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
Any ideas??
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
当您从 SQL Profiler 捕获查询并在 SSMS 中运行它时，您是否将其作为 sp_executesql 查询运行？我使用 NHibernate 2.1GA 遇到了类似的问题，这个答案适用于该版本，我还没有转换为 NH3。 NH Profiler 是一个很棒的工具，但它有助于将 SQL 提取为格式化查询，该查询并不代表发送到服务器的实际查询。
问题在于 NHibernate 向 sp_executesql 提供字符串参数的方式。字符串参数的类型为 nvarchar，长度等于值的长度。例如，此查询限制分别为 varchar(4) 和 varchar(20) 的两列：
此查询计划使用索引扫描并花费了 17 秒。将 nvarchar 更改为 varchar 会生成一个使用索引查找并在 << 中执行的计划。 2秒。这在 SSMS 中是可重现的。
根本原因是 Nibnerate 默认使用 DbType.String 而不是 DbType.AnsiString 作为 varchar 列。我的解决方案是添加 Fluent NHibernate 约定，将所有字符串映射更改为 AnsiString，这导致 NHibernate 创建以 varchar 形式提供参数的查询。
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.
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:
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.
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 超时发生。所以我建议在您的应用程序中查找多个会话 poen 并进行确保情况并非如此，仅使用 1..
我也建议使用 nhibernate 配置文件.. http:// nhprof.com/
它是一个很酷的工具..它实际上显示了向数据库触发的查询和检索的行，并且也非常易于使用..您需要做的就是将连接字符串设置为您运行查询的数据库，然后瞧，您可以看到所有查询，并且可以告别 SQL 分析器了。
希望有帮助。
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..
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.
Hope that helps.