实体框架包括较差的性能

Question

上下文

我们似乎有一个实体框架6.x相关的问题。我们花了几周的时间来确定性能问题，并且修复了我们能找到/想到的大部分(如果不是全部的话)。简而言之，当我们使用Include时，我们看到了性能的大幅下降。

• 利用EFCache.
• 启用了运行EF 6.2的db模型缓存。
• 利用缓存的视图。
• 在可能的情况下，在没有延迟加载的情况下使用上下文，使用正确的(和最小的)包含。
• 对只读数据使用AsNoTracking。
• 使用没有代理生成或自动检测更改的上下文(尽管后者似乎是一个很小的改进)。
• 上下文生存期是最小的，在可能的情况下使用块中的单个查询。
• 清理对象上的构造函数，以便EF在将数据映射到对象时应经历最少的开销。
• “一路异步”当然提高了响应能力，但并没有减少所做的工作。

目前(推定)我们仍然存在可能产生影响的问题：

• 主键，集群GUID。
• 表每种类型的层次结构，实体是其中的一部分，会导致双重连接。

我们已经讨论过的大部分相关话题，都没有多大效果。据我们所知，数据库是“好的”。在查询访问数据库之前，我们使用log4net拦截器，我们发现尽管我们的3-7包含的一些查询非常可怕，但它们并不是非常慢:时间从0ms到100 0ms不等。它往往是2000到8000毫秒，直到物体“准备”使用。

目前我们的数据库中最多有50.000个实体。然而，即使有一个近乎干净的数据库，差别也是极小的。

代码

(简化、提取)模型结构：

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; }
}

(简化)缓慢查询。包装Stopwatch表示平均持续时间为2秒，但查询本身在log4net生成的日志文件中只列出了几毫秒：

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

我们尝试过其他方法：

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

摘要

根据所提供的信息，有没有人看到我们可能错过的一个明确的问题，或者对我们可以尝试的事情有其他的建议？

我们现在还存在着上述两个问题，这是否会妨碍我们以半快的方式构造目标呢？

也许是相关的，使用Find(userId)而不是FirstOrDefault块，并且似乎没有在合理的时间内完成。

更新1

针对@Ivan运行上述查询，运行98 In (2968 In)并生成以下(完整) SQL语句：

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]

更新2

作为对@grek40 40的响应，我们现有的拦截器将添加到每个select查询中，以确保我们接收的实体没有标志Deleted == true。它加入了每个对象+包含的实体表，因此上面的查询显示了另外三个联接。如果我们禁用拦截器，剩下的是4个连接，而不是7秒。我们不太重视它，但是现在我们已经禁用了它，上面通过实体框架进行查询的计算时间从~3秒到~2秒。它似乎已经对我们所看到的三分之一的性能问题负责。

更新3

为了响应@GertArnold，下面是实体基类的映射代码，与上面的查询匹配：

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");

Answer 1

在我看来，对于获取用户信息这样简单的操作来说，这个查询太复杂了(太多的联接)。

要获得最大的性能，只需编写带有@userId参数的存储过程，在此存储过程中优化SQL查询而不使用实体框架(在SSMS中检查实际查询计划)，然后在实体框架中编写包装器来调用此过程。

如果还不够，请使用创建索引视图进行此查询。

如果还不够，则必须重新设计数据库结构，使其更加简单；如果您可以在临时表中缓存某些视图，并在用户表或employee表发生更改时，通过触发器更新这些缓存视图。这能帮上很多忙。

Answer 2

请尝试

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();
}

如果这有帮助，那么可能的原因是IQueryable的FirstOrDefault生成了SQL的前1，这反过来可能会使SQL优化器使用嵌套的循环而不是哈希匹配。

Answer 3

到目前为止，我发现的最好的方法是使用EntityFramework +扩展，阅读这个https://entityframework-plus.net/query-include-optimized，您就会立即理解它的兴趣。