实体框架-存储过程的执行时间极长 [英] Entity Framework - extremely long execution time for stored procedure

问题描述

我昨天一直在跟踪我的问题，实体框架6从存储过程中获取复杂的返回值。我的存储过程现在在实体框架下运行。但是，它在3分钟后超时，连接超时。

I'm following up on my question yesterday, Entity Framework 6 get complext return value from a stored procedure. My stored procedure now runs under entity framework. However, it times out after 3 minutes, the connection time out.

我在SQL Server Management Studio中使用以下行运行存储过程（省略了客户信息）：

I run the stored procedure in my SQL Server Management Studio with the line (customer information omitted):

EXEC spGetDupWOs @ProjectName=N'...', @City=N'...', @State=N'LA', @ProposalNum=N'201703080740-001', @County=N'...', @Owner=N'...', @QuoteRecipients=N'...', @ProjectID=-1

它的执行时间不到一秒钟。

It executes in less than a second. When Entity framwork executes it, it takes forever.

使用SQL Server Profiler，我确定Entity Framework正在将该行发送到SQL Server：

Using the SQL Server Profiler, I determined that Entity Framework is sending this line to the SQL server:

exec sp_executesql N'EXEC spGetDupWOs',N'@ProjectName nvarchar(19),@City nvarchar(6),@State nvarchar(2),@ProjectNum nvarchar(12),@County nvarchar(10),@Owner nvarchar(23),@QuoteRecipients nvarchar(23),@ProjectID bigint',@ProjectName=N'...',@City=N'Holden',@State=N'LA',@ProposalNum=N'201703080740-001',@County=N'Livingston',@Owner=N'...',@BID_RECIP=N'...',@ProjectID=-1

当我在SSMS中运行此程序时，它将永远运行。

When I run this in SSMS, it takes forever to run.

阅读类似的问题，看起来像是参数嗅探和执行计划更改。

Reading the similar questions it looks like the issue is Parameter Sniffing and a change in execution plan.

这是我的电话，要求执行我的应用程序中的存储过程：

Here is my call to execute the stored procedure in my application:

            List<DuplicateProposals> duplicateCheckResults = 
                db.Database.SqlQuery<DuplicateProposals>("spGetDupWOs", 
                spl.ToArray())
                .ToList();

在网上阅读了很多文章之后，我感到更加困惑。如何更改呼叫以解决此问题？

After reading a bunch of articles online, I'm even more confused. How can I change my call to resolve this?

推荐答案

我最终不得不将整个呼叫转换为单个字符串，传递给SqlQuery函数。

I ended up having to convert the entire call into a single string that I passed to the SqlQuery function.

            string sql = string.Format("exec spGetDupWOs @ProjectName=N'{0}',@City=N'{1}',@State=N'{2}',@ProjectNumber=N'{3}',@County=N'{4}',@Owner=N'{5}',@QuoteRecipients=N'{6}',@ProjectID={7}", 
                project.ProjectName, 
                project.City,
                project.State,
                project.ProjectNumber,
                project.County,
                project.Owner,
                quoteRecipientsList, 
                "null");

是的，我必须在字符串中包含N前缀才能使其正常工作，我不是肯定为什么会这样。

Yes, I had to include the N prefix to the strings to make it work, I'm not sure why but it worked.

感谢大家的所有帮助。没有您的帮助，我无法解决这个问题。

Thanks for all of the help everyone. I could not have solved this without your help.

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