Stored procedure is not using indexes

Question

This stored procedure is the result of a linq query execution: I got it from the Sql Server Profiler

exec sp_executesql N'SELECT 
[Limit1].[C2] AS [C1], 
[Limit1].[object] AS [object], 
[Limit1].[C1] AS [C2]
FROM ( SELECT TOP (50) 
    [GroupBy1].[A1] AS [C1], 
    [GroupBy1].[K1] AS [object], 
    1 AS [C2]
    FROM ( SELECT 
        [Extent1].[object] AS [K1], 
        COUNT(1) AS [A1]
        FROM [dbo].[BEM_EVT_FULL] AS [Extent1]
        WHERE [Extent1].[date] > @p__linq__0
        GROUP BY [Extent1].[object]
    )  AS [GroupBy1]
)  AS [Limit1]',N'@p__linq__0 datetime2(7)',@p__linq__0='2015-01-21 00:00:00'

I have created indexes on all the columns (object and date). The problem is that this query is totaly ignoring indexes and takes more than 3 minutes to run.

On the other hand I have this query that I've run manually.

SELECT 
[Limit1].[C2] AS [C1], 
[Limit1].[object] AS [object], 
[Limit1].[C1] AS [C2]
FROM ( SELECT TOP (50) 
    [GroupBy1].[A1] AS [C1], 
    [GroupBy1].[K1] AS [object], 
    1 AS [C2]
    FROM ( SELECT 
        [Extent1].[object] AS [K1], 
        COUNT(1) AS [A1]
        FROM [dbo].[BEM_EVT_FULL] AS [Extent1]
        WHERE [Extent1].[date] > convert(datetime2, '2015-01-21 00:00:00.0000000', 121)
        GROUP BY [Extent1].[object]
    )  AS [GroupBy1]
)  AS [Limit1]

This query benefits from indexes and run under 1s. Does anyone have an idea why this is?

have you proved this by looking at the plan? and is the timing from SQL profiler or your .NET code? — Steve Drake, Apr 17 '15 at 15:56
Parameter sniffing? Check the plan and see what value it was compiled with (properties of the first thing on the left) — James Z, Apr 17 '15 at 16:09
How do you know it is ignoring the indexes? If it is just execution time I agree with @JamesZ that this is likely a case of parameter sniffing. — Sean Lange, Apr 17 '15 at 16:14
As an aside, [this may help](http://stackoverflow.com/q/9270490/61305). — Aaron Bertrand, Apr 17 '15 at 16:25
@SteveDrake i've proved this by looking at sql server profiler — drex drex, Apr 17 '15 at 19:05
When it's running you can use something like this to get the plan: "SELECT qp.* FROM sys.dm_exec_requests r cross apply sys.dm_exec_query_plan(r.plan_handle) qp WHERE session_id = XXX" where XXX is the spid / session id. — James Z, Apr 17 '15 at 19:11
I have checked stored procedures in the DB there was none of them — drex drex, Apr 20 '15 at 07:23

score 0 · Answer 1 · edited May 23 '17 at 12:22

0

I feel like adding OPTION(RECOMPILE) will help you. The problem is how do you the Entity Framework to do that. Check out this link Adding a query hint when calling Table-Valued Function

exec sp_executesql 
    N'SELECT    [Limit1].[C2] AS [C1], 
                [Limit1].[object] AS [object], 
                [Limit1].[C1] AS [C2]
    FROM 
    (
        SELECT TOP (50) [GroupBy1].[A1] AS [C1], 
                        [GroupBy1].[K1] AS [object], 
                        1 AS [C2]
        FROM 
        (
            SELECT 
            [Extent1].[object] AS [K1], 
            COUNT(1) AS [A1]
            FROM [dbo].[BEM_EVT_FULL] AS [Extent1]
            WHERE [Extent1].[date] > @p__linq__0
            GROUP BY [Extent1].[object]
        )  AS [GroupBy1]
    )  AS [Limit1]
    OPTION (RECOMPILE)',

    N'@p__linq__0 datetime2(7)',
    @p__linq__0='2015-01-21 00:00:00'

edited May 23 '17 at 12:22

Community

1
1

answered Apr 17 '15 at 16:13

Stephan

5,891
1
16
24

1

The OP doesn't control the text of the query; it's generated by that icky Entity Framework thing [that doesn't do a very good job](http://blogs.msdn.com/b/bindeshv/archive/2010/07/13/ef-query-execution-pattern-usage-of-sp-executesql-vs-direct-execution-of-sql-statement.aspx). – Aaron Bertrand Apr 17 '15 at 16:25
Thanks for the tip. I edited my answer with a link to someone who managed to do it. – Stephan Apr 17 '15 at 16:42
3

Ugh, that is ugly. I wonder if all the ugly workarounds and hacks you have to perform to make EF behave properly are worth any so-called advantages... – Aaron Bertrand Apr 17 '15 at 16:44
I think i'm having parameter sniffing issue, withe the dateTime passed in where statement – drex drex Apr 17 '15 at 19:10
@Stephan are you sure we put OPTION(RECOMPILE) at that statement ? – drex drex Apr 20 '15 at 06:58
I don't know. I thought you would run the script in my answer and see if it corrects your problem. Then if it works, I thought you would figure out how to add OPTION(RECOMPILE) to your entity script from the link I found. – Stephan Apr 20 '15 at 13:37

Stored procedure is not using indexes

1 Answers1