c# - query - entity framework performance

Entity Framework incluye mal desempeño

Question

Contexto

Parece que tenemos un problema relacionado con Entity Framework 6.x. Hemos pasado semanas intentando resolver problemas de rendimiento y hemos solucionado la mayoría, si no todas, las que podemos encontrar o pensar. En resumen, estamos viendo una caída masiva en el rendimiento al utilizar Include .

• Utilizando EFCache.
• Habilitado el almacenamiento en caché del modelo DB ejecutando EF 6.2
• Haciendo uso de vistas en caché.
• Usar un contexto sin carga perezosa cuando sea posible, utilizando los correctos (y mínimos) incluidos.
• Usando AsNoTracking para datos de solo lectura.
• Uso de un contexto sin generación de proxy ni cambios de detección automática (aunque este último parece ser una mejora mínima).
• La vida útil del contexto es mínima, solo consulta dentro de usar bloques cuando sea posible
• Limpie los constructores de los objetos, por lo que EF debería experimentar una sobrecarga mínima al asignar los datos a los objetos.
• " Asincronizar todo ", lo que sin duda mejoró la capacidad de respuesta, pero no reduce el trabajo que se está realizando.

Problemas actuales (presumibles) que todavía tenemos que podrían tener un impacto:

• Clave principal, GUIDs agrupados.
• Tabla por jerarquía de tipos, con la entidad que forma parte de ella, lo que podría causar uniones dobles.

La mayoría de los temas relacionados que ya hemos grabado, sin mucho efecto. Por lo que podemos decir, la base de datos está "bien". Al utilizar un interceptor log4net antes de que las consultas lleguen a la base de datos, descubrimos que aunque algunas de nuestras consultas con 3-7 incluyen son monstruosas, no son extremadamente lentas: tiempos que van desde 0 ms a 100 ms. Sin embargo, tiende a ser de 2000 ms hasta 8000 ms hasta que los objetos están "listos" para su uso.

Tenemos como máximo 50.000 entidades actualmente en nuestra base de datos. Sin embargo, incluso con una base de datos casi limpia, hay una diferencia mínima.

Código

Estructura del modelo (simplificada, extraída):

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 string Username { get; set; }
public virtual string Password { get; set; }
public virtual Person Person { get; set; }
}
public class Person : Entity
{
public virtual DateTime DateOfBirth { get; set; }
public virtual Name Name { get; set; }
public virtual Employee Employee { get; set; }
}
public class Employee : Entity
{
public virtual string EmployeeId { get; set; }
}

(Simplificada) consulta lenta, tomando dos segundos en promedio, pero algunos ms basados ​​en 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);
}

Hemos intentado enfoques alternativos:

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

Resumen

En función de la información proporcionada, ¿alguien ve un problema claro que podemos haber pasado por alto o tiene alguna otra sugerencia sobre cosas que podríamos intentar?

¿Podría el hecho de que todavía tengamos los dos ''problemas'' mencionados anteriormente para que el impelente EF construya los objetos de forma semi-rápida?

Tal vez sea relevante, usar Find(userId) lugar de los bloques FirstOrDefault y no parece terminar dentro de un período razonable.

Actualización 1

En respuesta a @Ivan Stoev, la ejecución de la consulta anterior se ejecutó durante 98 ms (2968 ms) y generó la siguiente instrucción SQL (completa):

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]

Actualización 2

En respuesta a @ grek40: el interceptor que tenemos en su lugar se agrega a cada consulta de selección para asegurarnos de que las entidades que estamos recibiendo no tengan el indicador Deleted == true . Se está uniendo a la tabla de Entidades para cada objeto + incluir, y por lo tanto la consulta anterior muestra 3 combinaciones adicionales. Si deshabilitamos el interceptor, nos quedamos con 4 combinaciones en lugar de 7. No pensamos mucho en ello, pero ahora que lo deshabilitamos, el tiempo calculado para la consulta anterior, a través del Entity Framework, pasó de ~ 3 segundos a ~ 2 segundos. Parece haber sido responsable de 1/3 de los problemas de rendimiento que estamos viendo.

Actualización 3

En respuesta a @GertArnold, el siguiente es nuestro código de asignación para nuestra clase base Entidad, que coincide con la consulta anterior:

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

Lo más probable es que se trate de problemas de indexación o índices perdidos en sus tablas, intente ejecutar consultas lentas en el asesor de optimización de Microsoft SQL Server y le dará recomendaciones sobre sus consultas y tablas.

Este enlace será útil: https://docs.microsoft.com/en-us/sql/relational-databases/performance/start-and-use-the-database-engine-tuning-advisor

Answer 2

Me parece que la consulta es demasiado complicada (demasiadas combinaciones) para una operación tan simple como obtener información del usuario.

Para obtener el máximo rendimiento, solo escriba el procedimiento almacenado con el parámetro @userId, optimice la consulta SQL en este procedimiento almacenado sin Entity Framework (verifique el plan de consulta real en SSMS) y luego escriba la envoltura en Entity Framework para llamar a este procedimiento.

Si no es suficiente, cree una vista indizada para esta consulta.

Si aún no es suficiente, tiene que rediseñar la estructura de la base de datos para que sea más simple, y si puede almacenar en caché algunas VISTA en tablas temporales y actualizar estas vistas de caché mediante activadores si se modifica la tabla de usuarios o la tabla de empleados. Esto puede ayudar mucho.

Answer 3

Por favor, inténtalo

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

Si ayuda, entonces la posible razón es que FirstOrDefault de IQueryable genera el TOP 1 de SQL, lo que a su vez puede hacer que el optimizador de SQL use bucles anidados en lugar de coincidencias de hash.