Phase 5: Database & SQL Mastery - Answers | Knowledge Base

[Q5.01] Optimize truy vấn EF Core

Tiếp cận (Step-by-step):

Bật Logging của EF Core để in ra console câu Raw SQL.
Lấy câu Raw SQL vào SSMS/Azure Data Studio, bật tính năng Include Actual Execution Plan.
Phân tích Plan: Tìm kiếm vòng xoáy Index Scan hoặc Key Lookup lớn. Kiểm tra cảnh báo sát thủ (Implicit conversion, thiếu Index).
Fix EF Core:
- Kiểm tra xem có bị N+1 Query do quên .Include() không.
- Nếu Data trả về quá nhiều, hãy dùng .Select() để Projection thành DTO (bỏ đi các cột béo dư thừa).
- Dùng .AsNoTracking() nếu chỉ lấy dữ liệu hiển thị.
- Nếu SQL sinh ra quá lằng nhằng (Cartesian explosion), gắn thêm .AsSplitQuery().
- Bổ sung Index cần thiết ở phía DB bằng Migration ([Index]).

Trả lời: Hiện tượng này là do bẫy Parameter Sniffing.

Giải thích: Lần đầu chạy SP, SQL Server sinh ra một Execution Plan dựa trên tham số đầu vào. Nếu tham số đó trải trên dữ liệu cực kỳ thưa thớt (trả về 1 dòng), nó tạo ra Plan A siêu tốc. Nhưng khi tham số khác truyền vào cần quét hàng triệu dòng, SQL Server vẫn lôi Plan A ra xài (vì bị rập khuôn), dẫn tới thảm hoạ timeout.
Cách Fix:
1. Gắn cờ OPTION (RECOMPILE) ở query để nó lập lại plan mỗi lần chạy (Chi phí compile cao xíu nhưng plan luôn đúng).
2. Dùng biến Local (Local variables) để đánh lừa Optimizer.
3. Trong SQL 2022+ hoặc Azure SQL, bật Parameter Sensitive Plan Optimization để hệ thống tự xử lý bằng cách lưu nhiều plan cho 1 query.

Trả lời:

Clustered Index: Quyết định thứ tự xắp xếp vật lý của các dòng dữ liệu trên đĩa đĩa. Một bảng chỉ có tối đa 1 Clustered Index.
Non-Clustered Index: Giống như phần Mục Lục ở cuối sách. Nó sắp xếp logical, và trỏ về Clustered Index Key (hoặc con trỏ vật lý).
Tại sao KHÔNG nên dùng GUID ngẫu nhiên (NEWID()) làm PK/Clustered Index? Vì dữ liệu mới chèn vào sẽ bị vứt ngẫu nhiên giữa các Block/Page (vì GUID sinh ngẫu nhiên). Gây ra hiện tượng Page Split cực kì nặng nề, làm ổ cứng bị phân mảnh (Fragmentation) và tốn tài nguyên sắp xếp lại. (Nếu dùng GUID, phải thiết lập sinh ra GUID tuần tự kiểu sơ bộ NEWSEQUENTIALID()).

Trả lời:

READ COMMITTED (Mặc định): Người viết (Writer) đặt Lock lên Data. Người đọc (Reader) phải chờ Writer thả Lock ra mới được lấy. Dẫn đến ứng dụng chậm, bị block liên tục dưới tải lớn.
RCSI (Snapshot): Khi bật RCSI, mỗi khi Writer muốn đổi data, SQL lưu phiên bản cũ vào TempDB. Khi Reader muốn đọc, nó sẽ được trả về phiên bản cũ đó ngay lập tức (Snapshot). Writer không chặn Reader và ngược lại. Hiệu năng read/write song song tăng đột biến, bù lại tốn vùng lưu trữ ở TempDB.

Trả lời: Cho trường hợp này, kiến trúc chuẩn là Dapper + Stored Procedure/View.

Lý do bỏ qua EF Core: EF Core xuất sắc trong quản lý Domain State (Update/Delete/Insert theo rule của Entity). Tuy nhiên, với lệnh Report cần JOIN 10 bảng phức tạp, hàm Window (ROW_NUMBER()), EF Core thường biến query thành một mớ hỗn độn (SQL rác), khó tune. Đặc biệt việc load dữ liệu khổng lồ vào Object graph của EF tốn rất nhiều RAM.
Lý do dùng Dapper: Cho phép viết Raw SQL tận dụng 100% cú pháp nâng cao của SQL, map kết quả thành DTO tuỳ biến nhẹ nhàng. Plan được tối ưu hoàn toàn ở mức Database.