上下文
我们似乎遇到了一个与实体框架6.x相关的问题。我们花了数周时间试图确定性能问题并修复大部分（如果不是全部）我们可以找到/想到的。简而言之，当使用Include时，我们看到了巨大的性能下降。
- 利用EFCache。
- 启用运行EF 6.2的db模型缓存。
- 利用缓存视图。
- 在可能的情况下使用没有延迟加载的上下文，利用正确的（和最小的）包含。
- 对于只读数据使用
AsNoTracking。 - 使用没有代理生成或自动检测更改的上下文（尽管后者似乎有微小的改进）。
- 上下文生命周期很短，在可能的情况下单个查询在使用块内完成。
- 对象的构造函数干净，因此EF在将数据映射到对象时应该经历最小的开销。
- "始终异步"，这肯定提高了响应能力，但并不会减少所做的工作量。
我们仍然存在可能影响的（预计的）当前问题：
- 主键，聚集GUID。
- 表类型层次结构，实体是其中一部分，导致双重连接。
我们已经研究了大部分相关主题，但没有太多效果。据我们所知，数据库“还好”。在查询命中数据库之前，利用log4net拦截器，我们发现尽管我们的3-7个包含的一些查询是巨大的，但它们并不那么慢：时间从0ms到100ms不等。直到对象可以使用为止，这往往是2000ms到8000ms。
我们目前的数据库中最多有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进行监控，平均持续时间为两秒，但查询本身在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);
摘要
根据提供的信息，是否有人看到了我们可能忽略的明显问题，或者对我们可以尝试的事情有建议？
我们仍然存在两个前述的“问题”是否会妨碍EF以半快速的方式构造对象呢？
或许相关的是，使用Find(userId)而不是FirstOrDefault会阻塞并且在合理的时间内无法完成。
更新1
回复@Ivan Stoev - 运行以上查询花费了98毫秒（2968毫秒），并生成了以下完整的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 的反馈 - 我们使用的拦截器会在每个选择查询中添加内容，以确保我们接收到的实体没有标记为 Deleted == true。它为每个对象和 include 加入了 Entities 表，并且上面的查询显示了额外的 3 个连接。如果我们禁用拦截器，那么在上述查询中，我们只剩下 4 个连接而不是 7 个连接。我们没有太在意，但现在我们已经禁用它，通过 Entity Framework 计算上述查询的时间从约 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");
Entity类不是TPT继承的一部分？另外，能否给我们一些线索，context.Set<User>().Include(u => u.Person.Employee).Where(u => u.Id == userId).Take(1).ToString()（即生成的SQL查询）是什么样子的？还有context.Set<User>().Local.Count？阻塞Find真的很奇怪，你能消除像EFCache这样的非EF内容吗？ - Ivan StoevEntity确实是TPT的一部分 - 通常这样的查询（没有EF继承）应该只有一个简单的1FROM和2个LEFT/INNER连接。顺便问一下，是什么生成了Var_XX子查询，这是我第一次在EF生成的SQL中看到这样的别名 - 是某个第三方拦截器吗？ - Ivan Stoev