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?

Could you show the queries that get generated for each one? Also, why aren't you querying the `context` directly? Why use `AsQueryable()`? — Yuval Itzchakov, May 18 '15 at 14:22
@haim770 Redundant, yes, but not problematic or harmful though. — Servy, May 18 '15 at 14:23
the one with int parameter becomes a func<> instead of expression as the other one — Thorarins, May 18 '15 at 14:25
@Servy: That's right, but why would one do that? That's right, but why would one do that? — Rob, May 18 '15 at 14:25
@Andreas Well, it's not in the code shown. If it was out of scope in the real code then it wouldn't compile, rather than being slow. — Servy, May 18 '15 at 14:26
Does your program execute any EF code before that? EF takes some time for first query for initialization, so if your first sample is the first query that program executes, then it can be a reason for 10 seconds — Sergey Litvinov, May 18 '15 at 14:26
@Andreas Now the comment is still wrong; both of the queries in the code shown will compile into expressions, not delegates. — Servy, May 18 '15 at 14:28
http://stackoverflow.com/questions/793571/why-would-you-use-expressionfunct-rather-than-funct , are you sure? — Thorarins, May 18 '15 at 14:28
Maybe this is related: https://social.msdn.microsoft.com/Forums/en-US/fbd18f3c-8dfa-49a6-b57a-685a5f0d4098/linq-to-entities-with-variable-substitution-slow?forum=adodotnetentityframework — L-Four, May 18 '15 at 14:30
imho this is about parameter sniffing. Try `EXEC sp_updatestats` on the database — tschmit007, May 18 '15 at 14:31
It looks like you are referencing a ID. Is that a Primary Key? If so use FirstOrDefault(); — jackncoke, May 18 '15 at 14:32
I've already seen the MSDN article but Id and param are the same datatype — Patrick Oberbacher, May 18 '15 at 14:32

score 2 · Accepted Answer · answered May 18 '15 at 14:44

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 ?

score 2 · Answer 2 · answered May 18 '15 at 14:51

2

I found the solution.

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

Thx for your help.

P.

answered May 18 '15 at 14:51

Patrick Oberbacher

75
7

and the Query is still diffrent? the one with parameter is still slower since EF produces weird sql – Thorarins May 18 '15 at 14:53
the queries are still the same. but both take the same amount of time – Patrick Oberbacher May 18 '15 at 14:54

EntityFramework 6.1.1 with Linq Performance issue

2 Answers2