Entity Framework include poor performance

Question

Context

We appear to be having an Entity Framework 6.x related issue. We've spent weeks attempting to nail down performance issues and fixed most if not all which we can find/think of. In short, we are seeing a massive drop in performance when utilizing Include.

Utilizing EFCache.
Enabled db model caching running EF 6.2.
Making use of cached views.
Using a context without Lazy Loading where possible utilizing the correct (and minimal) includes.
Using AsNoTracking for read-only data.
Using a context without proxy generation nor autodetect changes (though the latter appears to be a minimal improvement).
Context lifetime is minimal, single query within using blocks where possible.
Clean constructors on the objects so EF should experience minimal overhead in mapping the data to objects.
"Async all the way" which certainly improved responsiveness, but does not reduce the work being done.

Current (presumed) issues we still have that could have an impact:

Primary key, clustered GUIDs.
Table per type hierarchy, with Entity being part of it, causing double joins.

Most of the related topics we've already burned through, without much effect. As far as we can tell, the database is 'okay'. Utilizing a log4net interceptor before the queries hit the database, we found out that although some of our queries with 3-7 includes are monstrous, they aren't abysmally slow: times ranging from 0ms to 100ms. It tends to be 2000ms up to 8000ms until the objects are 'ready' for use though.

We have at most 50.000 entities currently in our database. However, even with a near-clean database, there is minimal difference.

Code

(Simplified, extracted) model structure:

public class Entity
{
    public virtual Guid Id { get; set; }
    public virtual long Version { get; set; }
    public virtual string EntityType { get; set; }
}

public class User : Entity
{
    public virtual Guid Id { get; set; }
    public virtual string Username { get; set; }
    public virtual string Password { get; set; }

    public virtual Person Person { get; set; }
}

public class Person : Entity
{
    public virtual Guid Id { get; set; }
    public virtual DateTime DateOfBirth { get; set; }
    public virtual string Name { get; set; }

    public virtual Employee Employee { get; set; }
}

public class Employee : Entity
{
    public virtual Guid Id { get; set; }
    public virtual string EmployeeCode { get; set; }
}

(Simplified) slow query. Monitoring by wrapping a Stopwatch indicates an average duration of two seconds, but the query itself lists only a few ms in log4net's generated log file:

var userId = .... // Obtained elsewhere
using (var context = new DbContext())
{
    var user =
        context.Set<User>()
            .Include(u => u.Person.Employee)
            .FirstOrDefault(u => u.Id == userId);
}

We've tried alternative approaches:

context.Set<User>().Where(u => u.Id == userId).Load();
context.Set<Person>().Where(p => p.User.Id == userId).Load();
context.Set<Employee>().Where(e => e.Person.User.Id == userId).Load();

var user = context.Set<User>().Local.FirstOrDefault(u => u.Id == userId);

Summary

Based on the provided information, does anyone see a clear issue which we may have missed, or otherwise have suggestions for things we could try?

Could the fact that we still have the two aforementioned 'issues' present dether EF from constructing the objects in a semi-fast way?

Perhaps relevant, using Find(userId) instead of FirstOrDefault blocks and does not appear to finish within a reasonable period.

Update 1

In response to @Ivan Stoev - running the above query ran for 98ms (2968ms) and generated the following (full) SQL statement:

SELECT 
    [Limit1].[CheckSum] AS [CheckSum], 
    [Limit1].[C1] AS [C1], 
    [Limit1].[Id] AS [Id], 
    [Limit1].[Version] AS [Version], 
    [Limit1].[EntityType] AS [EntityType], 
    [Limit1].[Deleted] AS [Deleted], 
    [Limit1].[UpdatedBy] AS [UpdatedBy], 
    [Limit1].[UpdatedAt] AS [UpdatedAt], 
    [Limit1].[CreatedBy] AS [CreatedBy], 
    [Limit1].[CreatedAt] AS [CreatedAt], 
    [Limit1].[LastRevision] AS [LastRevision], 
    [Limit1].[AccessControlListId] AS [AccessControlListId], 
    [Limit1].[EntityStatus] AS [EntityStatus], 
    [Limit1].[Username] AS [Username], 
    [Limit1].[Password] AS [Password], 
    [Limit1].[Email] AS [Email], 
    [Limit1].[ResetHash] AS [ResetHash], 
    [Limit1].[Flag] AS [Flag], 
    [Limit1].[CryptoKey] AS [CryptoKey], 
    [Limit1].[FailedPasswordTries] AS [FailedPasswordTries], 
    [Limit1].[LastPasswordTry] AS [LastPasswordTry], 
    [Limit1].[UXConfigId] AS [UXConfigId], 
    [Limit1].[LastActivity] AS [LastActivity], 
    [Limit1].[C2] AS [C2], 
    [Limit1].[C3] AS [C3], 
    [Limit1].[C4] AS [C4], 
    [Limit1].[C5] AS [C5], 
    [Limit1].[C6] AS [C6], 
    [Limit1].[C7] AS [C7], 
    [Limit1].[C8] AS [C8], 
    [Limit1].[C9] AS [C9], 
    [Limit1].[C10] AS [C10], 
    [Limit1].[C11] AS [C11], 
    [Limit1].[C12] AS [C12], 
    [Limit1].[C13] AS [C13], 
    [Limit1].[C14] AS [C14], 
    [Limit1].[C15] AS [C15], 
    [Limit1].[C16] AS [C16], 
    [Limit1].[Id1] AS [Id1], 
    [Limit1].[Version1] AS [Version1], 
    [Limit1].[EntityType1] AS [EntityType1], 
    [Limit1].[Deleted1] AS [Deleted1], 
    [Limit1].[UpdatedBy1] AS [UpdatedBy1], 
    [Limit1].[UpdatedAt1] AS [UpdatedAt1], 
    [Limit1].[CreatedBy1] AS [CreatedBy1], 
    [Limit1].[CreatedAt1] AS [CreatedAt1], 
    [Limit1].[LastRevision1] AS [LastRevision1], 
    [Limit1].[AccessControlListId1] AS [AccessControlListId1], 
    [Limit1].[EntityStatus1] AS [EntityStatus1], 
    [Limit1].[CheckSum1] AS [CheckSum1], 
    [Limit1].[C17] AS [C17], 
    [Limit1].[C18] AS [C18], 
    [Limit1].[C19] AS [C19], 
    [Limit1].[C20] AS [C20], 
    [Limit1].[C21] AS [C21], 
    [Limit1].[C22] AS [C22], 
    [Limit1].[C23] AS [C23], 
    [Limit1].[C24] AS [C24], 
    [Limit1].[C25] AS [C25], 
    [Limit1].[C26] AS [C26], 
    [Limit1].[Name_Firstname] AS [Name_Firstname], 
    [Limit1].[Name_Surname] AS [Name_Surname], 
    [Limit1].[Name_Prefix] AS [Name_Prefix], 
    [Limit1].[Name_Title] AS [Name_Title], 
    [Limit1].[Name_Middle] AS [Name_Middle], 
    [Limit1].[Name_Suffix] AS [Name_Suffix], 
    [Limit1].[Sex] AS [Sex], 
    [Limit1].[DateOfBirth] AS [DateOfBirth], 
    [Limit1].[State] AS [State], 
    [Limit1].[C27] AS [C27], 
    [Limit1].[C28] AS [C28], 
    [Limit1].[C29] AS [C29], 
    [Limit1].[C30] AS [C30], 
    [Limit1].[C31] AS [C31], 
    [Limit1].[Id2] AS [Id2], 
    [Limit1].[Version2] AS [Version2], 
    [Limit1].[EntityType2] AS [EntityType2], 
    [Limit1].[Deleted2] AS [Deleted2], 
    [Limit1].[UpdatedBy2] AS [UpdatedBy2], 
    [Limit1].[UpdatedAt2] AS [UpdatedAt2], 
    [Limit1].[CreatedBy2] AS [CreatedBy2], 
    [Limit1].[CreatedAt2] AS [CreatedAt2], 
    [Limit1].[LastRevision2] AS [LastRevision2], 
    [Limit1].[AccessControlListId2] AS [AccessControlListId2], 
    [Limit1].[EntityStatus2] AS [EntityStatus2], 
    [Limit1].[CheckSum2] AS [CheckSum2], 
    [Limit1].[C32] AS [C32], 
    [Limit1].[C33] AS [C33], 
    [Limit1].[C34] AS [C34], 
    [Limit1].[C35] AS [C35], 
    [Limit1].[C36] AS [C36], 
    [Limit1].[C37] AS [C37], 
    [Limit1].[C38] AS [C38], 
    [Limit1].[C39] AS [C39], 
    [Limit1].[C40] AS [C40], 
    [Limit1].[C41] AS [C41], 
    [Limit1].[C42] AS [C42], 
    [Limit1].[C43] AS [C43], 
    [Limit1].[C44] AS [C44], 
    [Limit1].[C45] AS [C45], 
    [Limit1].[C46] AS [C46], 
    [Limit1].[C47] AS [C47], 
    [Limit1].[C48] AS [C48], 
    [Limit1].[C49] AS [C49], 
    [Limit1].[C50] AS [C50], 
    [Limit1].[C51] AS [C51], 
    [Limit1].[Ssn] AS [Ssn], 
    [Limit1].[Employeenumber] AS [Employeenumber], 
    [Limit1].[Bankaccount] AS [Bankaccount], 
    [Limit1].[PersonId] AS [PersonId]
    FROM ( SELECT TOP (1) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Username] AS [Username], 
        [Extent1].[Password] AS [Password], 
        [Extent1].[Email] AS [Email], 
        [Extent1].[ResetHash] AS [ResetHash], 
        [Extent1].[Flag] AS [Flag], 
        [Extent1].[CryptoKey] AS [CryptoKey], 
        [Extent1].[FailedPasswordTries] AS [FailedPasswordTries], 
        [Extent1].[LastPasswordTry] AS [LastPasswordTry], 
        [Extent1].[UXConfigId] AS [UXConfigId], 
        [Extent1].[LastActivity] AS [LastActivity], 
        [Extent2].[Version] AS [Version], 
        [Extent2].[EntityType] AS [EntityType], 
        [Extent2].[Deleted] AS [Deleted], 
        [Extent2].[UpdatedBy] AS [UpdatedBy], 
        [Extent2].[UpdatedAt] AS [UpdatedAt], 
        [Extent2].[CreatedBy] AS [CreatedBy], 
        [Extent2].[CreatedAt] AS [CreatedAt], 
        [Extent2].[LastRevision] AS [LastRevision], 
        [Extent2].[AccessControlListId] AS [AccessControlListId], 
        [Extent2].[EntityStatus] AS [EntityStatus], 
        [Extent2].[CheckSum] AS [CheckSum], 
        '0X0X' AS [C1], 
        CAST(NULL AS int) AS [C2], 
        CAST(NULL AS varchar(1)) AS [C3], 
        CAST(NULL AS varchar(1)) AS [C4], 
        CAST(NULL AS varchar(1)) AS [C5], 
        CAST(NULL AS varchar(1)) AS [C6], 
        CAST(NULL AS varchar(1)) AS [C7], 
        CAST(NULL AS varchar(1)) AS [C8], 
        CAST(NULL AS bigint) AS [C9], 
        CAST(NULL AS datetime2) AS [C10], 
        CAST(NULL AS bigint) AS [C11], 
        CAST(NULL AS varchar(1)) AS [C12], 
        CAST(NULL AS varchar(1)) AS [C13], 
        CAST(NULL AS varchar(1)) AS [C14], 
        CAST(NULL AS uniqueidentifier) AS [C15], 
        [Join3].[Id1] AS [Id1], 
        [Join3].[Name_Firstname] AS [Name_Firstname], 
        [Join3].[Name_Surname] AS [Name_Surname], 
        [Join3].[Name_Prefix] AS [Name_Prefix], 
        [Join3].[Name_Title] AS [Name_Title], 
        [Join3].[Name_Middle] AS [Name_Middle], 
        [Join3].[Name_Suffix] AS [Name_Suffix], 
        [Join3].[Sex] AS [Sex], 
        [Join3].[DateOfBirth] AS [DateOfBirth], 
        [Join3].[State] AS [State], 
        [Join3].[Version] AS [Version1], 
        [Join3].[EntityType] AS [EntityType1], 
        [Join3].[Deleted] AS [Deleted1], 
        [Join3].[UpdatedBy] AS [UpdatedBy1], 
        [Join3].[UpdatedAt] AS [UpdatedAt1], 
        [Join3].[CreatedBy] AS [CreatedBy1], 
        [Join3].[CreatedAt] AS [CreatedAt1], 
        [Join3].[LastRevision] AS [LastRevision1], 
        [Join3].[AccessControlListId] AS [AccessControlListId1], 
        [Join3].[EntityStatus] AS [EntityStatus1], 
        [Join3].[CheckSum] AS [CheckSum1], 
        CASE WHEN ([Join3].[Id1] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE '0X1X' END AS [C16], 
        CAST(NULL AS varchar(1)) AS [C17], 
        CAST(NULL AS varchar(1)) AS [C18], 
        CAST(NULL AS varchar(1)) AS [C19], 
        CAST(NULL AS varchar(1)) AS [C20], 
        CAST(NULL AS bigint) AS [C21], 
        CAST(NULL AS varchar(1)) AS [C22], 
        CAST(NULL AS smallint) AS [C23], 
        CAST(NULL AS datetime2) AS [C24], 
        CAST(NULL AS uniqueidentifier) AS [C25], 
        CAST(NULL AS datetime2) AS [C26], 
        CAST(NULL AS varchar(1)) AS [C27], 
        CAST(NULL AS varchar(1)) AS [C28], 
        CAST(NULL AS varchar(1)) AS [C29], 
        CAST(NULL AS uniqueidentifier) AS [C30], 
        [Join6].[Id2] AS [Id2], 
        [Join6].[Ssn1] AS [Ssn], 
        [Join6].[Employeenumber1] AS [Employeenumber], 
        [Join6].[Bankaccount1] AS [Bankaccount], 
        [Join6].[PersonId1] AS [PersonId], 
        [Join6].[Version] AS [Version2], 
        [Join6].[EntityType] AS [EntityType2], 
        [Join6].[Deleted] AS [Deleted2], 
        [Join6].[UpdatedBy] AS [UpdatedBy2], 
        [Join6].[UpdatedAt] AS [UpdatedAt2], 
        [Join6].[CreatedBy] AS [CreatedBy2], 
        [Join6].[CreatedAt] AS [CreatedAt2], 
        [Join6].[LastRevision] AS [LastRevision2], 
        [Join6].[AccessControlListId] AS [AccessControlListId2], 
        [Join6].[EntityStatus] AS [EntityStatus2], 
        [Join6].[CheckSum] AS [CheckSum2], 
        CASE WHEN ([Join6].[Id2] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE '0X2X' END AS [C31], 
        CAST(NULL AS varchar(1)) AS [C32], 
        CAST(NULL AS varchar(1)) AS [C33], 
        CAST(NULL AS varchar(1)) AS [C34], 
        CAST(NULL AS varchar(1)) AS [C35], 
        CAST(NULL AS bigint) AS [C36], 
        CAST(NULL AS varchar(1)) AS [C37], 
        CAST(NULL AS smallint) AS [C38], 
        CAST(NULL AS datetime2) AS [C39], 
        CAST(NULL AS uniqueidentifier) AS [C40], 
        CAST(NULL AS datetime2) AS [C41], 
        CAST(NULL AS int) AS [C42], 
        CAST(NULL AS varchar(1)) AS [C43], 
        CAST(NULL AS varchar(1)) AS [C44], 
        CAST(NULL AS varchar(1)) AS [C45], 
        CAST(NULL AS varchar(1)) AS [C46], 
        CAST(NULL AS varchar(1)) AS [C47], 
        CAST(NULL AS varchar(1)) AS [C48], 
        CAST(NULL AS bigint) AS [C49], 
        CAST(NULL AS datetime2) AS [C50], 
        CAST(NULL AS bigint) AS [C51]
        FROM    [dbo].[Users] AS [Extent1]
        INNER JOIN  (SELECT [Var_27].[Id] AS [Id], [Var_27].[Version] AS [Version], [Var_27].[EntityType] AS [EntityType], [Var_27].[Deleted] AS [Deleted], [Var_27].[UpdatedBy] AS [UpdatedBy], [Var_27].[UpdatedAt] AS [UpdatedAt], [Var_27].[CreatedBy] AS [CreatedBy], [Var_27].[CreatedAt] AS [CreatedAt], [Var_27].[LastRevision] AS [LastRevision], [Var_27].[AccessControlListId] AS [AccessControlListId], [Var_27].[EntityStatus] AS [EntityStatus], [Var_27].[CheckSum] AS [CheckSum]
            FROM [dbo].[Entities] AS [Var_27]
            WHERE [Var_27].[Deleted] <> 1 ) AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
        LEFT OUTER JOIN  (SELECT [Extent3].[Id] AS [Id1], [Extent3].[Name_Firstname] AS [Name_Firstname], [Extent3].[Name_Surname] AS [Name_Surname], [Extent3].[Name_Prefix] AS [Name_Prefix], [Extent3].[Name_Title] AS [Name_Title], [Extent3].[Name_Middle] AS [Name_Middle], [Extent3].[Name_Suffix] AS [Name_Suffix], [Extent3].[Sex] AS [Sex], [Extent3].[DateOfBirth] AS [DateOfBirth], [Extent3].[State] AS [State], [Extent4].[Id] AS [Id3], [Extent4].[Version] AS [Version], [Extent4].[EntityType] AS [EntityType], [Extent4].[Deleted] AS [Deleted], [Extent4].[UpdatedBy] AS [UpdatedBy], [Extent4].[UpdatedAt] AS [UpdatedAt], [Extent4].[CreatedBy] AS [CreatedBy], [Extent4].[CreatedAt] AS [CreatedAt], [Extent4].[LastRevision] AS [LastRevision], [Extent4].[AccessControlListId] AS [AccessControlListId], [Extent4].[EntityStatus] AS [EntityStatus], [Extent4].[CheckSum] AS [CheckSum]
            FROM   [dbo].[People] AS [Extent3]
            INNER JOIN  (SELECT [Var_28].[Id] AS [Id], [Var_28].[Version] AS [Version], [Var_28].[EntityType] AS [EntityType], [Var_28].[Deleted] AS [Deleted], [Var_28].[UpdatedBy] AS [UpdatedBy], [Var_28].[UpdatedAt] AS [UpdatedAt], [Var_28].[CreatedBy] AS [CreatedBy], [Var_28].[CreatedAt] AS [CreatedAt], [Var_28].[LastRevision] AS [LastRevision], [Var_28].[AccessControlListId] AS [AccessControlListId], [Var_28].[EntityStatus] AS [EntityStatus], [Var_28].[CheckSum] AS [CheckSum]
                FROM [dbo].[Entities] AS [Var_28]
                WHERE [Var_28].[Deleted] <> 1 ) AS [Extent4] ON [Extent3].[Id] = [Extent4].[Id]
            LEFT OUTER JOIN [dbo].[Employees] AS [Extent5] ON [Extent3].[Id] = [Extent5].[Person_Id] ) AS [Join3] ON [Join3].[Id1] = [Extent1].[Person_Id]
        LEFT OUTER JOIN  (SELECT [Extent6].[Id] AS [Id2], [Extent6].[Person_Id] AS [Person_Id1], [Extent6].[Ssn] AS [Ssn1], [Extent6].[Employeenumber] AS [Employeenumber1], [Extent6].[Bankaccount] AS [Bankaccount1], [Extent6].[PersonId] AS [PersonId1], [Extent7].[Id] AS [Id4], [Extent7].[Version] AS [Version], [Extent7].[EntityType] AS [EntityType], [Extent7].[Deleted] AS [Deleted], [Extent7].[UpdatedBy] AS [UpdatedBy], [Extent7].[UpdatedAt] AS [UpdatedAt], [Extent7].[CreatedBy] AS [CreatedBy], [Extent7].[CreatedAt] AS [CreatedAt], [Extent7].[LastRevision] AS [LastRevision], [Extent7].[AccessControlListId] AS [AccessControlListId], [Extent7].[EntityStatus] AS [EntityStatus], [Extent7].[CheckSum] AS [CheckSum], [Extent8].[Person_Id] AS [Person_Id2]
            FROM   [dbo].[Employees] AS [Extent6]
            INNER JOIN  (SELECT [Var_29].[Id] AS [Id], [Var_29].[Version] AS [Version], [Var_29].[EntityType] AS [EntityType], [Var_29].[Deleted] AS [Deleted], [Var_29].[UpdatedBy] AS [UpdatedBy], [Var_29].[UpdatedAt] AS [UpdatedAt], [Var_29].[CreatedBy] AS [CreatedBy], [Var_29].[CreatedAt] AS [CreatedAt], [Var_29].[LastRevision] AS [LastRevision], [Var_29].[AccessControlListId] AS [AccessControlListId], [Var_29].[EntityStatus] AS [EntityStatus], [Var_29].[CheckSum] AS [CheckSum]
                FROM [dbo].[Entities] AS [Var_29]
                WHERE [Var_29].[Deleted] <> 1 ) AS [Extent7] ON [Extent6].[Id] = [Extent7].[Id]
            INNER JOIN [dbo].[Employees] AS [Extent8] ON 1 = 1 ) AS [Join6] ON ([Join6].[Person_Id1] = [Extent1].[Person_Id]) AND ([Extent1].[Person_Id] = [Join6].[Person_Id2])
        WHERE [Extent1].[Id] = @p__linq__0
    )  AS [Limit1]

Update 2

In response to @grek40 - the interceptor we have in place adds to each select query to make sure the entities we are receiving do not have the flag Deleted == true. It is joining the Entities table for every object + include, and thus the above query shows 3 additional joins. If we disable the interceptor, we are left with 4 joins instead of 7. We didn't think much of it, but now that we've disabled it, the calculated time for the query above, through Entity Framework, went from ~3 seconds to ~2 seconds. It appears to have been accountable for 1/3rd of the performance issues we're seeing.

Update 3

In response to @GertArnold, the following is our mapping code for our Entity base class, matching the above query:

modelBuilder.Entity<Entity>()
            .HasKey(p => new { p.Id })
            // Table Per Type (TPT) inheritance root class
            .ToTable("Entities", "dbo");
        // Properties:
        modelBuilder.Entity<Entity>()
            .Property(p => p.Id)
                .IsRequired()
                .HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None)
                .HasColumnType("uniqueidentifier");
        modelBuilder.Entity<Entity>()
            .Property(p => p.Version)
                .IsRequired()
                .IsConcurrencyToken()
                .HasColumnType("bigint");
        modelBuilder.Entity<Entity>()
            .Property(p => p.EntityType)
                .IsRequired()
                .HasColumnType("varchar");
        modelBuilder.Entity<Entity>()
            .Property(p => p.Deleted)
                .IsRequired()
                .HasColumnType("bit");
        modelBuilder.Entity<Entity>()
            .Property(p => p.UpdatedBy)
                .HasColumnType("uniqueidentifier");
        modelBuilder.Entity<Entity>()
            .Property(p => p.UpdatedAt)
                .HasColumnType("datetime");
        modelBuilder.Entity<Entity>()
            .Property(p => p.CreatedBy)
                .HasColumnType("uniqueidentifier");
        modelBuilder.Entity<Entity>()
            .Property(p => p.CreatedAt)
                .HasColumnType("datetime");
        modelBuilder.Entity<Entity>()
            .Property(p => p.LastRevision)
                .IsRequired()
                .HasColumnType("bigint");
        modelBuilder.Entity<Entity>()
            .Property(p => p.AccessControlListId)
                .HasColumnType("uniqueidentifier");
        modelBuilder.Entity<Entity>()
            .Property(p => p.EntityStatus)
                .IsRequired()
                .HasColumnType("bigint");
        modelBuilder.Entity<Entity>()
            .Property(p => p.CheckSum)
                .IsRequired()
                .HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Computed)
                .IsConcurrencyToken()
                .HasColumnType("int");

Grab the query, generated by EF (for example using profiler) and try to execute it in SSMS. Most probably you'll have some missing indexes there — Dmitrij Kultasev, Nov 23 '17 at 16:37
Is one of your simplified model class abstract ? Is the generated sql query different with var user = context.Set().Where(u => u.Id == userId) .Include(u => u.Person.Employee) .FirstOrDefault(); ? — jbl, Nov 23 '17 at 16:57
@jbl - We dont have any abstract classes. Our model consists of roughly 80 entities, with most of them inheriting the above Entity object. — , Nov 23 '17 at 17:10
I could say don't use EF if you want a performant application! If you think your queries are executing quickly but EF is taking 8000ms to come back have you tried profiling to ensure there's definitely none of your code executing before object hydration? — Charleh, Nov 27 '17 at 10:05
Have You tried to profile the dotnet code? With VS Performance Profiler or Redgate or some other tool? — Daniel Stackenland, Nov 27 '17 at 10:32
Did you make sure to warm-up EF before measuring query time? The first database access will be much slower since EF will build its internal database model. — grek40, Nov 27 '17 at 11:22
@Dmitrij Kultasev - When executing the generated queries directly in SSMS they are rapid, with the query window usually listing 00:00:00. We have considered an indexes issue before, but believe that we have (most of) the correct ones in place. — , Nov 27 '17 at 12:55
@grek40 - Some of our slow queries are post-login, presuming that EF is primed-and-ready after having executed at least one other query before that stage. Do you mean per context? We have these within using blocks. Could there be some overhead there? — , Nov 27 '17 at 13:00
@TVisser no, a fresh context should be fine, only minimal overhead expected from there. If I understand correctly, you execute the exact query that EF creates with some SQL tool in a much smaller timespan and the result is only a single/few entities due to the given userId? — grek40, Nov 27 '17 at 13:06
@grek40 - Correct. The posted information serves just as an example, but it appears to happen with every query we have that utilizes `Include`, to join up to sometimes 7 related entities. If we drop the Person and Employee includes from the above example, the execution time is anywhere between 10ms and 100ms. — , Nov 27 '17 at 13:32
Hope `Entity` class is not a part of TPT inheritance? Also give us some clues, what does `context.Set().Include(u => u.Person.Employee).Where(u => u.Id == userId).Take(1).ToString()` (i.e. the generated SQL query) looks like? And `context.Set().Local.Count` ? Blocking `Find` is really weird, can you eliminate the non EF stuff like `EFCache`. — Ivan Stoev, Nov 27 '17 at 16:17
@Ivan Stoev - The last query generates '0' when executing the query with `AsNoTracking`, as to be expected. Without `AsNoTracking` the result is 1. I've updated the post containing the exact/full query generated. — , Nov 27 '17 at 17:28
@TVisser you said you were async all the way but both find and firstOrDefault have async variants that you should likely use — Aluan Haddad, Nov 27 '17 at 17:43
Wow, the `Entity` *is* part of TPT - normally such query (w/o EF inheritance) should have simple 1 `FROM` and 2 `LEFT/INNER` joins. Btw, what is generating `Var_XX` subqueries, this is the first time I see such alias in EF generated SQL - some 3rd party interceptor? — Ivan Stoev, Nov 27 '17 at 17:44
Turn on lazy loading and just query for User with Id and then call **Include** and **Load** specifically for the properties you need — Rudresha Parameshappa, Nov 27 '17 at 17:45
context.Set().Where(u => u.Id == userId).Include(x=>x.Property).Load(); — Rudresha Parameshappa, Nov 27 '17 at 17:46
Query generator from hell :) ok, so part of the issue is the separate `Entity` table that acts as a history keeping base table for everything (as far as I can tell), blowing each join table with another nested join. Judging by the generated discriminator columns of the result, I wonder if there is more type hierarchy involved or whether they only result from the Entity inheritance... — grek40, Nov 27 '17 at 20:48
Another thing... is `Employee` to `Person` and `Person` to `User` a one-to-many relationship? It looks like it at first sight and I wonder whether it makes a difference to the query. — grek40, Nov 27 '17 at 20:55
FirstOrDefault is slower than using where from what I understand... It shouldn't be that much slower but there's a chance you're loading and checking more than you should. The suggestion to look at the SQL that is actually produced and used is the best. I would also test again Where like so.. ```var user = context.Set().Where(u => u.Id == userId).FirstOrDefault();``` — Michael Puckett II, Nov 27 '17 at 22:27
You don't need the TPT inheritance. EF doesn't have to know anything of the `Entity`class. You can map each of your classes as an independent entity and still (in C#) let them inherit from the base class. It only takes a bit more mapping code. — Gert Arnold, Nov 28 '17 at 07:33
The latter can be mitigated by using a base configuration class: https://stackoverflow.com/a/15579971/861716 — Gert Arnold, Nov 28 '17 at 07:39
We're now in the process of converting our model to have an abstract Entity class, hoping that this will fix it. What I still dont fully understand is that the query in SSMS is fast but slow through EF. Is EF experiencing quite some overhead when it comes to joins? @Ivan Stoev - the Var_XX is part of an interceptor taking care of entities flagged as deleted. — , Nov 28 '17 at 15:43
Getting rid of unnecessary EF inheritance should help getting simpler queries with less joins (although upgrading the database would be a pain), but I'm unsure if that's the issue. Normally a few more joins by PK do not affect performance. May be the combination of delete filter and joins produces a bad plan (full table scans). Another possibility is the parameter sniffing (in case the same query runs fast in SSMS). Anyway, I wasn't able to reproduce it with the sample model even populating the tables with approx. 50K records. Good luck. — Ivan Stoev, Nov 28 '17 at 15:58
@TVisser is there anything about the interceptor that happens at the result mapping after the query completed? — grek40, Nov 29 '17 at 09:22
@grek40 - Updated the answer with additional information. After some testing, it appears no additional work is being done by the interceptor when mapping the entities. — , Nov 29 '17 at 09:55
@TVisser Have you investigated general deadlocks or other SQL performance issue? Since the query is slow via EF, but fast via SSMS you should look into what it's actually doing at runtime. EF may be opening a connection with different parameters than SSMS. OR the particular timing of the query could be the issue, ie something immediately before this is causing issues in the DB. — Vlad274, Nov 29 '17 at 23:31
@RuslanTolkachev Such a truism, like qub1n's answer, isn't very helpful. "I have problems using X" - "Don't use X". The issue is about EF, so the answer should also be in the context of EF. Even "no way" (when substantiated) is a better answer than "don't use it". — Gert Arnold, Nov 30 '17 at 08:03
If you are having issues now (and everyone with a site that grows will have issues with it because it's just a toy), you will eventually have to find alternatives (stored procedures) but hopefully you are not going to be as deep in to it as Panda Express who can not find a DBA who would touch their system. Just use SP like suggested for most of your db calls. — Ruslan Tolkachev, Nov 30 '17 at 13:26
After trying your model I come to the same conclusion as Ivan earlier. However, I don't get the same sequence of INNER/OUTER joins. Could you also show the mapping code as it seems to be different? Also, it would be interesting to know which line of the "alternative approach" (with the `Load()`s) is the performance killer. — Gert Arnold, Dec 02 '17 at 11:55
@GertArnold I've updated the post to contain the mapping code for our base Entity class. — , Dec 06 '17 at 11:31

score 7 · Answer 1 · answered Nov 28 '17 at 07:20

7

It seems to me that the query is too much complicated (too many joins) for such simple operation as getting user info.

To gain maximum performance, just write Stored Procedure with @userId parameter, optimize SQL Query in this Stored Procedure without Entity Framework (check Actual Query plan in SSMS) and then just write wrapper in Entity Framework to call this procedure.

If it is not enough, create indexed view for this query.

If it is still not enough, you have to redesign database structure to be more simple and if you may cache some VIEWS in temporary tables and update these cache views by triggers if user table or employee table is changed. This can help a lot.

answered Nov 28 '17 at 07:20

Tomas Kubes

23,880
18
111
148

5

Once you start doing the things outside the EF, you lose the whole point of using EF at all. For instance, you can create db views and use them from EF, but you cannot load entities with related data, which the OP is all about - *Entity Framework **include** poor performance", in other words, no `Include`, no problem, no need of db views. But db views cannot do what the `Include` is doing, so they are not solution to the problem. – Ivan Stoev Nov 30 '17 at 18:59
1

Thanks, qub1n. For the reasons exposed by @IvanStoev I won´t award the whole bounty to this answer. – Mario Levrero Dec 04 '17 at 08:58

score 2 · Answer 2 · answered Nov 28 '17 at 09:58

Please try

var userId = .... // Obtained elsewhere
using (var context = new DbContext())
{
    var user =
        context.Set<User>()
            .Include(u => u.Person.Employee)
            .Where(u => u.Id == userId)
            .ToList()
            .FirstOrDefault();
}

If it helps then the possible reason is that IQueryable's FirstOrDefault generates SQL's TOP 1, which in turn may make SQL optimizer use Nested Loops instead of Hash Matches.

score 1 · Answer 3 · answered Feb 08 '19 at 13:41

1

The best way i found until now is with EntityFramework Plus extension, read this https://entityframework-plus.net/query-include-optimized, you will understand the interest instantly

answered Feb 08 '19 at 13:41

olivier houssin

842
1
6
11

score 0 · Answer 4 · answered Nov 28 '17 at 07:08

Most probably it could be indexing issues or missing indexes on you tables, try to run slow queries on Microsoft SQL server tunning advisor and it will give you recommendations about your queries and tables.

This Link will be helpful: https://learn.microsoft.com/en-us/sql/relational-databases/performance/start-and-use-the-database-engine-tuning-advisor