Entity Framework Tutorial Mejorar EF Incluir Rendimiento

¿Cómo mejorar el marco de la entidad incluir el rendimiento?

Entity Framework hace un gran trabajo para generar SQL. Sin embargo, a menudo están muy lejos para ser optimizados.

using (var ctx = new CustomerContext())
{
    var customers = ctx.Customers
        .Include(x => x.Orders.Select(y => y.Items.Select(z => z.Product))
        .Include(x => x.Payments.Select(y => y.Items)
        .Include(x => x.Shippings.Select(y => y.Items)
        .ToList();
}

Responder

SPLIT la consulta LINQ en múltiples consultas
USE EF + Query IncludeOptimized ( Recomendado )

SPLIT la consulta LINQ en múltiples consultas

No tiene que incluir todo en la misma consulta, ¡la estrategia de dividir y conquistar puede aplicarse aquí también!

Pros

Convierte la consulta de BIG monster generada por Entity Framework en múltiples monstruos pequeños
Devuelve menos valor NULL

Contras

A veces puede ser más lento que incluir
No se puede utilizar con AsNoTracking ()
Se requieren varios viajes de ida y vuelta a la base de datos.

¿Cómo?

SPLIT las consultas en múltiples consultas más pequeñas
¡Hecho!

using (var ctx = new CustomerContext())
{
    // 1. SPLIT the queries into multiple smaller queries
    var customers = ctx.Customers
            .Include(x => x.Orders.Select(y => y.Items.Select(z => z.Product))
            .ToList();
                    
    var customers = ctx.Customers
            .Include(x => x.Payments.Select(y => y.Items)
            .ToList();
                       
    var customers = ctx.Customers
            .Include(x => x.Shippings.Select(y => y.Items)
            .ToList();
                     
    // 2. Done!
}

USE EF + Query IncludeOptimized ( Recomendado )

La biblioteca de Entity Framework Plus contiene el método de extensión IncludeOptimized que, además, divide la consulta en múltiples consultas, pero mucho más:

Más fácil
Más rápido
Flexible

Pros

Más fácil de usar que las consultas de división
Se requiere una base de datos de ida y vuelta
Bonus: Permite filtrar entidades relacionadas

Contras

A veces puede ser más lento que incluir
No se puede utilizar con AsNoTracking ()
No se puede mezclar con Incluir

¿Cómo?

CAMBIAR todos los Incluir por IncludeOptimized
¡Hecho!

using (var ctx = new CustomerContext())
{
    // 1. CHANGE all Include by IncludeOptimized
    var customers = ctx.Customers
            .IncludeOptimized(x => x.Orders.Select(y => y.Items.Select(z => z.Product))
            .IncludeOptimized(x => x.Payments.Select(y => y.Items)
            .IncludeOptimized(x => x.Shippings.Select(y => y.Items)
            .ToList();
                       
    // 2. Done!
}

SQL generado para Include vs IncludeOptimized

Las personas que miran el SQL generado a menudo se quedan sin palabras.

SELECT
[UnionAll2].[ID] AS [C1],
[UnionAll2].[ID1] AS [C2],
[UnionAll2].[Code] AS [C3],
[UnionAll2].[CreatedDate] AS [C4],
[UnionAll2].[C1] AS [C5],
[UnionAll2].[ID2] AS [C6],
[UnionAll2].[ID3] AS [C7],
[UnionAll2].[Code1] AS [C8],
[UnionAll2].[Customer_ID] AS [C9],
[UnionAll2].[C2] AS [C10],
[UnionAll2].[ID4] AS [C11],
[UnionAll2].[ID5] AS [C12],
[UnionAll2].[Code2] AS [C13],
[UnionAll2].[ID6] AS [C14],
[UnionAll2].[Code3] AS [C15],
[UnionAll2].[Order_ID] AS [C16],
[UnionAll2].[C3] AS [C17],
[UnionAll2].[C4] AS [C18],
[UnionAll2].[C5] AS [C19],
[UnionAll2].[C6] AS [C20],
[UnionAll2].[C7] AS [C21],
[UnionAll2].[C8] AS [C22],
[UnionAll2].[C9] AS [C23],
[UnionAll2].[C10] AS [C24],
[UnionAll2].[C11] AS [C25],
[UnionAll2].[C12] AS [C26],
[UnionAll2].[C13] AS [C27],
[UnionAll2].[C14] AS [C28],
[UnionAll2].[C15] AS [C29],
[UnionAll2].[C16] AS [C30],
[UnionAll2].[C17] AS [C31],
[UnionAll2].[C18] AS [C32],
[UnionAll2].[C19] AS [C33],
[UnionAll2].[C20] AS [C34]
FROM (SELECT
CASE WHEN ([Join2].[ID1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
[Extent1].[ID] AS [ID],
[Extent1].[ID] AS [ID1],
[Extent1].[Code] AS [Code],
[Extent1].[CreatedDate] AS [CreatedDate],
[Join2].[ID1] AS [ID2],
[Join2].[ID1] AS [ID3],
[Join2].[Code1] AS [Code1],
[Join2].[Customer_ID] AS [Customer_ID],
CASE WHEN ([Join2].[ID1] IS NULL) THEN CAST(NULL AS int) WHEN ([Join2].[ID2] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2],
[Join2].[ID2] AS [ID4],
[Join2].[ID2] AS [ID5],
[Join2].[Code2] AS [Code2],
[Join2].[ID3] AS [ID6],
[Join2].[Code3] AS [Code3],
[Join2].[Order_ID] AS [Order_ID],
CAST(NULL AS int) AS [C3],
CAST(NULL AS int) AS [C4],
CAST(NULL AS varchar(1)) AS [C5],
CAST(NULL AS int) AS [C6],
CAST(NULL AS int) AS [C7],
CAST(NULL AS int) AS [C8],
CAST(NULL AS int) AS [C9],
CAST(NULL AS varchar(1)) AS [C10],
CAST(NULL AS int) AS [C11],
CAST(NULL AS int) AS [C12],
CAST(NULL AS int) AS [C13],
CAST(NULL AS varchar(1)) AS [C14],
CAST(NULL AS int) AS [C15],
CAST(NULL AS int) AS [C16],
CAST(NULL AS int) AS [C17],
CAST(NULL AS int) AS [C18],
CAST(NULL AS varchar(1)) AS [C19],
CAST(NULL AS int) AS [C20]
FROM [dbo].[EF6_Performance_Include_IncludeOptimized_Customer] AS [Extent1]
LEFT OUTER JOIN (SELECT [Extent2].[ID] AS [ID1], [Extent2].[Code] AS [Code1], [Extent2].[Customer_ID] AS [Customer_ID], [Join1].[ID2], [Join1].[Code2], [Join1].[Order_ID], [Join1].[ID3], [Join1].[Code3]
FROM [dbo].[EF6_Performance_Include_IncludeOptimized_Order] AS [Extent2]
LEFT OUTER JOIN (SELECT [Extent3].[ID] AS [ID2], [Extent3].[Code] AS [Code2], [Extent3].[Order_ID] AS [Order_ID], [Extent4].[ID] AS [ID3], [Extent4].[Code] AS [Code3]
FROM [dbo].[EF6_Performance_Include_IncludeOptimized_OrderItem] AS [Extent3]
LEFT OUTER JOIN [dbo].[EF6_Performance_Include_IncludeOptimized_Product] AS [Extent4] ON [Extent3].[Product_ID] = [Extent4].[ID] ) AS [Join1] ON [Extent2].[ID] = [Join1].[Order_ID] ) AS [Join2] ON [Extent1].[ID] = [Join2].[Customer_ID]
UNION ALL
SELECT
2 AS [C1],
[Extent5].[ID] AS [ID],
[Extent5].[ID] AS [ID1],
[Extent5].[Code] AS [Code],
[Extent5].[CreatedDate] AS [CreatedDate],
CAST(NULL AS int) AS [C2],
CAST(NULL AS int) AS [C3],
CAST(NULL AS varchar(1)) AS [C4],
CAST(NULL AS int) AS [C5],
CAST(NULL AS int) AS [C6],
CAST(NULL AS int) AS [C7],
CAST(NULL AS int) AS [C8],
CAST(NULL AS varchar(1)) AS [C9],
CAST(NULL AS int) AS [C10],
CAST(NULL AS varchar(1)) AS [C11],
CAST(NULL AS int) AS [C12],
[Join4].[ID4] AS [ID2],
[Join4].[ID4] AS [ID3],
[Join4].[Code4] AS [Code1],
[Join4].[Customer_ID] AS [Customer_ID],
CASE WHEN ([Join4].[ID5] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C13],
[Join4].[ID5] AS [ID4],
[Join4].[ID5] AS [ID5],
[Join4].[Code5] AS [Code2],
[Join4].[Payment_ID] AS [Payment_ID],
CAST(NULL AS int) AS [C14],
CAST(NULL AS int) AS [C15],
CAST(NULL AS varchar(1)) AS [C16],
CAST(NULL AS int) AS [C17],
CAST(NULL AS int) AS [C18],
CAST(NULL AS int) AS [C19],
CAST(NULL AS int) AS [C20],
CAST(NULL AS varchar(1)) AS [C21],
CAST(NULL AS int) AS [C22]
FROM [dbo].[EF6_Performance_Include_IncludeOptimized_Customer] AS [Extent5]
INNER JOIN (SELECT [Extent6].[ID] AS [ID4], [Extent6].[Code] AS [Code4], [Extent6].[Customer_ID] AS [Customer_ID], [Extent7].[ID] AS [ID5], [Extent7].[Code] AS [Code5], [Extent7].[Payment_ID] AS [Payment_ID]
FROM [dbo].[EF6_Performance_Include_IncludeOptimized_Payment] AS [Extent6]
LEFT OUTER JOIN [dbo].[EF6_Performance_Include_IncludeOptimized_PaymentItem] AS [Extent7] ON [Extent6].[ID] = [Extent7].[Payment_ID] ) AS [Join4] ON [Extent5].[ID] = [Join4].[Customer_ID]
UNION ALL
SELECT
3 AS [C1],
[Extent8].[ID] AS [ID],
[Extent8].[ID] AS [ID1],
[Extent8].[Code] AS [Code],
[Extent8].[CreatedDate] AS [CreatedDate],
CAST(NULL AS int) AS [C2],
CAST(NULL AS int) AS [C3],
CAST(NULL AS varchar(1)) AS [C4],
CAST(NULL AS int) AS [C5],
CAST(NULL AS int) AS [C6],
CAST(NULL AS int) AS [C7],
CAST(NULL AS int) AS [C8],
CAST(NULL AS varchar(1)) AS [C9],
CAST(NULL AS int) AS [C10],
CAST(NULL AS varchar(1)) AS [C11],
CAST(NULL AS int) AS [C12],
CAST(NULL AS int) AS [C13],
CAST(NULL AS int) AS [C14],
CAST(NULL AS varchar(1)) AS [C15],
CAST(NULL AS int) AS [C16],
CAST(NULL AS int) AS [C17],
CAST(NULL AS int) AS [C18],
CAST(NULL AS int) AS [C19],
CAST(NULL AS varchar(1)) AS [C20],
CAST(NULL AS int) AS [C21],
[Join6].[ID6] AS [ID2],
[Join6].[ID6] AS [ID3],
[Join6].[Code6] AS [Code1],
[Join6].[Customer_ID] AS [Customer_ID],
CASE WHEN ([Join6].[ID7] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C22],
[Join6].[ID7] AS [ID4],
[Join6].[ID7] AS [ID5],
[Join6].[Code7] AS [Code2],
[Join6].[Shipping_ID] AS [Shipping_ID]
FROM [dbo].[EF6_Performance_Include_IncludeOptimized_Customer] AS [Extent8]
INNER JOIN (SELECT [Extent9].[ID] AS [ID6], [Extent9].[Code] AS [Code6], [Extent9].[Customer_ID] AS [Customer_ID], [Extent10].[ID] AS [ID7], [Extent10].[Code] AS [Code7], [Extent10].[Shipping_ID] AS [Shipping_ID]
FROM [dbo].[EF6_Performance_Include_IncludeOptimized_Shipping] AS [Extent9]
LEFT OUTER JOIN [dbo].[EF6_Performance_Include_IncludeOptimized_ShippingItem] AS [Extent10] ON [Extent9].[ID] = [Extent10].[Shipping_ID] ) AS [Join6] ON [Extent8].[ID] = [Join6].[Customer_ID]) AS [UnionAll2]
ORDER BY [UnionAll2].[ID1] ASC, [UnionAll2].[C1] ASC, [UnionAll2].[ID3] ASC, [UnionAll2].[C2] ASC, [UnionAll2].[C4] ASC, [UnionAll2].[C7] ASC, [UnionAll2].[C13] ASC, [UnionAll2].[C16] ASC

¿Desplegó realmente todo el código para ver todo el SQL generado para ejecutar esta consulta?

¿Sorprendido? ¿Asombrado? ¿Asombrado?

Sí, esto es lo que yo llamo un monstruo SQL, y si lo ejecutas, la mayoría de las columnas contendrá un valor NULO que lo hace aún peor.

Veamos ahora cuál es el SQL generado por el método IncludeOptimized

//EF+ Query Future: 1 of 4

SELECT
[Project2].[ID] AS [ID],
[Project2].[C2] AS [C1],
[Project2].[ID1] AS [ID1],
[Project2].[C1] AS [C2],
[Project2].[ID2] AS [ID2],
[Project2].[Code] AS [Code]
FROM ( SELECT
[Extent1].[ID] AS [ID],
[Join2].[ID1] AS [ID1],
[Join2].[ID2] AS [ID2],
[Join2].[Code1] AS [Code],
[Join2].[C1] AS [C1],
CASE WHEN ([Join2].[ID1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
FROM [dbo].[EF6_Performance_Include_IncludeOptimized_Customer] AS [Extent1]
LEFT OUTER JOIN (SELECT [Extent2].[ID] AS [ID1], [Extent2].[Customer_ID] AS [Customer_ID], [Project1].[ID] AS [ID2], [Project1].[Code] AS [Code1], [Project1].[C1] AS [C1]
FROM [dbo].[EF6_Performance_Include_IncludeOptimized_Order] AS [Extent2]
LEFT OUTER JOIN (SELECT
[Extent3].[Order_ID] AS [Order_ID],
[Extent4].[ID] AS [ID],
[Extent4].[Code] AS [Code],
1 AS [C1]
FROM [dbo].[EF6_Performance_Include_IncludeOptimized_OrderItem] AS [Extent3]
LEFT OUTER JOIN [dbo].[EF6_Performance_Include_IncludeOptimized_Product] AS [Extent4] ON [Extent3].[Product_ID] = [Extent4].[ID] ) AS [Project1] ON [Extent2].[ID] = [Project1].[Order_ID] ) AS [Join2] ON [Extent1].[ID] = [Join2].[Customer_ID]
) AS [Project2]
ORDER BY [Project2].[ID] ASC, [Project2].[C2] ASC, [Project2].[ID1] ASC, [Project2].[C1] ASC
;

// EF+ Query Future: 2 of 4

SELECT
[Project1].[ID] AS [ID],
[Project1].[C2] AS [C1],
[Project1].[ID1] AS [ID1],
[Project1].[C1] AS [C2],
[Project1].[ID2] AS [ID2],
[Project1].[Code] AS [Code],
[Project1].[Payment_ID] AS [Payment_ID]
FROM ( SELECT
[Extent1].[ID] AS [ID],
[Join1].[ID1] AS [ID1],
[Join1].[ID2] AS [ID2],
[Join1].[Code1] AS [Code],
[Join1].[Payment_ID] AS [Payment_ID],
CASE WHEN ([Join1].[ID1] IS NULL) THEN CAST(NULL AS int) WHEN ([Join1].[ID2] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
CASE WHEN ([Join1].[ID1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
FROM [dbo].[EF6_Performance_Include_IncludeOptimized_Customer] AS [Extent1]
LEFT OUTER JOIN (SELECT [Extent2].[ID] AS [ID1], [Extent2].[Customer_ID] AS [Customer_ID], [Extent3].[ID] AS [ID2], [Extent3].[Code] AS [Code1], [Extent3].[Payment_ID] AS [Payment_ID]
FROM [dbo].[EF6_Performance_Include_IncludeOptimized_Payment] AS [Extent2]
LEFT OUTER JOIN [dbo].[EF6_Performance_Include_IncludeOptimized_PaymentItem] AS [Extent3] ON [Extent2].[ID] = [Extent3].[Payment_ID] ) AS [Join1] ON [Extent1].[ID] = [Join1].[Customer_ID]
) AS [Project1]
ORDER BY [Project1].[ID] ASC, [Project1].[C2] ASC, [Project1].[ID1] ASC, [Project1].[C1] ASC
;

//EF+ Query Future: 3 of 4

SELECT
[Project1].[ID] AS [ID],
[Project1].[C2] AS [C1],
[Project1].[ID1] AS [ID1],
[Project1].[C1] AS [C2],
[Project1].[ID2] AS [ID2],
[Project1].[Code] AS [Code],
[Project1].[Shipping_ID] AS [Shipping_ID]
FROM ( SELECT
[Extent1].[ID] AS [ID],
[Join1].[ID1] AS [ID1],
[Join1].[ID2] AS [ID2],
[Join1].[Code1] AS [Code],
[Join1].[Shipping_ID] AS [Shipping_ID],
CASE WHEN ([Join1].[ID1] IS NULL) THEN CAST(NULL AS int) WHEN ([Join1].[ID2] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
CASE WHEN ([Join1].[ID1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
FROM [dbo].[EF6_Performance_Include_IncludeOptimized_Customer] AS [Extent1]
LEFT OUTER JOIN (SELECT [Extent2].[ID] AS [ID1], [Extent2].[Customer_ID] AS [Customer_ID], [Extent3].[ID] AS [ID2], [Extent3].[Code] AS [Code1], [Extent3].[Shipping_ID] AS [Shipping_ID]
FROM [dbo].[EF6_Performance_Include_IncludeOptimized_Shipping] AS [Extent2]
LEFT OUTER JOIN [dbo].[EF6_Performance_Include_IncludeOptimized_ShippingItem] AS [Extent3] ON [Extent2].[ID] = [Extent3].[Shipping_ID] ) AS [Join1] ON [Extent1].[ID] = [Join1].[Customer_ID]
) AS [Project1]
ORDER BY [Project1].[ID] ASC, [Project1].[C2] ASC, [Project1].[ID1] ASC, [Project1].[C1] ASC
;

//EF+ Query Future: 4 of 4

SELECT
[Extent1].[ID] AS [ID],
[Extent1].[Code] AS [Code],
[Extent1].[CreatedDate] AS [CreatedDate]
FROM [dbo].[EF6_Performance_Include_IncludeOptimized_Customer] AS [Extent1]
ORDER BY [Extent1].[ID] ASC;

Eso seguro que no es el SQL más optimizado, pero ya es mucho más legible.

Prime Library

Performance

Expression Evaluator

More Projects...

Want to use EF6 in .NET Core?

Entity Framework Tutorial Mejorar EF Incluir Rendimiento

¿Cómo mejorar el marco de la entidad incluir el rendimiento?

Responder

SPLIT la consulta LINQ en múltiples consultas

Pros

Contras

¿Cómo?

USE EF + Query IncludeOptimized ( Recomendado )

Pros

Contras

¿Cómo?

SQL generado para Include vs IncludeOptimized

Performance

Expression Evaluator

Related

Want to use EF6 in .NET Core?

Entity Framework Tutorial Mejorar EF Incluir Rendimiento

¿Cómo mejorar el marco de la entidad incluir el rendimiento?

Preguntas relacionadas con StackOverflow

Responder

SPLIT la consulta LINQ en múltiples consultas

Pros

Contras

¿Cómo?

USE EF + Query IncludeOptimized ( Recomendado )

Pros

Contras

¿Cómo?

SQL generado para Include vs IncludeOptimized

Performance

Expression Evaluator

Related