5秒SP通过LINQ-to-SQL达到30秒超时 [英] A 5sec SP hitting a 30sec timeout through LINQ-to-SQL

问题描述

我有一个可以通过SSMS在5秒钟内执行的SP

I have a SP that executes in 5 seconds through SSMS

通过LINQ-to-SQL excel插件执行相同的SP时，它会在30秒后超时(对该相同SP的简单查询会花费很长时间，但会返回结果)

When that same SP is executed through a LINQ-to-SQL excel add-in it times out after 30 seconds (simpler queries for that same SP take a long time but return results)

然后我更改了SP，以便将所有输入参数重新分配给SP内部的新本地参数.如此一来，SP在SSMS中的运行时间为36秒(因此，SSMS如此之快的开始是有原因的)

I then changed the SP so that it reassigns all the input parameters to new local parameters inside the SP. That made the SP run in 36seconds in SSMS (so there is the reason why SSMS was so fast to begin with)

所以我猜测SQL Server在我的LINQ-to-SQL查询中没有使用参数嗅探吗?

So I'm guessing that SQL server isn't making use of parameter sniffing for my LINQ-to-SQL queries?

所以，我的问题是，有什么方法可以使LINQ-to-SQL中的SP与SSMS中的SP一样快(带有参数嗅探功能)

So, my question is, is there any way to make the SP as fast in LINQ-to-SQL as it is in SSMS (with it's parameter sniffing)

推荐答案

无论您从SSMS还是LINQ调用SQL Server，SQL Server都以相同的方式优化存储过程.但是它确实使用计划缓存.存储计划以供以后使用相同的登录名+ ansi设置重复使用.传入的第一个值可以确定计划的外观.如果不同的登录名/设置以不同的值开头，则可能导致不同的缓存计划.这是LINQ和SSMS之间性能差异的一种解释.

SQL Server optimizes stored procedures the same way whether you call them from SSMS or from LINQ. But it does use plan caching. A plan is stored for later reuse with the same login + ansi settings. The first values passed in can determine how the plan looks. If a different login/settings starts with different values, that can result in a different cached plan. That's one explanation for performance differences between LINQ and SSMS.

要重置所有缓存的计划，请使用:

To reset all cached plans, use:

DBCC FREEPROCCACHE

为了使SP精确地优化您要使用的值，可以使用

In order to have the SP optimized for exactly the values you're calling with, you could use with recompile:

create procedure dbo.MySP with recompile as ...

这将导致为每个调用编译该过程.这样会否定参数化.

This causes the procedure to be compiled for every invocation. This would negate parameterization.

(您的情况相当不寻常.SQLServer可以选择强制参数化，但是没有阻止它的选项.)

(Your situation is rather unusual. SQL Server has an option to force parameterization, but there's no option to prevent it.)

这篇关于5秒SP通过LINQ-to-SQL达到30秒超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！