Entity Framework EF 성능 향상
Entity Framework의 성능 향상 방법
Entity Framework는 SQL을 생성하는 데 매우 효과적입니다. 그러나, 그들은 종종 아주 멀리까지 최적화됩니다.
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(); }
StackOverflow 관련 질문
- Entity Framework 성능 포함
- Include ()를 여러 번 사용할 때 엔티티 프레임 워크 코드가 느립니다.
- Entity Framework. 성능 문제 포함
- Entity Framework 성능 포함
대답
- 여러 쿼리에서 LINQ 쿼리를 분할합니다.
- USE EF + Query IncludeOptimized 사용 ( 권장 )
LINQ 쿼리를 여러 쿼리로 분할
동일한 검색어에 모든 것을 포함시킬 필요는 없으며 분할 및 정복 전략도 여기에 적용될 수 있습니다!
찬성
- Entity Framework에서 생성 된 BIG 괴물 쿼리를 여러 개의 작은 몬스터로 변환하십시오.
- 적은 NULL 값을 반환합니다.
단점
- 때때로 Include보다 느릴 수 있습니다.
- AsNoTracking ()과 함께 사용할 수 없습니다.
- 여러 데이터베이스 왕복이 필요합니다.
방법?
- 여러 개의 작은 쿼리에서 쿼리를 분할하십시오.
- 끝난!
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 사용 ( 권장 )
Entity Framework Plus 라이브러리에는 IncludeOptimized 확장 메서드가 포함되어 있습니다.이 메서드는 후드 아래에서 쿼리를 여러 쿼리로 나눕니다.
- 더 쉬운
- 빨리
- 융통성 있는
찬성
- 쿼리를 분할하는 것보다 사용하기 쉽습니다.
- 하나의 데이터베이스 왕복이 필요합니다.
- 보너스 : 관련 엔티티 필터링 허용
단점
- 때때로 Include보다 느릴 수 있습니다.
- AsNoTracking ()과 함께 사용할 수 없습니다.
- Include와 섞일 수 없다.
방법?
- IncludeOptimized 포함 모두 변경
- 끝난!
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! }
Include 대 IncludeOptimized 용으로 생성 된 SQL
생성 된 SQL을 보는 사람들은 종종 말문이됩니다.
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
이 쿼리를 실행하기 위해 생성 된 모든 SQL을보기 위해 모든 코드를 실제로 스크롤 했습니까?
고작? 놀랐어? 깜짝 놀라게 하는?
네, 이것은 제가 괴물 SQL이라고 부르는 것입니다. 그리고 그것을 실행하면, 대부분의 열은 NULL 값을 포함하게되어 더욱 악화됩니다.
이제 IncludeOptimized 메소드에 의해 생성 된 SQL이 무엇인지 살펴 보겠습니다.
//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;
그것은 가장 최적화 된 SQL은 아니지만 이미 더 쉽게 읽을 수 있습니다.
