Optimizing dateTime on linq query

Question

I am facing a performance problem.

When I run this query with dateTime declared inside this query:

var temp = (from p in db.BEM_EVT_FULL
                      where (p.date_reception > new DateTime(2015,01,01))
                      group p by p.mc_object into g
                      orderby g.Count() descending
                      select new StringIntType
                      {
                          str = g.Key,
                          nbr = g.Count()}).Take(50).ToList();

In sql server profiler it is translated to that query

SELECT TOP (50) 
[Project1].[C3] AS [C1], 
[Project1].[mc_object] AS [mc_object], 
[Project1].[C2] AS [C2]
FROM ( SELECT 
    [GroupBy1].[A1] AS [C1], 
    [GroupBy1].[A2] AS [C2], 
    [GroupBy1].[K1] AS [mc_object], 
    1 AS [C3]
    FROM ( SELECT 
        [Extent1].[mc_object] AS [K1], 
        COUNT(1) AS [A1], 
        COUNT(1) AS [A2]
        FROM [dbo].[BEM_EVT_FULL] AS [Extent1]
        WHERE [Extent1].[date_reception] > convert(datetime2, '2015-01-01 00:00:00.0000000', 121)
        GROUP BY [Extent1].[mc_object]
    )  AS [GroupBy1]
)  AS [Project1]
ORDER BY [Project1].[C1] DESC

And it works just fine it get executed under 1s.

Now when I declare DateTime outside query and I run this query:

DateTime dt = new DateTime(2015,01,01);

        var temp = (from p in db.BEM_EVT_FULL
                      where (p.date_reception > dt)
                      group p by p.mc_object into g
                      orderby g.Count() descending
                      select new StringIntType
                      {
                          str = g.Key,
                          nbr = g.Count()
                      }).Take(50).ToList();

In sql server profiler it is translated to that query

exec sp_executesql N'SELECT TOP (50) 
[Project1].[C3] AS [C1], 
[Project1].[mc_object] AS [mc_object], 
[Project1].[C2] AS [C2]
FROM ( SELECT 
    [GroupBy1].[A1] AS [C1], 
    [GroupBy1].[A2] AS [C2], 
    [GroupBy1].[K1] AS [mc_object], 
    1 AS [C3]
    FROM ( SELECT 
        [Extent1].[mc_object] AS [K1], 
        COUNT(1) AS [A1], 
        COUNT(1) AS [A2]
        FROM [dbo].[BEM_EVT_FULL] AS [Extent1]
        WHERE [Extent1].[date_reception] > @p__linq__0
        GROUP BY [Extent1].[mc_object]
    )  AS [GroupBy1]
)  AS [Project1]
ORDER BY [Project1].[C1] DESC',N'@p__linq__0 datetime2(7)',@p__linq__0='2015-01-01 00:00:00'

Which take around 5mn to get executed, all that difference because of DateTime declaration. What changes should I make to declare DateTime outside query, and get same performance as the first query?

any clue from http://stackoverflow.com/questions/3710523/sql-server-sp-executesql-and-execution-plans ? — tschmit007, Apr 16 '15 at 09:34
Is there an index on `[date_reception]` in the database? What about `[mc_object]`? — D Stanley, Apr 16 '15 at 15:06
Task a look at this article: http://dba.stackexchange.com/questions/15262/why-does-a-query-run-slower-in-a-stored-procedure-than-in-the-query-window — Ryan Amies, Apr 16 '15 at 15:31

score 1 · Answer 1 · edited May 23 '17 at 11:52

I guess you have many rows in your table and the value 2015-01-01 00:00:00 provides enough filtering to dismiss many rows (say 8 out of 10), and is worth using the corresponding index.

With the variable, the query optimizer does not know if the variable will provide enough filtering, compared to a simple table scan. It might judge it is not worth using the index ( especially if the index has an INCLUDE clause not relevant for the query. According to your previous question, it may be the case)

It may also have generated a bad query plan because of "parameter sniffing"

Anyway, you may try to introduce a forced "OPTION RECOMPILE" using a dedicated query interceptor. See https://stackoverflow.com/a/26762756/1236044

score 0 · Answer 2 · answered Apr 16 '15 at 13:26

0

The reason for this (I believe) is that in the first scenario, the DateTime is a compile time constant, however in the second it is not, and so EF has to pass it as a parameter.

answered Apr 16 '15 at 13:26

Ryan Amies

4,902
1
21
36

I Imagine because the first query plan is cached, whilst the second cannot be cached. – Ryan Amies Apr 16 '15 at 15:30
I don't think `DateTime`s can be compile time constants. – Asad Saeeduddin Apr 16 '15 at 15:52

score 0 · Answer 3 · answered Apr 16 '15 at 15:12

My guess is that with the constant value, the SQL compiler knows how to create the most efficient plan, but with a parameter, it doesn't, so it has to guess, any may guess badly. Since the first query only returns 50 records and still takes almost a minute to run I think adding indexes (or reindexing if indexes already exist) on [date_reception] and [mc_object] should help. If those don't work you may be forced to build the SQL manually rather than using Linq since you don't have much control over the SQL that Linq generates.

Before punting you might also have a look at the execution plans (and maybe repost on http://dba.stackexchange.com). Also reindex your tables and see if that doesn't help. — D Stanley, Apr 16 '15 at 15:27

George Polevoy · Answer 4 · 2015-05-12T20:55:23.170

0

Try to figure out the difference in the execution plans. In this experiment it's different due to the fact that the column is declared as datetime and the comparand is declared differently. In one case its an inline statement and in the other its a declared variable. If you declare the field as datetime2 theres no difference in the execution plan.

edited May 12 '15 at 20:55

answered May 12 '15 at 19:25

George Polevoy

7,450
3
36
61

Optimizing dateTime on linq query

4 Answers4