LINQ to SQL 存储过程。异常说超时但没有超时
使用 .NET Framework v3.5、SQL Server 2008、用 C# 编写的网站
我有一个存储过程，通过将其从服务器资源管理器拖动到我的 DBML 中，将其添加到其中。
在它的属性中，它返回自动生成的类型。
该过程需要<从 SQL Mgmt Studio 中运行所有输入需要 1 秒。 然而，从 1 个特定输入的代码（在 Mgmt 工作室中花费 < 1 秒）来看，它会挂起，然后抛出：
System.Data.SqlClient.SqlException：超时已过期。
对于这一输入，这种情况并不总是发生！从代码调用时它也可以正常工作。上次它不起作用时，我删除了相同的存储过程并将其重新添加到 DBML 中。这“修复”了它，并且该输入与所有其他输入同时运行良好。然而，这还不是一个足够的修复！它又发生了，我无法继续根据需要删除和重新添加。
我没有对“修复”期间返回的数据进行任何更改，所以我无法想象问题可能是什么。任何对此的帮助将不胜感激！
Website using .NET Framework v3.5, SQL Server 2008, written in C#
I have a stored procedure which I have added to my DBML by dragging it across from the server explorer.
In it's properties it returns Auto-generated type.
The procedure takes < 1 second to run from within SQL Mgmt Studio for all inputs.
However from the code for 1 particular input (which takes < 1 second in the Mgmt studio) it hangs and then throws:
System.Data.SqlClient.SqlException: Timeout expired.
This didn't always happen for this one input! It used to also work fine when called from the code. The last time it didn't work I deleted and re-added the same stored procedure to the DBML. This "fixed" it, and that input ran fine and in the same time as all the others. However this is not an adequate fix! It has happened again and I can't keep deleting and re-adding as required.
I made no changes to the data that's being returned during the point at which it was "fixed", so I can't think what the problem could be. Any help on this would be much appreciated!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果它说超时，那么它就超时了。唯一的问题是“为什么”？
针对您的数据库运行 SQL Server Profiler 跟踪并查看实际发送到服务器的查询。可能还发出了另一个查询。可能有另一个事务干扰您的生产场景。
If it says it's timing out, it's timing out. The only question is "why"?
Run a SQL Server Profiler trace against your database and see what query is actually going to the server. It's possible that another query is being issued too. It's possible there is another transaction interfering in your production scenario.
事实证明，这是参数嗅探 - 这在另一篇文章中进行了解释： 从 DotNet 执行存储过程需要很长时间，但在 SSMS 中它是立即的
It turns out that this is parameter sniffing - this is explained in another post: Executing stored proc from DotNet takes very long but in SSMS it is immediate
另外，请确保存储过程没有被保留在事务内部，等待另一个进程完成。我刚刚遇到过这个问题，在一个事务中多次调用 Linq to Sql 存储过程。它给了我一个超时过期错误，我刚刚意识到它正在等待先前的调用完成，因此超时。
Also, be sure that the stored procedure is not being held up inside of a transaction, waiting for another process to complete. I just ran across this with a Linq to Sql stored procedure being called multiple times within a transaction. It gave me a timeout expired error and I just realized it was waiting for a previous call to complete, and thus timing out.