EntityFramework 6.1.1 with Linq Performance issue

Question

I am using EntityFramework to select data from my mssql database. My query looks something like this:

int param = 123456;
using (var context = new DatabaseContext())
{
    var query = context.Table.AsQueryable();
    var result = query.Where(o => o.Id == param).ToList();
}

This query takes about 10 seconds.

using (var context = new DatabaseContext())
{
    var query = context.Table.AsQueryable();
    var result = query.Where(o => o.Id == 123456).ToList();
}

This query takes less than 1 second.

I just found out that EntityFramework generates two different queries.

Query 1:

SELECT TOP (20) 
[Project1].[Id] AS [Id], 
[Project1].[Name] AS [Name],  
FROM ( SELECT [Project1].[Id] AS [Id], [Project1].[Name] AS [Name], row_number() OVER (ORDER BY [Project1].[Id] DESC) AS [row_number]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name]
        FROM [dbo].[Table] AS [Extent1]
        WHERE [Extent1].[Id] = @p__linq__0
    )  AS [Project1]
)  AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[Id] DESC
-- p__linq__0: '2932323' (Type = Int32, IsNullable = false)

Query 2:

SELECT TOP (20) 
[Filter1].[Id] AS [Id], 
[Filter1].[Name] AS [Name]
FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], row_number() OVER (ORDER BY [Extent1].[Id] DESC) AS [row_number]
    FROM [dbo].[Table] AS [Extent1]
    WHERE 2932323 = [Extent1].[Id]
)  AS [Filter1]
WHERE [Filter1].[row_number] > 0
ORDER BY [Filter1].[Id] DESC

Is there a way to speed up the first one or another way to do it?

Answer 1

imho, seen the sql queries, this is not (only) an EF related performance problem, but an SQL server performance problem.

You should considers the following:

• as Id is not a PK: create an index on it;
• use EXEC sp_updatestats to update the statistics and help the db server to produce better execution plans.

btw: how long takes query 1 in SSMS ?

Answer 2

I found the solution.

I dropped all execution plans using sp_recompile and now all is working fine.

Thx for your help.

P.