6 posts tagged with "Performance"

Measure before optimising — memory, latency, build time and numbers you can actually verify.

Thêm 4 index làm INSERT chậm 6 lần: cái giá không ai nhắc khi bảo bạn đánh index

September 29, 2026 · 13 min read

Middle Fullstack Developer @ Utop.vn

Summary

Index tăng tốc đọc bằng cách trả giá ở mỗi lần ghi, và cái giá đó lớn hơn hầu hết người ta hình dung. Đo trên PostgreSQL 16 với cùng 200.000 dòng: bảng không index chèn xong trong 287,8 ms và chiếm 10,2 MB; bảng có bốn index mất 1.722,3 ms và chiếm 27 MB. Tức là chậm gấp 6 lần và tốn gấp 2,6 lần dung lượng — chỉ để thêm bốn cấu trúc mà có thể chẳng câu truy vấn nào dùng tới. Câu hỏi đúng không phải "cột này có nên có index không" mà là "phần đọc tiết kiệm được có bù nổi phần ghi phải trả không".

Mọi bài viết về tối ưu database đều kết thúc bằng lời khuyên thêm index. Rất ít bài nói về hoá đơn đi kèm, và càng ít bài đưa con số.

Bài này đào sâu bài Thiết kế database và bài Index trong series học SQL 30 ngày. Mọi số liệu đo thật trên PostgreSQL 16.11.

Index Exists but the Query Still Scans the Whole Table? Sargability and One Function Call

September 26, 2026 · 12 min read

Nguyễn Huỳnh Minh Tiến

Middle Fullstack Developer @ Utop.vn

Summary

An index is a structure sorted by the column's values. Wrap a function around the column — date_part('year', created_at), LOWER(email), CAST(...) — and the thing you are comparing is no longer the sorted value, so the database cannot use the index and falls back to scanning the whole table. A condition that can use an index is called sargable. Measured on PostgreSQL 16 with 200,000 rows: the function-wrapped version runs in 29.6 ms with a Seq Scan, the range-rewritten version runs in 5.4 ms with a Bitmap Index Scan — about 5.4× faster, returning the same 37,518 rows.

A familiar situation: the query is slow, you add an index on exactly the column being filtered, you run it again — still just as slow. The index is right there, \d shows it, but the execution plan never mentions it.

This post drills into the Index lesson and the query optimization lesson from my 30-day SQL series. Every number is real output from PostgreSQL 16.11.

When Does Calling .Result Deadlock, and When Does It Not?

September 21, 2026 · 13 min read

Nguyễn Huỳnh Minh Tiến

Middle Fullstack Developer @ Utop.vn

Summary

.Result and .Wait() block the current thread. When await suspends, it captures the current SynchronizationContext so that the code after it runs back on that same context. If the context only allows one thread at a time — a WPF UI thread, an ASP.NET Framework request context — and that thread is the one .Result is blocking, the continuation can never get in: deadlock. Console apps and ASP.NET Core have no SynchronizationContext, so the continuation runs on the thread pool and nothing gets stuck. The real fix is async all the way.

The code below runs perfectly in a console app, and freezes solid the moment you paste it verbatim into an ASP.NET Framework controller or a button handler in WPF:

public string GetCustomerName(int id)
{
    return GetCustomerAsync(id).Result;   // hangs here
}

private async Task<string> GetCustomerAsync(int id)
{
    var response = await _http.GetStringAsync($"/customers/{id}");
    return Parse(response).Name;
}

No exception, no stack trace, no timeout. The thread simply stops forever. This is not HttpClient's fault, and async is not "broken" — it is the direct consequence of a design decision in how await works.

Why Does EF Core Fire 201 Queries for One List Screen? Fixing N+1

September 21, 2026 · 12 min read

Nguyễn Huỳnh Minh Tiến

Middle Fullstack Developer @ Utop.vn

Summary

A 200-row list whose log shows 201 SQL statements is textbook N+1: one query fetches the list, then each row triggers another query to load its related data. The root cause is navigation properties being loaded one at a time instead of together. The default fix is a projection with Select into a DTO. Include is only the right tool when you need real entities, and once you Include two or more collections you need AsSplitQuery to avoid a cartesian explosion.

You open the customer list page, the API responds after four seconds, yet the database CPU barely moves and every individual SQL statement runs in a few milliseconds. Turn on logging and it turns out the problem is not one slow query, but 201 fast ones queued up nose to tail.

That is an N+1 query. It does not make the code wrong, it throws no exception, no unit test catches it, and on your local machine with twenty seeded rows it may even be faster than the correct fix. It only shows itself once the tables hold real data and the network latency to the database is non-zero.

Why Does .NET Eat 96% of Container RAM While Idle? Server GC and How to Cut Memory by 65%

September 12, 2026 · 11 min read

Nguyễn Huỳnh Minh Tiến

Middle Fullstack Developer @ Utop.vn

Summary

An ASP.NET Core 9 container sat at 987 MiB against a 1 GiB ceiling (96%) after two hours without receiving a single request. This was not a memory leak: the number was absolutely flat across repeated measurements, which is the signature of retained heap rather than a leak. The cause was Server GC — the ASP.NET Core default, which creates one heap per CPU core and releases very little memory back to the operating system. Setting DOTNET_gcServer=0 and DOTNET_GCConserveMemory=5 brought anonymous memory down from 822 MB to 280 MB (−65%), measured as stable after 30 minutes. The trade-off: Workstation GC gives lower throughput under heavy load on many-core machines.

This post records a real diagnostic session on a 4-core / 8 GB VPS running nine WordPress sites alongside a .NET API. Every number below is measured, not illustrative.