Linq存储过程超时，但SSMS快速 [英] Linq Stored Procedure Timeout but SSMS Quick

问题描述

我有一个使用LinqToSQL调用的存储过程.我一点也不做任何特别的事情，例如

I have a stored procedure which I am calling using LinqToSQL. I'm not doing anything special at all, e.g.

MyDataContext db = new MyDataContext()

var results = db.storedProcedure(param1, param2, param3)

// Do stuff

当我使用完全相同的参数运行存储过程时，我会在2到6秒之间得到结果.该数据库是一个远程数据库.

When I run the stored procedure using the exact same parameters I get results between 2 and 6 seconds. The database is a remote database.

但是，当我运行存储过程时，它花费了275秒(调试后……).在正常情况下，这会产生以下异常:

However, when I run the stored procedure it takes (after debugging....) 275 seconds! Under normal circumstances this gives the following exception:

[Win32Exception(0x80004005):等待操作超时]

[Win32Exception (0x80004005): The wait operation timed out]

[SqlException(0x80131904):超时已过期.在操作完成之前超时或服务器没有响应. System.Data.SqlClient.SqlConnection.OnError(SqlException异常，布尔值breakConnection，Action 1 wrapCloseInAction) +1753346 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action 1 wrapCloseInAction)+5295154 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj，布尔调用方HasConnectionLock，布尔asyncClose)+242 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream，BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject stateObj，Boolean& dataReady)+1682 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()+59 System.Data.SqlClient.SqlDataReader.get_MetaData()+90 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds，RunBehavior runBehavior，字符串resetOptionsString)+365 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior，RunBehavior runBehavior，布尔返回流，布尔异步，Int32超时，任务与任务，布尔异步写入)+1325 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior，RunBehavior runBehavior，布尔值returnStream，字符串方法，TaskCompletionSource`1完成，Int32超时，任务和任务，布尔值asyncWrite)+175 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior，RunBehavior runBehavior，Boolean returnStream，String方法)+53 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior行为，字符串方法)+134 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior行为)+41 System.Data.Common.DbCommand.ExecuteReader()+12 System.Data.Linq.SqlClient.SqlProvider.Execute(表达式查询，QueryInfo queryInfo，IObjectReaderFactory工厂，Object [] parentArgs，Object [] userArgs，ICompiledSubQuery []子查询，Object lastResult)+1306 System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(表达式查询，QueryInfo [] queryInfos，IObjectReaderFactory工厂，Object [] userArguments，ICompiledSubQuery []子查询)+118 System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq .Provider.IProvider.Execute(表达式查询)+342 System.Data.Linq.DataContext.ExecuteMethodCall(对象实例，MethodInfo methodInfo，Object []参数)+83

[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +1753346 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +5295154 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +242 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1682 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +59 System.Data.SqlClient.SqlDataReader.get_MetaData() +90 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +365 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) +1325 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +175 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +134 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41 System.Data.Common.DbCommand.ExecuteReader() +12 System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) +1306 System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) +118 System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) +342 System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters) +83

所有其他存储过程都以相同的方式调用，但是没有一个出现此问题.远程数据库管理员说，他可以在发生超时之前看到呼叫的开始和结束，因此这似乎与Linq接收数据之后的步骤有关.

All other stored procedures are called in the same way, but none have this issue. The remote DB Admin says he can see the call start and finish before the timeout occurs, so it seems to be something to do with the steps AFTER Linq receives the data.

任何人以前都经历过这个问题，并且有任何解决办法的想法吗?

Has anyone experienced this before and any ideas how to fix it?

我尝试从dmbl文件中删除SP并重新添加它.它注意到其中一个值从十进制变为双精度，但除此之外都一样.

I have tried removing the SP from the dmbl file and re-adding it. It noticed a change in one of the values from decimal to double, but apart from that its all the same.

一如既往，昨天一切正常！

As always, it was working fine yesterday!

谢谢.

推荐答案

好吧，我终于找到了解决该问题的真正方法. SSMS通常使用ARITHABORT ON，而代码通常使用ARITHABORT OFF-这基本上是在代码中的数学行出现错误时如何处理所发生情况的选项-例如除以零.

Ok, I finally discovered the REAL answer to this problem. SSMS typically uses an ARITHABORT ON and code typically uses ARITHABORT OFF - this is basically an option for how to handle what happens if a mathematical line in the code has an error - e.g. divide by zero.

但是，这里的主要要点是两种方法都有不同的执行计划-这就是为什么同一件事在网站上(随机)所花的时间比SSMS中要长得多的原因.

The main thing here, though, is that both methods have a different execution plan - which is why the same thing can (randomly) take a lot longer on the website than in SSMS.

执行计划是根据首次使用时的估计值进行编译的，因此您会随机发现，执行计划以一种非常适合您的第一个查询的方式缓存，但对于后续查询而言却是可怕的.这就是这里发生的事情，这也是为什么它突然突然又开始工作的原因-在更改存储过程之后创建了一个新的查询计划.

The execution plans are compiled based on estimates of the first time it is used so what you randomly find is that the execution plan is cached in a terrible way that suits your first query but is horrible for subsequent queries. This is what happened here and it is also why it just suddenly started working again - a new query plan was created after the stored procedure was changed.

最后，我们在存储过程中使用了WITH RECOMPILE-因此无法有效地重用执行计划，但是我们丝毫没有注意到任何差异，并且自此以后就没有发生问题.

In the end we used WITH RECOMPILE in the stored procedure - so there is no efficient re-use of the execution plan, but we didn't notice any difference anyway and the problem has not occurred since.

这篇关于Linq存储过程超时，但SSMS快速的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！