运行存储过程时的实体框架问题

I have an issue with stored procedures and Entity Framework.

我有存储过程和实体框架的问题。

Let me explain what is happening... and what I have tried thus far.

让我解释一下发生了什么......以及我到目前为止所尝试的内容。

I have a stored procedure, which does not do an awful lot

我有一个存储过程，它没有做太多

SELECT 
    COUNT(DISTINCT(EmailAddress)) AcceptedQuotes, 
    CONVERT (DATE,QuoteDate) QuoteDate
FROM
    Quote Q
JOIN 
    Person P on Q.PersonPk = P.Pk
JOIN 
    Product Pr on Q.ProductPk = Pr.Pk
JOIN 
    Accepted A on Q.Pk = A.QuotePk
WHERE               
    QuoteDate between @startDate and @endDate
    AND CompanyPk = @companyPk
    AND FirstName != 'Test'
    AND FirstName != 'test'
    AND FirstName != 'EOH'

I want to execute this, and it works fine in SSMS and does not even take 1 second.

我想执行它，它在SSMS中工作正常，甚至不需要1秒钟。

Now, I import this in to Entity Framework, it times out and I set the command timeout to 120...

现在，我将其导入到Entity Framework中，它超时并将命令超时设置为120 ...

Ok so what I have tried thus far and what I have tested.

那么我到目前为止所尝试的以及我测试的内容。

If I use SqlCommand, SqlDataAdapter, DataTable way, with my own connection string, it executes as expected. When I use Entity Framework connection string in this scenario, it times out.

如果我使用SqlCommand，SqlDataAdapter，DataTable方式，使用我自己的连接字符串，它会按预期执行。当我在这种情况下使用Entity Framework连接字符串时，它会超时。

I altered my stored procedure to include "Recompile" option and also tried the SET ARITHABORT way, no luck, it times out when run through the EF.

我修改了我的存储过程以包含“重新编译”选项并尝试了SET ARITHABORT方式，没有运气，它在通过EF运行时超时。

Is this a bug in EF?

这是EF的错误吗？

I have now just about decided to rewrite this using "old school" data access.

我现在刚刚决定使用“旧学校”数据访问来重写它。

Also note that the EF executes fine with other stored procs, from the same database.

另请注意，EF可以与来自同一数据库的其他存储过程一起执行。

Any ideas or help would be greatly appreciated...

任何想法或帮助将不胜感激......

PS. I found this article, but no help either :(

PS。我找到了这篇文章，但也没有帮助:(

http://www.sommarskog.se/query-plan-mysteries.html

1 个解决方案

#1

This may be caused by Parameter Sniffing

这可能是由参数嗅探引起的

When a stored procedure is compiled or recompiled, the parameter values passed for that invocation are "sniffed" and used for cardinality estimation. The net effect is that the plan is optimized as if those specific parameter values were used as literals in the query.

当编译或重新编译存储过程时，为该调用传递的参数值被“嗅探”并用于基数估计。最终效果是计划被优化，就好像这些特定参数值在查询中用作文字一样。

Using dummy variables that are not directly displayed on parameters also ensure execution plan stability without need to add recompile hint, example below:

使用未直接显示在参数上的虚拟变量也可确保执行计划的稳定性，而无需添加重新编译提示，如下所示：

create procedure dbo.SearchProducts @Keyword varchar(100) As Declare @Keyworddummy as varchar(100) Set @Keyworddummy = @Keyword select * from Products where Keyword like @Keyworddummy

创建过程dbo.SearchProducts @Keyword varchar（100）As声明@Keyworddummy为varchar（100）设置@Keyworddummy = @Keyword select * from Products where Keyword like @Keyworddummy

To prevent this and other similar situations, you can use the following query option:

要防止出现此类情况和其他类似情况，可以使用以下查询选项：

OPTIMIZE FOR RECOMPILE

优化重建

Disable auto-update statistics during the batch

批处理期间禁用自动更新统计信息

#1

This may be caused by Parameter Sniffing

这可能是由参数嗅探引起的

Using dummy variables that are not directly displayed on parameters also ensure execution plan stability without need to add recompile hint, example below:

使用未直接显示在参数上的虚拟变量也可确保执行计划的稳定性，而无需添加重新编译提示，如下所示：

create procedure dbo.SearchProducts @Keyword varchar(100) As Declare @Keyworddummy as varchar(100) Set @Keyworddummy = @Keyword select * from Products where Keyword like @Keyworddummy

创建过程dbo.SearchProducts @Keyword varchar（100）As声明@Keyworddummy为varchar（100）设置@Keyworddummy = @Keyword select * from Products where Keyword like @Keyworddummy

To prevent this and other similar situations, you can use the following query option:

要防止出现此类情况和其他类似情况，可以使用以下查询选项：

OPTIMIZE FOR RECOMPILE

优化重建

Disable auto-update statistics during the batch

批处理期间禁用自动更新统计信息

秒客网

运行存储过程时的实体框架问题

1 个解决方案

#1

#1

相关文章