PHẦN 3: QUERY DATABASE VÀ TỐI ƯU CÂU LỆNH SQL - ĐIỀU PHẢI LÀM ĐỐI VỚI MỘT NHÀ CUNG CẤP API

PHẦN 2: DATA STRUCTURE, RESTFUL API, SQL, ENTITY FRAMEWORK CORE & LINQ SYNTAX

Cu Tý đang ở vị trí của một Back-end Developer được ba tháng, khoảng thời gian được xem là khá ít ỏi trên con đường trở thành một master thực thụ. Thời gian qua cậu học hỏi được khá nhiều từ những người đồng hành, dưới sự tư vấn nhiệt tình của giáo sư Xoay. Nhận thấy những thay đổi tích cực trong công việc, cậu vui lắm!

Tuy nhiên, không phải lúc nào công việc cũng suôn sẻ. Và Cu Tý đang gặp một trở ngại: Truy vấn dữ liệu. Vấn đề này lúc trước Cu Tý chưa chú ý lắm, vì dự án trước dữ liệu khá ít, và việc truy vấn khá dễ dàng. Mọi việc vẫn diễn ra thuận lợi cho tới khi Cu Tý tiếp nhận dự án mới: Nhân sự. Cu Tý phải dành cả hàng giờ để viết lại các câu truy vấn do chưa thực sự hiểu hết ý nghĩa trong quá trình viết. Và vấn đề vẫn tiếp tục tiếp diễn sau khi vấp phải những phàn nàn bên Front-end: dữ liệu trả về quá lâu !!!

Vậy vấn đề là ở đâu, và có tài liệu hướng dẫn để viết câu truy vấn cho tối ưu, mời các bạn theo dõi 3 ví dụ cơ bản sau:

1. Ví dụ 1

Cu Tý cần một danh sách người dùng để hiển thị thông tin trong một phòng ban. Cậu viết câu truy vấn SQL như sau:

SELECT *
FROM USER u
LEFT JOIN DEPARTMENT d ON u.DepartmentId = d.Id
WHERE u.DepartmentId = @paramDepartmentId

Hay tương đương câu Linq:

from user in context.User
join department in context.Department on user.DepartmentId equals department.Id
into joined from department in joined.DefaultIfEmpty()
where user.DepartmentId == paramDepartmentId
select user

Nhìn chung câu truy vấn ổn, nhưng tối ưu chưa? Câu trả lời là chưa! Thật sự mà nói, câu trả về có khá nhiều thông tin thừa. Trở lại với yêu cầu: hiển thị thông tin của các User trong một phòng ban. Câu hỏi đặt ra là các thông tin này cụ thể là thông tin gì, vì có thể trong bảng User có rất nhiều cột có dữ liệu khác nhau, và nếu như xui ta gặp phải cột có thông tin rất dài, có thể là dạng binary hoặc text (nvarchar(max) trong SQL Server) thì ... (không nói cũng biết là bị "đơm" ^^).

Câu truy vấn trên có thể viết lại như sau:

SELECT u.username, u.fullname, u.gender, d.name as departmentname
FROM USER u
INNER JOIN DEPARTMENT d ON u.DepartmentId = d.Id
WHERE u.Status = 1 AND u.DepartmentId IS NOT NULL AND u.DepartmentId = @paramDepartmentId

Hoặc câu Linq:

from user in context.User
join department in context.Department on user.DepartmentId equals department.Id
where user.Status == true and user.DepartmentId != null and user.DepartmentId == paramDepartmentId
select new {
UserName = user.UserName,
FullName = user.FullName,
Gender = user.Gender
DepartmentName = department.Name
}

Câu truy vấn trên tối ưu hơn ở các điểm sau:

• Ở mệnh đề WHERE có giới hạn thêm các kết quả của bảng User trước khi JOIN với bảng Department: các User có status = 1 và DepartmentId <> null.
• Câu SELECT chỉ lấy các giá trị cần thiết, tránh lấy các thông tin dư thừa.

Bài học rút ra: GIỚI HẠN TRONG MỆNH ĐỀ WHERE TRƯỚC KHI JOIN VÀ CHỈ LẤY CÁC TRƯỜNG THÔNG TIN CẦN THIẾT TRONG MỆNH ĐỀ SELECT

2. Ví dụ 2

Tiếp tục với vấn đề của Cu Tý, cấp trên giao cho cu cậu lấy danh sách 20 người có lương cao nhất trong một phòng ban. Cu Tý viết như sau:

SELECT u.username, d.name as departmentname, s.salary
FROM USER u
JOIN DEPARTMENT d ON u.DepartmentId = d.Id
JOIN (
SELECT TOP 20 s.UserId, s.salary
FROM SALARY s
WHERE s.UserId IN (SELECT Id FROM USER WHERE DepartmentId = @paramDepartmentId)
ORDER BY s.salary desc
) s ON u.Id = s.UserId
WHERE u.Status = 1 and u.DepartmentId is not null and u.DepartmentId = @paramDepartmentId

Hay câu Linq:

from user in context.User
join department in context.Department on user.DepartmentId equals department.Id
join (
from s in context.Salary
where (from u in context.User where u.DepartmentId = paramDepartmentId select Id).ToList().Contains(s.UserId)
)
where user.Status == true and user.DepartmentId != null and user.DepartmentId == paramDepartmentId
select new {
UserName = user.UserName,
DepartmentName = department.Name
Salary = salary.Salary
}

Câu truy vấn trên đáp ứng được yêu cầu của đề bài, tuy nhiên vẫn chưa tối ưu vì dính phải truy vấn lồng (SUB-QUERY). Câu trên sẽ được chia làm 2: Đầu tiên sẽ lấy danh sách 20 UserId có lương cao nhất của phòng ban, sau đó kết quả của câu đầu sẽ JOIN với các bảng chứa thông tin User và Department để lấy thêm thông tin cần thiết. Ta có thể viết như sau chỉ với câu lệnh JOIN:

SELECT TOP 20 u.username, d.name as departmentname, s.salary
FROM USER u
JOIN DEPARTMENT d ON u.DepartmentId = d.Id
JOIN SALARY s ON u.Id = s.UserId
WHERE u.Status = 1 and u.DepartmentId is not null and u.DepartmentId = @paramDepartmentId
ORDER BY s.Salary desc

Tương đương câu Linq:

(from user in context.User
join department in context.Department on user.DepartmentId equals department.Id
join salary in context.Salary on user.Id equals salary.UserId
where user.Status == true and user.DepartmentId != null and user.DepartmentId == paramDepartmentId
order by salary.Salary desc
select new {
UserName = user.UserName,
DepartmentName = department.Name
Salary = salary.Salary
}).Take(20).ToList()

Ta nhận được kết quả đáp ứng yêu cầu đề bài chỉ với một câu truy vấn và tốc độ sẽ nhanh hơn nhiều so với dùng SUB-QUERY.

Bài học rút ra: SỬ DỤNG JOIN THAY VÌ SUB-QUERY.

Trong một số trường hợp, câu Linq không được viết tốt EFCore sẽ translate ra dạng SUB-QUERY, khi đó là một vòng lặp các câu truy vấn rất tốn tài nguyên. Ví dụ câu Linq như sau:

from user in context.User
join department in context.Department on user.DepartmentId equals department.Id
where user.Status == true
select new {
UserName = user.userName,
CompanyName = department.Company.Name
}

Nếu không test kỹ thì khi chạy, trong trường hợp xui, câu lệnh trên sẽ được EFCore translate ra thành 2 phần:

• Đầu tiên là lấy danh sách Company:

SELECT * FROM COMPANY

• Sau đó sẽ lấy danh sách kết quả dạng vòng lặp, với mỗi @paramCompanyId là kết quả của danh sách Company trước đó.

SELECT u.username, s.salary
FROM USER u
JOIN DEPARTMENT d ON u.DepartmentId = d.Id
JOIN COMPANY c ON d.CompanyId = c.Id
WHERE u.Status = 1 and c.CompanyId = @paramCompanyId

Vì vậy việc theo dõi chuyển đổi giữa Linq và câu lệnh sql cần phải được kiểm tra kỹ lưỡng. Trên hệ quản trị CSDL có thể xem bằng tool SqlProfiler, còn trên code có thể xem thông qua MiniProfiler.

3. Ví dụ 3

Trong một số trường hợp, việc lấy ra dữ liệu và xử lý trên bộ nhớ RAM sẽ hiệu quả hơn thao tác trên cơ sở dữ liệu. Ví dụ đề bài cần lấy vềcác danh sách:

• Danh sách nhân viên thuộc phòng ban A.
• Danh sách nhân viên của tất cả các công ty có thời gian làm việc trên 5 năm.
• Danh sách nhân viên có thành tích làm việc cao nhất trong năm hiện tại.

Nếu xử lý thông thường trong CSDL, ta sẽ phải viết 3 câu lệnh để truy vấn. Điều này có thể thực sự không cần thiết, vì ta có thể gọi 1 câu duy nhất lấy về tất cả thông tin sau đó xử lý trên Back-end và trả về kết quả cho Front-end.

Trong trường hợp lượng người dùng lớn, ít thay đổi và truy vấn dữ liệu nhiều, nên cân nhắc sử dụng Cache (bộ nhớ đệm) để lấy kết quả truy vấn thay vì phải thực thi câu lệnh. Thời gian truy vấn trả về sẽ làm bạn ngạc nhiên đấy!!!

Bài học rút ra: LINH HOẠT TRONG VIỆC XỬ LÝ DỮ LIỆU, SỬ DỤNG CACHE HOẶC XỬ LÝ TRÊN RAM ĐÔI LÚC SẼ CHO HIỆU NĂNG TỐT HƠN.

Qua 3 ví dụ cơ bản, Cu Tý thấy hứng khởi lên hẳn. Còn khá nhiều điều phải học phía trước...

Còn nữa...