Hello everyone, I am Gangzi, a programmer. Last Wednesday afternoon, the planning supervisor called directly: “The order list cannot be opened. Click to scroll for half a minute. The planning clerk is waiting to input materials and schedule production.”
I turned on the monitor and took a look: P95 800ms, peak value 1.2 seconds. Looking at the server again, the database CPU is 40% and the application CPU is 20% – neither is busy, but it is just slow.
This situation of “no one is busy and the interface is slow” is 90% not due to insufficient server performance, butYou are using the most expensive way to get data. Specific to .NET projects, most of the time the problem lies in the query behavior of EF Core.
I have compiled the troubleshooting sequence and correction methods in this article into Chapter 2 of the “.NET Production Environment Pitfall Avoidance Manual”. There are three EF Core accident reviews in total. You can pick it up at the end of the article. Let’s look at the conclusion first: any of the following points can cut down a lot of time if done individually. It takes 800ms to 60ms to complete them all.
1. Don’t change the code yet, take apart 800ms and look at it.
I have seen too many people add cache, add Redis, and change ORM as soon as they start, but in the end they find that they are not looking for the right place at all.
Before changing the code, take three numbers:
| number to take | How to get it |
|---|---|
| The total time spent on the interface | In Action Stopwatch Package one layer, or look at the time-consuming field in APM/log |
| Total SQL execution time | EF Core logs LogToor database side Extended Events |
| How many SQL statements are sent in one request? | Same as above, count the number of log entries – this number is most easily ignored and most illustrative of the problem. |
The third number is the key. I counted it at that time. 217 items。
An interface to “check order list” sent 217 SQL statements. This is not a speed issue, this is a design issue.
How to open EF Core logs (the development environment is sufficient):
optionsBuilder
.UseSqlServer(conn)
.LogTo(Console.WriteLine, new[] { DbLoggerCategory.Database.Command.Name },
LogLevel.Information)
.EnableSensitiveDataLogging(); // 仅开发环境！
⚠️
EnableSensitiveDataLogging()The parameter values will be entered into the log. Do not open it in the production environment – it may contain customer names, prices, and ID numbers.
If you want to see what SQL a certain LINQ is finally translated into, you don’t need to connect to the database:
var sql = query.ToQueryString();
This step only takes ten minutes, but it determines whether all subsequent optimizations are meaningful.
2. Optimization point 1: N+1 query, this is the most expensive one
On-site recurrence
The code looks like this, and there is no problem at all:
// 一页 50 条订单
var orders = await _db.Orders
.Where(o => o.Status == OrderStatus.InProduction)
.OrderByDescending(o => o.CreatedAt)
.Skip((page - 1) * 50).Take(50)
.ToListAsync();
foreach (var o in orders)
{
// 每条订单查客户
o.CustomerName = (await _db.Customers.FindAsync(o.CustomerId))?.Name;
// 每条订单查明细条数
o.ItemCount = await _db.OrderItems.CountAsync(i => i.OrderId == o.Id);
// 每条订单查最新报工记录
o.LastReportTime = await _db.WorkReports
.Where(r => r.OrderId == o.Id)
.MaxAsync(r => (DateTime?)r.ReportedAt) ?? DateTime.MinValue;
// 每条订单查是否有待处理异常
o.HasIssue = await _db.OrderIssues.AnyAsync(x => x.OrderId == o.Id && !x.Resolved);
}
50 orders × 4 queries = 200 times, plus 1 main query,201 round trips. Including the total number of additional checks of the paging component, 217 items are correct.
This is N+1:First check the main table once, and then check N times for each record. A single SQL statement only takes 0.5~2ms, which doesn’t look slow, but 200 round trips add up to 700ms – and most of these 200 times are spent onNetwork round trip and connection context switchingYes, it’s not the database that’s slow.
So you will see the anomaly: the database CPU is 40%, but the interface is 800ms. The CPU is not busy, it is all consumed on the road.
Three modification methods, ranked by degree of recommendation
Modified method 1: Project and check in one go (most recommended)
Leave the work of “filling fields” to SQL and check it all at once:
var list = await _db.Orders
.Where(o => o.Status == OrderStatus.InProduction)
.OrderByDescending(o => o.CreatedAt)
.Skip((page - 1) * 50).Take(50)
.Select(o => new OrderListItemDto
{
Id = o.Id,
OrderNo = o.OrderNo,
CustomerName = o.Customer!.Name,
ItemCount = o.Items.Count,
LastReportTime = o.WorkReports.Max(r => (DateTime?)r.ReportedAt),
HasIssue = o.Issues.Any(x => !x.Resolved)
})
.ToListAsync();
The generated SQL will have several related subqueries or LEFT JOIN.1 piece done. This is my most commonly used way of writing: it not only solves N+1, but also easily takes only the required columns (the third point will explain why this is important).
Modification 2: Include preloading, but be careful about the amount of data
var orders = await _db.Orders
.Include(o => o.Customer)
.Include(o => o.Items)
.AsSplitQuery() // 多个集合 Include 时必须加，后面会讲
.Where(o => o.Status == OrderStatus.InProduction)
.OrderByDescending(o => o.CreatedAt)
.Skip((page - 1) * 50).Take(50)
.ToListAsync();
Include It can reduce the query to 1~3, but it has a price:All columns of the entire row of entities will be found.. There are 60 fields in the order table, but your page only uses 8, leaving 52 for nothing to check, pass, and occupy memory.
It’s no problem to use it with a small amount of data, but I prefer projection in scenarios like list pages.
Modification 3: Look up dictionaries in batches and then spell them in memory
Some statistical methods cannot be expressed using LINQ (such as stored procedures or cross-database). In this case, use “batch query first, then memory matching”:
var orderIds = orders.Select(o => o.Id).ToList();
// 一次查完全部明细条数
var itemCounts = await _db.OrderItems
.Where(i => orderIds.Contains(i.OrderId))
.GroupBy(i => i.OrderId)
.Select(g => new { OrderId = g.Key, Count = g.Count() })
.ToDictionaryAsync(x => x.OrderId, x => x.Count);
// 一次查完全部报工时间
var lastReports = await _db.WorkReports
.Where(r => orderIds.Contains(r.OrderId))
.GroupBy(r => r.OrderId)
.Select(g => new { OrderId = g.Key, Last = g.Max(x => x.ReportedAt) })
.ToDictionaryAsync(x => x.OrderId, x => x.Last);
Notice orderIds.Contains(...) When the amount of data is large, a huge IN list will be generated. Switched to after EF Core 8 OPENJSON Passing parameters has much better performance, but it also brought about a breaking change: “There is a syntax error near the keyword WITH” on some old versions of SQL Server. I wrote about this pitfall separately in Chapter 2 of the manual. Don’t panic if you encounter it, it’s a known issue.
This time: 217 SQL → 2, 800ms → 260ms.
3. Optimization point 2: For read-only queries, be sure to add AsNoTracking
After solving N+1, 260ms is still too slow. Next look at tracking overhead.
By default, EF Core doesChange tracking: It will save a snapshot of the original value, waiting for you to adjust SaveChanges() Field by field comparison is performed to determine what UPDATE to send.
The problem is——The list page does not change the data at all, so why should we save a snapshot for it?
// 只读查询，加这一个方法
var list = await _db.Orders
.AsNoTracking()
.Where(o => o.Status == OrderStatus.InProduction)
.Select(o => new OrderListItemDto { /* ... */ })
.ToListAsync();
AsNoTracking() The effect is not obvious when the amount of data is small, but once it is a large list, report, or export, the difference is huge: the memory usage can be reduced by nearly half, because there is no need to save a snapshot for each row of entities;SaveChanges() timely DetectChanges() Scanning is also saved.
A few practical details:
- As long as it doesn’t change, add it
AsNoTracking(). This is the default habit, don’t hesitate. - Don’t add the entity you want to change, otherwise
SaveChanges()It will not be updated – this pitfall is very hidden, the code does not report an error, but the data has not changed. - If you find out that you need to change some fields, use
AsNoTrackingWithIdentityResolution()Or simply firstAsNoTracking()Check the DTO and change it againUpdate()。 - Anonymous types and DTO projections themselves are not tracked, so projection +
AsNoTracking()In fact, it is a bit repetitive, but with the addition of no loss, the semantics are clearer.
By the way, let’s talk about the pitfalls of Blazor Server.
If your project is Blazor Server,DbContext Pay special attention to the registration method. default AddDbContext Register as Scoped. The life cycle of Scoped in Blazor Server isentire user connection——In other words, a user has the page open for eight hours. This DbContext Just eight hours to live.
There are two consequences: first, the number of tracked entities is increasing, and the memory only increases but not decreases; second, multiple components use the same context concurrently and directly throw A second operation was started on this context instance before a previous operation completed。
The correct approach is to use a factory and create a short one for each operation:
builder.Services.AddDbContextFactory(opt =>
opt.UseSqlServer(conn));
// 使用处
public class OrderService(IDbContextFactory factory)
{
public async Task> GetListAsync(int page)
{
await using var db = await factory.CreateDbContextAsync();
return await db.Orders.AsNoTracking() /* ... */.ToListAsync();
}
}
This time: 260ms → 190ms, memory allocation is reduced by about 40%.
4. Optimization point 3: Select projection, don’t get the entire row of entities back
As mentioned before, I will talk about it here separately because it is the most easily overlooked and the most direct benefit.
I have more than 60 fields in the main order table: customer information, amount, tax rate, process parameters, various timestamps, notes, extended fields…Only 8 are displayed on the list page.
Without projection, EF Core generates SELECT [o].[Id], [o].[OrderNo], ...(Check all 60 columns), and then 50 rows of data are transmitted back through TDS.
Changing the method is very simple,Select into DTO:
.Select(o => new OrderListItemDto
{
Id = o.Id,
OrderNo = o.OrderNo,
CustomerName = o.Customer!.Name,
Qty = o.Qty,
Status = o.Status,
CreatedAt = o.CreatedAt
})
Three benefits stacked together:
- Database reads few columns——The IO difference between 60 columns and 8 columns on a wide table is several times;
- Transmit less data over the network——The return body of this page has been reduced from 3.2MB to about 400KB;
- Application allocates less memory– No need to construct 50 complete entities, and no need to store tracking snapshots for them.
There is a detail to pay attention to:After projection, the navigation properties can no longer be used “conveniently”.. Because what is found is already a DTO, there is no such thing as navigation attributes. Someone found out after the projection item.Customer.Phone I can’t get it, I thought it was a bug, but it’s actually the expected behavior – whatever fields are needed, just Select Write it all at once.
This is why I recommend projection: it willForce you to think clearly about “what data does this page need?”. After thinking about it clearly, SQL will naturally become slimmer.
This time: 190ms → 130ms.
5. Optimization point 4: Do not use Skip for deep paging, and do not count the total number every time.
The cost of Skip increases linearly with the page number
.Skip((page - 1) * 50).Take(50)
When you turn to page 200, the SQL is OFFSET 9950 ROWS FETCH NEXT 50 ROWS ONLY. Database mustScan the first 9950 rows before discarding themthe deeper the page number, the slower it will be. In my actual measurement, page 200 takes 2.3 seconds.
The modified method is “Keyset Pagination” – no offset is used, and the value of the last entry on the previous page is used as the starting point:
// 上一页最后一条是 id=10240，按 Id 倒序
var list = await _db.Orders
.AsNoTracking()
.Where(o => o.Id < lastId && o.Status == OrderStatus.InProduction)
.OrderByDescending(o => o.Id)
.Take(50)
.Select(o => new OrderListItemDto { /* ... */ })
.ToListAsync();
Cooperate Id index on,No matter which page you turn to, it is an index seek, and the time consumption is basically constant.. Page 200 dropped from 2.3 seconds to 90ms.
The price is that you cannot jump directly to page N, only “previous page/next page”. This is completely acceptable for an ERP list page – no one in the business really clicks on page 200, they want to “filter by conditions”.
Don’t check the total every time
The paging component usually displays “X items in total”, so each request is sent:
var total = await query.CountAsync(); // 全表或大范围 COUNT
When the amount of data reaches millions, this COUNT may be slower than the list query itself.
Three solutions:
- Total cache: Cache for 30~60 seconds when the conditions remain unchanged, and use the cached value directly when turning pages;
- Only determine “whether there is a next page”: Take one more (
Take(51)), 50 items are returned. The existence of the extra item indicates that there is a next page. The front end only needs “previous page/next page” and does not need the total number at all; - approximation: The management background displays “about 12,000 items”. It is estimated using the number of rows in the system view. It does not need to be precise.
This knife: 130ms → 95ms for the homepage, 2.3s → 90ms for the deep page.
6. Optimization point 5: Batch writing, don’t saveChanges in the loop
The above is all about reading. There is a more common and more lethal way of writing here:
// 千万别这么写
foreach (var row in rows) // rows 有 1 万条
{
_db.WorkReports.Add(row);
await _db.SaveChangesAsync(); // 一万次事务、一万次往返
}
every time SaveChanges() It is an independent transaction, a network round trip, and a full volume DetectChanges() scanning. If 10,000 items are written like this, the monthly cost will be recalculated. 42 secondsand failure in the middle will leave half of the dirty data.
Modification 1: AddRange + SaveChanges once
await _db.WorkReports.AddRangeAsync(rows);
await _db.SaveChangesAsync();
One transaction, one round trip. But it’s still a one-by-one INSERT, and AddRange When it reaches 10,000 DetectChanges() It started to slow down itself.
Modification 2: Submit in batches
const int batchSize = 1000;
foreach (var batch in rows.Chunk(batchSize))
{
await _db.WorkReports.AddRangeAsync(batch);
await _db.SaveChangesAsync();
_db.ChangeTracker.Clear(); // 关键：清掉跟踪，防止越跑越慢
}
ChangeTracker.Clear() Many people don’t know this, but it is the key to whether the batch import can run well – if it is not clear, the tracked entities will keep accumulating, and the 10th batch will be several times slower than the 1st batch.
Modification three: ExecuteUpdate/ExecuteDelete, no tracking at all
After EF Core 7, there are batch updates and deletes, which are directly translated into a SQL:
// 把某批订单状态批量改成已关闭，一条 UPDATE
await _db.Orders
.Where(o => o.Status == OrderStatus.Finished && o.CloseFlag == false)
.ExecuteUpdateAsync(s => s
.SetProperty(o => o.CloseFlag, true)
.SetProperty(o => o.ClosedAt, DateTime.Now));
// 批量删除
await _db.TempImportRows
.Where(r => r.BatchId == batchId)
.ExecuteDeleteAsync();
In the past, this kind of demand had to come first ToList() Find them all, change their attributes, and then SaveChanges()——Check it, correct it, and write it again. It’s a disaster when the amount of data is large. Now the SQL is finished.
But there are two limitations that must be known：
- Bypass change trackingso the entities already loaded in your memory will become expired data and need to be manually refreshed or rechecked;
- Not triggered
SaveChangesInterceptors and domain events. If your audit logs and soft deletions depend onSaveChangesMade of interceptor, useExecuteUpdateIt will be bypassed directly and the audit field will not be written.
The second item I have encountered in the project: after batch closing orders, I found that there was not a single record in the audit table. After checking for a long time, I realized that the interceptor was not running.
This move: Monthly cost recalculation for 10,000 rows, 42 seconds → 6.8 seconds.
7. Optimization point 6: Include multiple collections, remember AsSplitQuery
This pitfall is relatively hidden, characterized by “the amount of data is not large, but the query suddenly slows down, and it consumes a lot of memory.”
var bom = await _db.Boms
.Include(b => b.Items) // 一个 BOM 20 个物料
.Include(b => b.Operations) // 30 道工序
.Include(b => b.Materials) // 15 种材料
.FirstOrDefaultAsync(b => b.Id == id);
EF Core uses “single query” mode by default, stuffing three collections into one SQL and using JOIN to spell them out. The result will beCartesian product：
20 × 30 × 15 = 9000 行. The dozens of fields in the main BOM table were repeated 9,000 times in these 9,000 rows.
The modification is to add a line:
var bom = await _db.Boms
.Include(b => b.Items)
.Include(b => b.Operations)
.Include(b => b.Materials)
.AsSplitQuery() // 拆成 4 条查询
.FirstOrDefaultAsync(b => b.Id == id);
Split into 4 independent queries, only 1 row is transmitted from the main table, and each collection transmits its own row.BOM tree query 1.6 seconds → 340ms.
It can also be turned on globally:
optionsBuilder.UseSqlServer(conn, o => o.UseQuerySplittingBehavior(
QuerySplittingBehavior.SplitQuery));
butDon’t cut through the whole situation with one knife. Splitting will require several more round trips, and scenarios with a single collection and small data volume will be slower. My habit is:Collection of two or more Include Just add AsSplitQuery()single collection is not added.
8. The last 35ms: Don’t expect ORM to solve the indexing problem
When I reached 95ms, I had nothing to change at the EF level. The remaining bit is in the database:
The condition of that query is Status + CreatedAt Combinatorial sorting while there is only one on the table CreatedAt Single column index. Add a combined index:
CREATE INDEX IX_Orders_Status_CreatedAt
ON Orders (Status, CreatedAt DESC)
INCLUDE (OrderNo, CustomerId, Qty);
INCLUDE Bring the columns to be used on the list page and let this query becomecovering indexno need to return the form.
95ms → 60ms。
Let me make one thing clear here:The upper limit of ORM optimization is “no waste”, not “speed up”. What EF Core can do is not issue 217 SQL statements, check 60 unused columns, and save snapshots of read-only data. The real query speed ultimately depends on the index and execution plan.
So the order cannot be reversed:Fix the waste of ORM first, and then look at the index. On the contrary, if you add the index first, you will use the index to feed 217 queries that should not exist in the first place.
9. Complete bill overview
| step | action | Number of SQL items | time consuming |
|---|---|---|---|
| starting point | — | 217 | 800ms |
| Optimization point 1 | Projection/batch dictionary lookup, kill N+1 | 217 → 2 | 260ms |
| Optimization point 2 | Read-only query plus AsNoTracking | 2 | 190ms |
| Optimization point 3 | Select projection, only take 8 columns | 2 | 130ms |
| Optimization point 4 | Keyset paging + total cache | 2 | 95ms |
| Optimization point 5 | Combined index + INCLUDE coverage | 2 | 60ms |
The other two scenarios are counted separately:
| scene | Change the law | before and after |
|---|---|---|
| Monthly cost recalculation (10,000 rows written) | batch + ChangeTracker.Clear() / ExecuteUpdate |
42s → 6.8s |
| BOM tree query (3 collections Include) | AsSplitQuery() |
1.6s → 340ms |
10. How to prevent it from being changed back after half a year?
Once the optimization is done, it’s the easiest thing to rebound. I left three gates in the project:
① There is a fixed number of SQL entries in the log. At the end of each request, a “number of SQL entries + total time taken” is recorded. If it exceeds the threshold (for example, 20), an alarm will be issued. N+1 grows slowly. If someone adds an in-loop query today and another one tomorrow, no one will notice.
② The slow query log brings the call stack. Just remembering “a certain SQL is 800ms slow” is useless. You need to know which interface and which piece of code is sending it.
③ Code Review six checklists:
These six items do not solve the problem of “whether you can write fast or not”, but only solve the problem of “don’t write 217 SQL statements”.
at last
Looking back at this interface, the most ironic thing is:There is no place where “server performance is not enough”. The database CPU is 40%, the application CPU is 20%, adding machines, memory, and cache cannot solve the problem.
It is slow because every layer of the code is wasted in the way it retrieves data: it can be checked 200 times in one go, snapshots are saved that will not be changed at all, 52 unused fields are moved, and 9950 rows are scanned just to skip them.
By the way, a reminder:Don’t cover slow queries with cache. Caching can make this interface appear to be faster, but those 217 SQLs are still there. If you change the filter conditions and the cache expires, the problem will return as it is, and it will be harder to find out – because you no longer know where the slowness is.
If you also have this kind of interface with “the CPU is not busy but the interface is slow”, just go through it in the above order: first count the number of SQL entries, then split the tracking overhead, then look at the projection, then paging, and finally the index.
These steps of the investigation process, plus three other EF Core incident reviews (Contains Error reporting, delayed loading serialization crash, RowVersion concurrency coverage), I compiled it into Chapter 2 of the “.NET Production Environment Pitfall Avoidance Manual”.
If necessary, reply in the background of the official account:Avoid pitfallsself-pickup.
After getting it, it is recommended to read Chapter 2 first. Those three articles are all pitfalls that can be compared in one sentence, which is faster than reading the document.
This article is simultaneously published on the independent station coderlog.net (search the title to read the full text); reply “Avoid Pitfalls” in the backend of the public account “CSharp Selected Camp” to receive the “.NET Production Environment Pitfall Avoidance Manual”.
I will continue to update the EF Core / .NET production practical series – I will only write about the pitfalls that I have actually encountered in the production environment, not the fictitious ones.
Read the original text: Code Collection
