7

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

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?

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

Community
  • 1
  • 1
Mike
  • 7,500
  • 8
  • 44
  • 62

2 Answers2

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.

Darren
  • 68,902
  • 24
  • 138
  • 144
  • 1
    Thank you for your answer, but unfortunately I'm looking for a way to add hints into the query that EF generates, not a stored procedure. – Mike Mar 27 '12 at 13:49
  • 1
    I've had a quick google and there is nothing obvious for your problem with EF, other ORMS such as NHibernate allow query hints. These posts below are the closest solution I can find to your problem: http://stackoverflow.com/questions/8031069/how-can-i-specify-an-index-hint-in-entity-framework http://stackoverflow.com/questions/926656/entity-framework-with-nolock – Darren Mar 27 '12 at 14:00
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

Community
  • 1
  • 1
Alireza
  • 5,421
  • 5
  • 34
  • 67
  • 1
    Does anybody have an actual EF example of this? – Matt Burland Nov 05 '14 at 16:01
  • Can you explain your scenario? I faced this issue a long time ago and the final solution was around it, not through. – Alireza Nov 05 '14 at 19:11
  • 1
    I came up with a different solution based on interceptors which are, I believe, a newer feature in EF (probably didn't exist at the time of your answer). See: http://stackoverflow.com/questions/26761827/adding-a-query-hint-when-calling-tvf – Matt Burland Nov 05 '14 at 19:14