如何在实体框架中控制参数嗅探和/或查询提示？

Update: I've created a suggestion to implement hint control in a future version of EF. Go here to vote for it.

更新:我已经创建了一个建议,以便在未来的EF版本中实现提示控制。去这里投票吧。

I have a problem where one of my Entity Framework (EF) queries is taking a very long time to execute in Sql Server, although when I copy-and-paste the generated TSQL into Sql Server Management Studio (SSMS) it runs extremely fast. After some investigation I found that I was experiencing a parameter sniffing issue, and the correct way to fix it is to insert one of many query hints (OPTIMIZE FOR, RECOMPILE, and so on). How do I insert these hints into my EF queries?

我有一个问题,我的一个实体框架(EF)查询在Sql Server中执行需要很长时间,尽管当我将生成的TSQL复制并粘贴到Sql Server Management Studio(SSMS)时,它运行得非常快。经过一些调查后,我发现我遇到了参数嗅探问题,正确的解决方法是插入许多查询提示之一(OPTIMIZE FOR,RECOMPILE等)。如何将这些提示插入到我的EF查询中?

Related questions coming at this from different perspectives are here, here, and here.

从不同角度出现的相关问题在这里,这里和这里。

2 个解决方案

#1

To apply a hint on a query generate by EF, you should use plan guides, more info here: One to one join Not fast enough in SQL Server

要对由EF生成的查询应用提示,您应该使用计划指南,此处有更多信息:一对一连接在SQL Server中不够快

#2

If you are executing stored procedures you could declare the parameters of the stored procedure internally.

如果您正在执行存储过程,则可以在内部声明存储过程的参数。

I.e.

CREATE PROCEDURE sp_test
(
     @param1     NVARCHAR(10),
     @param2     INT
)

AS

DECLARE @internalParam1 NVARCHAR(10)
DECLARE @internalParam2 INT

SET @internalParam1 = @param1
SET @internalParam2 = @param2

-- REST OF YOUR QUERY

GO

This will stop SQL Server caching any parameters that are being passed to the SP.

这将阻止SQL Server缓存传递给SP的任何参数。

#1