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?

Answer 1

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