Entity Framework Core 7 Performance-Optimierung aus der Praxis Pawel Gerr @pawelgerr [email protected]

Entity Framework Core Performance-Optimierung aus der Praxis • Optimization options and KPIs • Reduction of database requests • Reduction of query complexity • Query statistics and execution plans Agenda

Entity Framework Core Performance-Optimierung aus der Praxis Example for today – Products DB Products Prices Sellers Studio

Entity Framework Core Performance-Optimierung aus der Praxis Database Schema

Entity Framework Core Performance-Optimierung aus der Praxis My usual options for Entity Framework performance optimization 2nd level cache Hardware upgrade Materialized views Stored procedures Triggers Database Features Server Raw SELECT * FROM … Raw SQL Bulk operations Indices Entity Framework Requests reduction Query restructure Use 3rd party libs if available CPU, Memory, I/O

Entity Framework Core Performance-Optimierung aus der Praxis Did „the change“ anything good? – My KPI Execution time (LINQ) SELECT * FROM … SELECT * FROM … SELECT * FROM … Number of requests Query statistics SELECT * FROM … “Shape” of SQL Execution plans Parse time Compile time Execution time (DB) Physical reads Logical reads Row count Round trip time SQL generation Execution time (DB) Materialization My key performance indicators

Entity Framework Core Performance-Optimierung aus der Praxis Reducing database requests

Entity Framework Core Performance-Optimierung aus der Praxis N+1 queries problem Execution of hundreds of requests for one use case Caused by: unintentional execution of queries in loops Possible solutions: • Better code structure (software architecture) • Avoid overgeneralization (shared/core projects) • Help each other to improve (review/feedback) • Know the insights of Entity Framework • Lazy loading • Limitations Reducing database requests Demos 1.1 + 1.2

Simplified case of indirect execution of database requests in a loop Library Main Entity Framework Core Performance-Optimierung aus der Praxis Multiple requests in loops Reducing database requests var products = LoadProducts(); var prices = new List(); foreach (var product in products) { var price = GetPrice(product.Id); prices.Add(price); } public Price GetPrice(Guid productId) { MyDbContext ctx = …; return ctx.Prices.FirstOrDefault(p => p.ProductId == productId); } Executed N times

Entity Framework Core Performance-Optimierung aus der Praxis Multiple requests in loops (Logs) Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30’] SELECT * FROM [Products] AS [p] Executed DbCommand (21ms) [Parameters=[@__productId_0='1'], CommandType='Text', CommandTimeout='30’] SELECT TOP(1) * FROM [Prices] AS [p] WHERE [p].[ProductId] = @__productId_0 Executed DbCommand (7ms) [Parameters=[@__productId_0='2'], CommandType='Text', CommandTimeout='30’] SELECT TOP(1) * FROM [Prices] AS [p] WHERE [p].[ProductId] = @__productId_0 … Executed DbCommand (3ms) [Parameters=[@__productId_0='99'], CommandType='Text', CommandTimeout='30’] SELECT TOP(1) * FROM [Prices] AS [p] WHERE [p].[ProductId] = @__productId_0 Executed DbCommand (4ms) [Parameters=[@__productId_0='100'], CommandType='Text', CommandTimeout='30’] SELECT TOP(1) * FROM [Prices] AS [p] WHERE [p].[ProductId] = @__productId_0 Reducing database requests

Library Main Entity Framework Core Performance-Optimierung aus der Praxis LINQ queries are loops Reducing database requests var products = LoadProducts(); var prices = products.Select(product => GetPrice(product.Id)) .ToList(); public Price GetPrice(Guid productId) { MyDbContext ctx = …; return ctx.Prices.FirstOrDefault(p => p.ProductId == productId); }

Entity Framework Core Performance-Optimierung aus der Praxis LINQ queries are loops (Logs are same as before) Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30’] SELECT * FROM [Products] AS [p] Executed DbCommand (21ms) [Parameters=[@__productId_0='1'], CommandType='Text', CommandTimeout='30’] SELECT TOP(1) * FROM [Prices] AS [p] WHERE [p].[ProductId] = @__productId_0 Executed DbCommand (7ms) [Parameters=[@__productId_0='2'], CommandType='Text', CommandTimeout='30’] SELECT TOP(1) * FROM [Prices] AS [p] WHERE [p].[ProductId] = @__productId_0 … Executed DbCommand (3ms) [Parameters=[@__productId_0='99'], CommandType='Text', CommandTimeout='30’] SELECT TOP(1) * FROM [Prices] AS [p] WHERE [p].[ProductId] = @__productId_0 Executed DbCommand (4ms) [Parameters=[@__productId_0='100'], CommandType='Text', CommandTimeout='30’] SELECT TOP(1) * FROM [Prices] AS [p] WHERE [p].[ProductId] = @__productId_0 Reducing database requests

Library Main Entity Framework Core Performance-Optimierung aus der Praxis Approach 1 – Use-case specific methods Reducing database requests var productsWithPrices = LoadProductsWithPrices(); public List LoadProductsWithPrices() { return ctx.Products .Include(p => p.Prices) // merely symbolic, fetching prices is usually more complex .ToList(); } Approach 1.1

Entity Framework Core Performance-Optimierung aus der Praxis Approach 1 – Use-case specific methods (Logs) Executed DbCommand (23ms) [Parameters=[], CommandType='Text', CommandTimeout='30’] SELECT [p].*, [p0].* FROM [Products] AS [p] LEFT JOIN [Prices] AS [p0] ON [p].[Id] = [p0].[ProductId] ORDER BY [p].[Id] Reducing database requests

Library Main var products = LoadProducts(); var productIds = products.Select(p => p.Id); var pricesByProductId = GetPrices(productIds); public ILookup GetPrices(IEnumerable productIds) { return ctx.Prices .Where(p => productIds.Contains(p.ProductId)) .ToLookup(p => p.ProductId); } Entity Framework Core Performance-Optimierung aus der Praxis Approach 2 – load all required prices at once Reducing database requests Approach 1.2

Entity Framework Core Performance-Optimierung aus der Praxis Approach 2 – load all required prices at once (Logs) Executed DbCommand (32ms) [Parameters=[], CommandType='Text', CommandTimeout='30’] SELECT * FROM [Products] AS [p] Executed DbCommand (17ms) [Parameters=[], CommandType='Text', CommandTimeout='30’] SELECT * FROM [Prices] AS [p] WHERE [p].[ProductId] IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100) -- Entity Framework Core 8 Executed DbCommand (22ms) [Parameters=[@__productIds_0='[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35, 36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,7 8,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100]' (Size = 4000)], CommandType='Text', CommandTimeout='30’] SELECT * FROM [Prices] AS [p] WHERE [p].[ProductId] IN (SELECT [p0].[value] FROM OPENJSON(@__productIds_0) WITH ([value] int '$') AS [p0] Reducing database requests

Given: 100 products being in 5 studio Loading of all products “in one go” leads to 1 + 5 queries Library Main var productsByStudio = ctx.Products .Include(p => p.Studio) .ToLookup(p => p.Studio.Name); Entity Framework Core Performance-Optimierung aus der Praxis Lazy loading Reducing database requests public static ILookup ToLookup( this IEnumerable source, Func keySelector) { ... } Use eager loading to prevent unnecessary requests IEnumerable always forces query execution Demo 1.3 Approach 1.3

Entity Framework Core Performance-Optimierung aus der Praxis Lazy loading (Logs) Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30’] SELECT * FROM [Products] AS [p] Executed DbCommand (29ms) [Parameters=[@__p_0='2'], CommandType='Text', CommandTimeout='30’] SELECT * FROM [Studios] AS [s] WHERE [s].[Id] = @__p_0 Executed DbCommand (5ms) [Parameters=[@__p_0='3'], CommandType='Text', CommandTimeout='30’] SELECT * FROM [Studios] AS [s] WHERE [s].[Id] = @__p_0 Executed DbCommand (11ms) [Parameters=[@__p_0='4'], CommandType='Text', CommandTimeout='30’] SELECT * FROM [Studios] AS [s] WHERE [s].[Id] = @__p_0 Executed DbCommand (1ms) [Parameters=[@__p_0='5'], CommandType='Text', CommandTimeout='30’] SELECT * FROM [Studios] AS [s] WHERE [s].[Id] = @__p_0 Executed DbCommand (1ms) [Parameters=[@__p_0='1'], CommandType='Text', CommandTimeout='30’] SELECT * FROM [Studios] AS [s] WHERE [s].[Id] = @__p_0 -- With Eager Loading (Include) Executed DbCommand (21ms) [Parameters=[], CommandType='Text', CommandTimeout='30’] SELECT [p].*, [s].* FROM [Products] AS [p] INNER JOIN [Studios] AS [s] ON [p].[StudioId] = [s].[Id] Reducing database requests

Entity Framework Core Performance-Optimierung aus der Praxis Finding the problem area Use profiling tools In case of Microsoft SQL Server: XEvent Profiler in SQL Server Management Studio SQL Server Profiler (deprecated, resource intensive) Azure Data Studio Logs coming from EF with EnableSensitiveDataLogging Reducing database requests Logging DbContextOptionsBuilder builder = ...; builder.UseSqlServer("...") // or any other database .UseLoggerFactory(loggerFactory) .EnableSensitiveDataLogging(); In development only because possible security leak!

Entity Framework Core Performance-Optimierung aus der Praxis Finding the problem area Look for repetitive lines and cycles Reducing database requests Repetive lines Cycles

Query tags: correlation between LINQ and SQL LINQ SQL Entity Framework Core Performance-Optimierung aus der Praxis Finding the problem area Reducing database requests var products = ctx.Products .TagWith("My Query") .ToList(); -- My Query SELECT * FROM Products

Entity Framework Core Performance-Optimierung aus der Praxis Reducing query complexity

Entity Framework Core Performance-Optimierung aus der Praxis Cartesian explosion problem Fetching a lot of data from multiple tables at once • Increased memory consumption and execution time • High I/O load Caused by: loading of multiple navigational collection-properties Possible solutions: • Reduce Includes (eager loading) • Reduce access to navigational properties in projections (i.e. Select) Reducing query complexity Query splitting Demo 2.1

Entity Framework Core Performance-Optimierung aus der Praxis Eager loading via Include EF 7 loads all data using 1 SQL statement by default (QuerySplittingBehavior.SingleQuery) • Bigger result sets: 100 products with 10 prices each and 2 sellers (incl. 200 product-seller-relationship) (100 * 10 * 2) rows > (100 + 1000 + 2 + 200) rows • EF-forced ORDER BY clause produces considerable load Reducing query complexity Product Id Price Id Seller Id 1 1 1 1 1 2 1 2 1 1 2 2 1 3 1 1 3 2 SQL LINQ var products = ctx.Products .Include(p => p.Studio) .Include(p => p.Prices) .Include(p => p.Sellers) .ToList(); SELECT * FROM Products INNER JOIN Studios ... LEFT JOIN Prices ON ... LEFT JOIN Sellers ON ... ... ORDER BY Products.Id, Studios.Id, Prices.Id, … Cartesian explosion

EF 7 allows to change the QuerySplittingBehavior from SingleQuery to SplitQuery Entity Framework Core Performance-Optimierung aus der Praxis Include: query splitting by EF Reducing query complexity LINQ SQL var products = ctx.Products.AsSplitQuery().Include(p => p.Studio).Include(p => p.Prices).Include(p => p.Sellers).ToList(); SELECT [p].*, [s].* FROM [Products] AS [p] INNER JOIN [Studios] AS [s] ON [p].[StudioId] = [s].[Id] ORDER BY [p].[Id], [s].[Id] SELECT [p0].*, [p].[Id], [s].[Id] FROM [Products] AS [p] INNER JOIN [Studios] AS [s] ON [p].[StudioId] = [s].[Id] INNER JOIN [Prices] AS [p0] ON [p].[Id] = [p0].[ProductId] ORDER BY [p].[Id], [s].[Id] SELECT [t].*, [p].[Id], [s].[Id] FROM [Products] AS [p] INNER JOIN [Studios] AS [s] ON [p].[StudioId] = [s].[Id] INNER JOIN ( SELECT * FROM [SellerProducts] AS [s0] INNER JOIN [Sellers] AS [s1] ON [s0].[SellerId] = [s1].[Id] ) AS [t] ON [p].[Id] = [t].[ProductId] ORDER BY [p].[Id], [s].[Id] Approach 2.1

Entity Framework Core Performance-Optimierung aus der Praxis Fetching collections in projections Cartesian Explosion due to fetching of collection in Select Reducing query complexity LINQ var result = _ctx.Studios .Select(s => new { Studio = s, Infinity = s.Products .Where(p => p.Name.Contains("Infinity")) .Select(p => new { Product = p, p.Prices, p.Sellers }), Endgame = s.Products .Where(p => p.Name.Contains("Endgame")) .Select(p => new { Product = p, p.Prices, p.Sellers }) }) .ToList(); Demo 2.2 Before Statistics: • CPU: 1766 • Duration: 3180 • Reads: 20773 • Row Count: 200000

Entity Framework Core Performance-Optimierung aus der Praxis Fetching collections in projections (Logs) Executed DbCommand (936ms) [Parameters=[], CommandType='Text', CommandTimeout='30’] SELECT * FROM [Studios] AS [s] LEFT JOIN ( SELECT * FROM [Products] AS [p] LEFT JOIN [Prices] AS [p0] ON [p].[Id] = [p0].[ProductId] LEFT JOIN (SELECT * FROM [SellerProducts] AS [s0] INNER JOIN [Sellers] AS [s1] ON [s0].[SellerId] = [s1].[Id]) AS [t] ON [p].[Id] = [t].[ProductId] WHERE [p].[Name] LIKE N'%Infinity%' ) AS [t0] ON [s].[Id] = [t0].[StudioId] LEFT JOIN ( SELECT * FROM [Products] AS [p1] LEFT JOIN [Prices] AS [p2] ON [p1].[Id] = [p2].[ProductId] LEFT JOIN (SELECT * FROM [SellerProducts] AS [s2] INNER JOIN [Sellers] AS [s3] ON [s2].[SellerId] = [s3].[Id]) AS [t2] ON [p1].[Id] = [t2].[ProductId] WHERE [p1].[Name] LIKE N'%Endgame%' ) AS [t1] ON [s].[Id] = [t1].[StudioId] ORDER BY [s].[Id], [t0].[Id], [t0].[Id0], [t0].[ProductId0], [t0].[SellerId], [t0].[Id1], [t1].[Id], [t1].[Id0], [t1].[ProductId0], [t1].[SellerId] Reducing query complexity

Entity Framework Core Performance-Optimierung aus der Praxis Projections: query splitting by EF Cartesian Explosion due to fetching of collection in Select Reducing query complexity LINQ var result = _ctx.Studios .AsSplitQuery() .Select(s => new { Studio = s, Infinity = s.Products .Where(p => p.Name.Contains("Infinity")) .Select(p => new { Product = p, p.Prices, p.Sellers }), Endgame = s.Products .Where(p => p.Name.Contains("Endgame")) .Select(p => new { Product = p, p.Prices, p.Sellers }) }) .ToList(); Approach 2.2 AsSplitQuery Before Statistics: • CPU: 1766 • Duration: 3180 • Reads: 20773 • Row Count: 200000 AsSplitQuery 13 318 568 855

Entity Framework Core Performance-Optimierung aus der Praxis Projections: query splitting by EF (Logs) SELECT [s].[Id], [s].[Name] FROM [Studios] AS [s] ORDER BY [s].[Id] SELECT [t].*, [s].[Id] FROM [Studios] AS [s] INNER JOIN (SELECT * FROM [Products] AS [p] WHERE [p].[Name] LIKE N'%Infinity%') AS [t] ON [s].[Id] = [t].[StudioId] ORDER BY [s].[Id], [t].[Id] SELECT [p0].*, [s].[Id], [t].[Id] FROM [Studios] AS [s] INNER JOIN (SELECT * FROM [Products] AS [p] WHERE [p].[Name] LIKE N'%Infinity%') AS [t] ON [s].[Id] = [t].[StudioId] INNER JOIN [Prices] AS [p0] ON [t].[Id] = [p0].[ProductId] ORDER BY [s].[Id], [t].[Id] SELECT [t0].*, [s].[Id], [t].[Id] FROM [Studios] AS [s] INNER JOIN (SELECT * FROM [Products] AS [p] WHERE [p].[Name] LIKE N'%Infinity%') AS [t] ON [s].[Id] = [t].[StudioId] INNER JOIN (SELECT * FROM [SellerProducts] AS [s0] INNER JOIN [Sellers] AS [s1] ON [s0].[SellerId] = [s1].[Id]) AS [t0] ON [t].[Id] = [t0].[ProductId] ORDER BY [s].[Id], [t].[Id] -- Additional 3 statements for 'Endgame' Reducing query complexity Infinity

Usually, loading collection separately requires more code but less resources on database LINQ Entity Framework Core Performance-Optimierung aus der Praxis Manual query splitting Reducing query complexity var studiosQuery = _ctx.Studios; var studios = studiosQuery.ToList(); var infinityProducts = studiosQuery.SelectMany(s => s.Products).Where(p => p.Name.Contains("Infinity")).ToList(); var endgameProducts = studiosQuery.SelectMany(s => s.Products).Where(p => p.Name.Contains("Endgame")).ToList(); var productIds = infinityProducts.Concat(endgameProducts).Select(p => p.Id); var prices = _ctx.Prices.Where(p => productIds.Contains(p.ProductId)).ToList(); // cannot use "productsQuery.Select(p => p.Sellers)" because JoinTable "Seller_Product" won't be selected // thus Change Tracker won't wire up the property "Sellers" on the products. var sellers = _ctx.SellerProducts.Where(sp => productIds.Contains(sp.ProductId)).Include(sp => sp.Seller).ToList(); // Build the desired data structure in memory Approach 2.2 Manual Splitting Before Statistics: • CPU: 1766 • Duration: 3180 • Reads: 20773 • Row Count: 200000 AsSplitQuery 13 318 568 1305 Manual Splitting 1 270 22 1305

Entity Framework Core Performance-Optimierung aus der Praxis Manual query splitting (Logs) SELECT * FROM [Studios] AS [s] SELECT [p].* FROM [Studios] AS [s] INNER JOIN [Products] AS [p] ON [s].[Id] = [p].[StudioId] WHERE [p].[Name] LIKE N'%Infinity%' SELECT [p].* FROM [Studios] AS [s] INNER JOIN [Products] AS [p] ON [s].[Id] = [p].[StudioId] WHERE [p].[Name] LIKE N'%Endgame%' SELECT * FROM [Prices] AS [p] WHERE [p].[ProductId] IN (2, 4, …, 98, 100, 1, 3, …, 97, 99) SELECT * FROM [SellerProducts] AS [s] INNER JOIN [Sellers] AS [s0] ON [s].[SellerId] = [s0].[Id] WHERE [s].[ProductId] IN (2, 4, …, 98, 100, 1, 3, …, 97, 99) Reducing query complexity

Entity Framework Core Performance-Optimierung aus der Praxis Understanding queries

LINQ 2 LINQ 1 Entity Framework Core Performance-Optimierung aus der Praxis Which one is better? Understanding Queries var studios = ctx.Studios .Select(s => new { s.Products.OrderBy(p => p.Id).FirstOrDefault().Id, s.Products.OrderBy(p => p.Id).FirstOrDefault().Name }) .ToList(); var studios = ctx.Studios .Select(s => s.Products .OrderBy(p => p.Id) .Select(p => new { p.Id, p.Name }) .FirstOrDefault()) .ToList(); 2x “FirstOrDefault()” before selecting properties Selection of properties before “FirstOrDefault()”

SQL 2 SQL 1 Entity Framework Core Performance-Optimierung aus der Praxis Lets try with SQL … Understanding Queries SELECT ( SELECT TOP(1) p.Id FROM Products p WHERE s.Id = p.StudioId ORDER BY p.Id) AS FirstProductId, ( SELECT TOP(1) p.Name FROM Products p WHERE s.Id = p.StudioId ORDER BY p.Id) AS FirstProductName FROM Studios s SELECT p.Id, p.Name FROM Studios s LEFT JOIN ( SELECT Id, Name, StudioId FROM ( SELECT Id, Name, StudioId, ROW_NUMBER() OVER(PARTITION BY StudioId ORDER BY Id) AS row FROM Products ) p WHERE row <= 1 ) p ON s.Id = p.StudioId 2 sub-selects Window function “ROW_NUMBER”

Entity Framework Core Performance-Optimierung aus der Praxis Execution plans

Entity Framework Core Performance-Optimierung aus der Praxis (Textual) Representation of all database operations • Type and the order of operations (JOIN, filter, projection, ...) • Indexes being used • Amount of data flowing between two operations • Costs of an operation and a subtree Some tools have built-in support for displaying execution plans as a graph Execution plans

SQL 1 Entity Framework Core Performance-Optimierung aus der Praxis Another try … Execution plans Execution plan 1 SELECT ( SELECT TOP(1) p.Id FROM Products p WHERE s.Id = p.StudioId ORDER BY p.Id ) AS FirstProductId, ( SELECT TOP(1) p.Name FROM Products p WHERE s.Id = p.StudioId ORDER BY p.Id ) AS FirstProductName FROM Studios s Why not “index seek”?

Entity Framework Core Performance-Optimierung aus der Praxis Execution plans Execution plan 2 SQL 2 SELECT p.Id, p.Name FROM Studios s LEFT JOIN ( SELECT Id, Name, StudioId FROM ( SELECT Id, Name, StudioId, ROW_NUMBER() OVER(PARTITION BY StudioId ORDER BY Id) AS row FROM Products ) p WHERE row <= 1 ) p ON s.Id = p.StudioId Why “index scan” + “sort”?

Entity Framework Core Performance-Optimierung aus der Praxis Comparing • Compare estimated subtree costs • Deviance: estimated vs actual number of rows • Take statistics (reads) into consideration Execution plans Demo 3.1

Entity Framework Core Performance-Optimierung aus der Praxis SQL Server Operators General icon Cursors Parallelism T-SQL Execution plans

Entity Framework Core Performance-Optimierung aus der Praxis Execution plans Crash course Table scan Clustered index scan Non-clustered index scan Clustered index seek Non-clustered index seek Scan No clustered index Seek Filter Filtering Filtering Filtering Key lookup Missing (include) columns? RID lookup If no clustered index Sort “Stop & Go” operator! Required? Missing index? Sorting in .NET cheaper? Parallelism Expensive High query complexity Missing index? Fuzzy search? Bad discriminator?

Entity Framework Core Performance-Optimierung aus der Praxis JOINs Execution plans DB may perform sort on its own • Resource-saving • Data sets must be ordered • The “default” • Kind-of 2 nested loops • Speed depends on: • Scan of data set A • Seek of data set B • Large and unsorted sets • 2 phases: • Builds hash table for set A • Matches hash values from B Should be looked at Merge join Nested loop join Hash match join

Entity Framework Core Performance-Optimierung aus der Praxis Learnings • Look for database requests in “loops” • Split complex queries if necessary • Learn to read execution plans - optimize queries accordingly Resources • Entity Framework Core: https://docs.microsoft.com/en-us/ef/core/ • Execution Plans: https://docs.microsoft.com/en-us/sql/relational-databases/performance/execution-plans • Webinar demos: https://github.com/thinktecture/ef-core-performance-webinar Ask me @pawelgerr [email protected]