Entity Framework 6 - Parameter query 11x slower th

问题:

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 difference of opinions.

In my testing case, these two queries are identical, with the exception that I have placed the params inline the SQL.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS TIME ON 

exec sp_executesql N'SELECT DISTINCT 
    [Extent1].[POSTerminalID] AS [POSTerminalID]
    FROM [dbo].[POSDataEvents] AS [Extent1]
    WHERE ([Extent1].[DataTimeStamp] <= @p__linq__0) AND ([Extent1].[DataTimeStamp] >= @p__linq__1) AND ([Extent1].[DataOwnerID] = @p__linq__2)
',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 smallint',@p__linq__0='2017-06-22 16:16:01.3570000',@p__linq__1='2017-04-23 04:00:00',@p__linq__2=1

exec sp_executesql N'SELECT DISTINCT 
    [Extent1].[POSTerminalID] AS [POSTerminalID]
    FROM [dbo].[POSDataEvents] AS [Extent1]
    WHERE ([Extent1].[DataTimeStamp] <= ''2017-06-22 16:16:01'') AND ([Extent1].[DataTimeStamp] >= ''2017-04-23 04:00:00'') AND ([Extent1].[DataOwnerID] = 1)'

With the output stats:

 SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(289 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 11859 ms,  elapsed time = 5827 ms.

 SQL Server Execution Times:
   CPU time = 11859 ms,  elapsed time = 5828 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(289 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 6221 ms,  elapsed time = 509 ms.

 SQL Server Execution Times:
   CPU time = 6221 ms,  elapsed time = 509 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

The resulting query execution plan is also slightly different, but still accessing the same tables.

This brings me to my questions:

1. Why would the same query, just represented with parameters be nearly 11 times slower (~6 seconds to .5 seconds)? The same Index is used for the results.

2. How in entity Framework can I force inline parameters? I have seen another user ask on SO a few months back from this post with no response. I am not sure if that is even the right answer, but would like to test that. We wont be writing raw SQL inline. It has to come from Entity Framework.

回答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).

回答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

WHEN 0 = 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