Entity Framework 6 - Parameter query 11x slower than inline parameters query. Why?

c# entity-framework-6 linq linq-to-entities sql-server

Question

When I profiled several of our product's queries, I discovered that this particular query's speed was impacted by the usage of Entity Framework 6 parameters. There are several subjects on this, all of which have opposing viewpoints.

With the exception of the fact that I have included the parameters inline with the SQL in my test case, these two queries are identical.

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)'

Using the output statistics:

 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.

Although the final query execution plan still accesses the same data, it is somewhat different.

enter image description here

The following prompts my questions:

  1. Why would the same query run over 11 times slower (from 6 seconds to.5 seconds) when just expressed with parameters? The findings are based on the same Index.

  2. How can I force inline arguments in entity Framework? A few months ago, I saw an other member raise the same question on SO, but they got no answer. Although I'm not even sure whether that is the correct response, I'd want to try it. Raw SQL won't be written inline. Entity Framework is where it needs to come from.

1
0
6/22/2017 9:47:22 PM

Accepted Answer

ZZZ_tmp
1
6/22/2017 10:29:41 PM

Popular Answer

In one instance, I accidentally included a pricey text field (optionally).

Consequently, the produced code looked like

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

Consequently, in SSMS, I would just update this to

WHEN 0 = 1

and that field was entirely optimised.

When I discovered the query in SSMS > Query Store, I could see it was constantly scanning and loading the pricey field since it couldn't be optimised away. The parameterized query, however, had to account for it in the plan.

Remark: I applied 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 identifyquery_id Using SSMS > Query Store > Tracked Queries to find the query plan that was actually run.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow