实体框架4存储过程调用超时 [英] Entity Framework 4 Stored Procedure Call Timing Out

问题描述

我有一个导入到EF4中的存储过程，当我在30秒后使用某些参数调用它时，会引发超时错误。在SQL Server事件探查器中，我可以看到带有正确参数的存储过程调用仅花费了30秒以上，这是我的应用程序超时。

I have a stored procedure imported into EF4, and when I call it with certain parameters after 30 seconds it throws a time out error. In SQL Server profiler I can see the stored procedure call with the proper parameters taking just over 30 seconds, which is the timeout on my application.

但是，当我在查询分析器中执行发送到探查器的相同SQL时，它执行的时间不到一秒。是什么导致从EF调用和从SQL Server Management Studio调用之间的差异？

HOWEVER when I execute the same SQL sent to the profiler in Query Analyzer it executes sub-second. What could cause this discrepancy between being called from EF and being called from SQL Server Management Studio?

.NET错误的完整堆栈跟踪如下。

Full stack trace of .NET error bellow.

[SqlException（0x80131904）：超时已过期。在操作完成之前超时的时间
或服务器没有
响应。]

System.Data.SqlClient.SqlConnection.OnError（SqlException异常，
布尔breakConnection）+2073486

System.Data.SqlClient.SqlInternalConnection.OnError（SqlException
异常，布尔BreakConnection）+5064444

System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning （）+234

System.Data.SqlClient.TdsParser.Run（RunBehavior runBehavior，
SqlCommand cmdHandler，SqlDataReader dataStream，
BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject
stateObj）+2275

System.Data.SqlClient.SqlDataReader.ConsumeMetaData（）+33

System.Data.SqlClient.SqlDataReader.get_MetaData（）+86

System.Data .SqlClient.SqlCommand.FinishExecuteReader（SqlDataReader ds，
RunBehavior runBehavior，String resetOptionsString）+311

System.Data.SqlClient.SqlCommand.RunExecuteReaderTds（ CommandBehavior
cmdBehavior，RunBehavior runBehavior，布尔returnStream，布尔
异步）+987

System.Data.SqlClient.SqlCommand.RunExecuteReader（CommandBehavior
cmdBehavior，RunBehavior runBehavior，布尔returnStream，字符串
方法，DbAsyncResult结果）+162

System.Data.SqlClient.SqlCommand.RunExecuteReader（CommandBehavior
cmdBehavior，RunBehavior runBehavior，Boolean returnStream，字符串
方法）+32

System.Data.SqlClient.SqlCommand.ExecuteReader（CommandBehavior
行为，字符串方法）+141

System.Data.SqlClient.SqlCommand.ExecuteDbDataReader（CommandBehavior
行为）+12

System.Data.Common.DbCommand.ExecuteReader（CommandBehavior行为）
+10 System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands（EntityCommand
entityCommand ，CommandBehavior行为）+443

[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) +2073486
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5064444
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +86
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10 System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +443

[EntityCommandExecutionException：执行时发生错误使用
命令定义。有关详细信息，请参见内部异常。]

System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands（EntityCommand
EntityCommand，CommandBehavior行为）+479

System.Data.Objects。 ObjectContext.CreateFunctionObjectResult（EntityCommand
EntityCommand，EntitySetentitySet，EdmType edmType，MergeOption
mergeOption）+182

System.Data.Objects.ObjectContext.ExecuteFunction（String functionName，
MergeOption mergeOption，ObjectParameter []参数）+218

System.Data.Objects.ObjectContext.ExecuteFunction（字符串functionName，
ObjectParameter []参数）+53

MetaView。 DAL.MFCMData.MFCMDATAEntities.GetTradingOpenPositionCounterParty（Nullable 1
positionDT，Nullable 1 tradingAccountID）在
中C：\Projects\CASH\web＼ ＼MetaView\MetaView.DAL.MFCMData\MFCMData.Designer.cs：7064
MetaView.BusinessLayer.Shared.Accounts.CounterParties.GetCounterParties（Int32
tradingAccountID）在
中C：\Projects\CASH\web\MetaView\MetaView.BusinessLayer\Shared\Accounts\CounterParties.cs：161

[EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.]
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +479
System.Data.Objects.ObjectContext.CreateFunctionObjectResult(EntityCommand entityCommand, EntitySet entitySet, EdmType edmType, MergeOption mergeOption) +182
System.Data.Objects.ObjectContext.ExecuteFunction(String functionName, MergeOption mergeOption, ObjectParameter[] parameters) +218
System.Data.Objects.ObjectContext.ExecuteFunction(String functionName, ObjectParameter[] parameters) +53
MetaView.DAL.MFCMData.MFCMDATAEntities.GetTradingOpenPositionCounterParty(Nullable1 positionDT, Nullable1 tradingAccountID) in C:\Projects\CASH\web\MetaView\MetaView.DAL.MFCMData\MFCMData.Designer.cs:7064 MetaView.BusinessLayer.Shared.Accounts.CounterParties.GetCounterParties(Int32 tradingAccountID) in C:\Projects\CASH\web\MetaView\MetaView.BusinessLayer\Shared\Accounts\CounterParties.cs:161

推荐答案

所以几周前，我遇到了类似的问题，因此我们的一位DBA向我解释了这个问题（措辞并愚蠢到我课程级别）：

So I had a similar issue a couple of weeks ago, which was explained to me by one of our DBAs thusly (paraphrased and dumbed down to my level of course):

调用SQL Server存储过程时，服务器会为每个存储过程 per 创建并缓存执行计划object_id 。有时，SQL Server可能会根据传入的参数值创建错误的执行计划（在我们的示例中，对于可为空的参数，该计划为null）。发生这种情况时，快速的解决方法是在SQL Server Management Studio（或您可能使用的任何数据库管理工具）中运行 sp_recompile'Schema.Procedure'。所有这一切都清除了该存储过程的计划缓存。如果proc的下一个被调用者再次传递了 bad参数值，您将被困在相同的情况下，因此真正的解决方法是使用优化FOR 语法（请参见 http://msdn.microsoft.com/en -gb / library / ms181714.aspx ）。

When a SQL Server stored procedure is called, the server creates and caches an execution plan per stored procedure per object_id. Sometimes a bad execution plan can be created by SQL Server depending on the parameter value that is passed in (in our case it was null for a nullable parameter). When this happens, the quick fix is to run sp_recompile 'Schema.Procedure' in SQL Server Management Studio (or whatever DB management tool you might be using). All this does is clear the plan cache for that stored procedure. If the next callee of the proc passes in the "bad" parameter value again you'll be stuck in the same situation so the real fix is to give the query a hint using the OPTIMIZE FOR syntax (see http://msdn.microsoft.com/en-gb/library/ms181714.aspx).

简而言之，如果添加 OPTION（OPTIMIZE FOR（@myParameter ='Some为您提供良好执行计划的值'））在您的位置和/或 ORDER BY 子句应该可以解决问题。

In short if you add OPTION (OPTIMIZE FOR (@myParameter = 'Some value that gives you a GOOD execution plan')) after your WHERE and/or ORDER BY clauses it should fix the issue.

此外，如果您想知道为什么在SSMS中执行相同 SQL时，您总是很快结果是因为SSMS对于名为ARITHABORT（ SET ARITHABORT ON ）的选项的默认设置为ON，所有其他应用程序均默认设置为OFF，但其工作方式及其含义超出了我的经验，我也没有去研究它。有人告诉我，我不应该使用它。我相信真正的DBA可以更好地解释原因。

Also, if you're wondering why when executing the same SQL in SSMS you always get fast results it's because SSMS has a default setting of ON for an option called ARITHABORT (SET ARITHABORT ON) which all other apps have set to OFF by default, but how it works and its implications are beyond my experience and I haven't bothered to read up on it. I have been told that I shouldn't use it though. I'm sure a real DBA can do a better job of explaining why.

这篇关于实体框架4存储过程调用超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！