Entity Framework 6 - Parameter Query 11x Slower Than Inline Parameters Query. Why?

Profiling some queries in our product, I found that use of Entity Framework 6 parameters impact on performance with this one query. There are many topics on this, both with differe

Solution 1:

The problem is that Entity Framework generates parameters of type DateTime2 while the actual database columns are defined as DateTime. There are two solutions:

Either change your database columns to DateTime2 or tell Entity Framework to use DateTime instead (see here).

Solution 2:

I had a case where I was optionally including an expensive text field.

So the generated code had something like

WHEN@includeExpensiveField = 1 THEN [o].[ExpensiveField] ELSE NULL

So in SSMS when I ran the query manually I'd just change this to

WHEN0 = 1

and it completely optimized that field out.

However the parameterized query had to account for it in the plan and when I found the query in SSMS > Query Store I could see it was always scanning and loading the expensive field because it couldn't optimize it out.

Note: I used this code

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.* FROM sys.query_store_plan AS Pl INNER JOIN sys.query_store_query AS Qry ON Pl.query_id = Qry.query_id INNER JOIN sys.query_store_query_text AS Txt ON Qry.query_text_id = Txt.query_text_id where query_sql_text not like '%expensivefield%' order by last_execution_time desc

to find the query_id used and then found the actual executed query plan in SSMS > Query Store > Tracked Queries