CODEWITHSUNDEEP
sql-serversql-execution-plan
Austin Arnett
Austin Arnett

Reputation: 117

Parameterized query creates an unfavorable execution plan. Optimize for parameters that are not NULL

As a forewarning, I'm working with a SQL query generated from entity framework, entity framework is irrelevant to the question though.

Some context: I am trying to pull specific records from a batch of 4,000 C# objects and perform an update or insert on them. I do not have the primary key of the records, for the objects come from an API, so I have to use a unique set of columns to pull the correct record.

The (simplified) queries and their execution plans:

Parameterized query (parameters are declared with a value set to 1 for demonstration purposes)

SELECT [x].[Id]
      FROM [Gradebook].[AssignmentScore] AS [x]
      WHERE
         ( ( ((([x].[CourseSectionAssignment_Id] = @__CSA_1) AND ([x].[Student_Id] = @__S_ID_1)) AND ([x].[AssessmentGBID] = @__A_GBID_1))
          OR ((([x].[CourseSectionAssignment_Id] = @__CSA_2) AND ([x].[Student_Id] = @__S_ID_2)) AND ([x].[AssessmentGBID] = @__A_GBID_2)) )
          OR ((([x].[CourseSectionAssignment_Id] = @__CSA_3) AND ([x].[Student_Id] = @__S_ID_3)) AND ([x].[AssessmentGBID] = @__A_GBID_3)) )

And it's (unfavorable) execution plan:

Parameterized query execution plan

Literal values query:

SELECT [x].[Id]
      FROM [Gradebook].[AssignmentScore] AS [x]
      WHERE
       ( ( ((([x].[CourseSectionAssignment_Id] = 1) AND ([x].[Student_Id] = 2)) AND ([x].[AssessmentGBID] = 3))
        OR ((([x].[CourseSectionAssignment_Id] = 4) AND ([x].[Student_Id] = 5)) AND ([x].[AssessmentGBID] = 6)) )
        OR ((([x].[CourseSectionAssignment_Id] = 7) AND ([x].[Student_Id] = 8)) AND ([x].[AssessmentGBID] = 9)) )

And it's (favorable) execution plan:

Execution plan of query with literal values

I know why, or at least I believe I know why, the execution plans are different. That is because the optimizer does not know whether the parameters will be NULL and must optimize around that case. Testing the literal query with NULLs creates the unfavorable execution plan. (Why wouldn't parameter sniffing see that the values are not null and create the better execution plan?)

Currently in the C# code, I am manually using expression trees to replace the object's properties with expression constants so that the generated query is the literal query. As far as I'm aware, literals force SQL server to generate a new execution plan each time, which isn't great.

I would like the parameterized query to generate the favorable execution plan. So far, the only answer I've found is using the OPTION(RECOMPILE) hint, which isn't exactly what I want, since it forces the execution plan to be recreated.

How can I use the same, favorable execution plan each time with the parameterized query?

Upvotes: 0

Views: 118

Answers (1)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

If you can't get around a bad index, in my experience, this will consistently produce a good execution plan.

SELECT [x].[Id]
FROM [Gradebook].[AssignmentScore] AS [x]
WHERE [x].[CourseSectionAssignment_Id] = 1
AND [x].[Student_Id] = 2
AND [x].[AssessmentGBID] = 3
UNION
SELECT [x].[Id]
FROM [Gradebook].[AssignmentScore] AS [x]
WHERE [x].[CourseSectionAssignment_Id] = 4
AND [x].[Student_Id] = 5
AND [x].[AssessmentGBID] = 6
UNION
SELECT [x].[Id]
FROM [Gradebook].[AssignmentScore] AS [x]
WHERE [x].[CourseSectionAssignment_Id] = 7
AND [x].[Student_Id] = 8
AND [x].[AssessmentGBID] = 9

Alternatively, you could create a temp table with the "valid" combinations and simply join to it on these three fields.

Upvotes: 1

Related Questions