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 request takes about 10 seconds.

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

This request takes less than 1 second.

I just found out that EntityFramework generates two different requests.

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

Request 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 or other way to do this?

Answer 1

imho, the sql queries seen are not an (only) EF-related performance issue, but a SQL server performance issue.

You should consider the following:

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

btw: how long does request 1 in SSMS take?

Answer 2

I have found a solution.

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

Thanks for your help.

R.