Một vài kiểu viết Join

Bài viết này nhằm phản hồi lại comment của bạn NANIA trong bài Các Loại JOIN Trong SQL Server. Tôi đã có thể viết lại một comment, nhưng vì muốn kèm theo một vài hình, đồng thời nhận thấy đây là một pattern khá phổ biến khi lập trình T-SQL nên mới viết thành một bài riêng

Hy vọng có thể giúp một số bạn hiểu thêm về cơ chế hoạt động của bộ Optimizer.
Comment của bạn NANIA nói rằng, trong hai kiểu viết sau:

--kiểu 1
WITH ProFilter AS (SELECT ProId, ProName FROM Products p WHERE p.DelFlag=0 AND Active=1 AND ShopID = 'taithien')
SELECT p.*, pt.*
FROM ProFilter p
INNER JOIN ProductType pt
WHERE p.ProTypeID = pt.ProTypeID
 
--kiểu 2
SELECT *
FROM Products p
INNER JOIN ProductType pt
WHERE p.ProTypeID = pt.ProTypeID AND p.DelFlag=0 AND Active=1 AND ShopID = 'taithien'

thì “kiểu viết 1 tối ưu hơn. Cụ thể là dữ liệu đã được lọc bớt dư thừa trong bảng product=> profilter để giảm không gian tìm kiếm cho câu lệnh join đằng sau. Còn trong đoạn lệnh thứ 2, bạn select toàn bộ dữ liệu ra và join trước khi loc, như vậy sẽ rất tốn thời gian của hệ thống, đồng thời nó phải làm việc với 1 lượng dữ liệu rất lớn.”.

Ý của bạn NANIA rất có lý, tuy nhiên tôi cho rằng không lập luận nào thuyết phục bằng việc quan sát phương án thực thi của câu lệnh, vì đó là cách mà SQL Server thực sự tiến hành xử lý câu lệnh, chứ không phải cách mà ta hình dung nó sẽ thực hiện.

Tôi dùng database AdventureWorks và viết một câu lệnh theo hai cách tương tự như trong ví dụ trên:

--kiểu 1
WITH ProFilter AS(
SELECT *
FROM Production.Product
WHERE MakeFlag = 1 AND SafetyStockLevel = 500 AND CLASS = 'H' AND Style = 'U')
 
SELECT p.*, pm.Name AS Model
FROM ProFilter p
JOIN Production.ProductModel pm ON p.ProductModelID = pm.ProductModelID
 
--kiểu 2
SELECT p.*, pm.Name AS Model
FROM Production.Product p
JOIN Production.ProductModel pm ON p.ProductModelID = pm.ProductModelID
AND MakeFlag = 1 AND SafetyStockLevel = 500 AND CLASS = 'H' AND Style = 'U'

Bạn có thể nhận thấy là hai câu lệnh dùng phương án thực thi giống hệt nhau – Bắt đầu bằng Clustered Index Seek trên bảng ProductModel và Clustered Index Scan trên bảng Product và sau đó là Nested Loop Join. Và vì phương án thực thi như nhau nên chi phí cũng bằng nhau. Như vậy với kiểu viết 1, bộ Optimizer không thực hiện Common Table Expression để lọc trước rồi mới join kết quả với câu lệnh chính, mà gom chung vào với nhau và xử lý như một lệnh join đơn thuần. Vì sao như vậy? Câu trả lời đơn giản nhất là, vì làm như vậy hiệu năng được tối ưu hơn. Để so sánh, ta hãy thử viết lại kiểu 1 theo cách để “ép” SQL Server thực hiện filter trước rồi mới join kết quả với lệnh chính. Ở ví dụ dưới đây, tôi dùng bảng tạm (temp table) thay cho Common Table Expression:

--kiểu 1'
SELECT *
INTO #ProFilter
FROM Production.Product
WHERE MakeFlag = 1 AND SafetyStockLevel = 500 AND CLASS = 'H' AND Style = 'U'
 
SELECT p.*, pm.Name AS Model
FROM #ProFilter p
JOIN Production.ProductModel pm ON p.ProductModelID = pm.ProductModelID
 
--kiểu 2
SELECT p.*, pm.Name AS Model
FROM Production.Product p
JOIN Production.ProductModel pm ON p.ProductModelID = pm.ProductModelID
AND MakeFlag = 1 AND SafetyStockLevel = 500 AND CLASS = 'H' AND Style = 'U'

Và bây giờ chi phí của việc filter trước rồi mới join đã trở nên khá lớn so với join trực tiếp. Câu lệnh viết theo kiểu 2 (query số 3) chỉ chiếm chi phí 40% trong cả đoạn lệnh, nói cách khác là hai lệnh kia có tổng chi phí cao gấp rưỡi lệnh join. Trở lại với câu hỏi tại sao ở trên, bộ Optimizer trong quá trình phân tích câu lệnh có bước xây dựng một đồ thị gọi là sequence tree (tạm dịch là cây diễn dịch) – cây này sau đó được dùng làm đầu vào cho việc tạo lập phương án thực thi. Ở bước này nó có quyền “viết lại” theo cách không thay đổi ý nghĩa của câu lệnh nhưng làm tối ưu hóa việc thực hiện. Vì vậy cây diễn dịch không nhất thiết tương ứng 1-1 với câu lệnh ban đầu. (Lưu ý là mặc dù bộ Optimizer vẫn tiếp tục thông minh hơn qua mỗi phiên bản mới, nhưng trong nhiều trường hợp nó vẫn “chết cứng” với cách viết của câu lệnh và chọn một phương án thực thi dở tệ. Vì thế viết câu lệnh như thế nào để đạt được hiệu năng cao vẫn rất quan trọng).

Ở ví dụ trên câu lệnh được viết lại như vậy vì khi đó nó có thể được áp dụng tối ưu hóa trên một lệnh join, điều mà SQL Server thường xử lý rất tốt. Do đó chi phí thường thấp hơn so với khi bị “ép buộc” thực hiện theo cách khác. Như ở ví dụ dùng temp table, câu lệnh đã bị tách làm hai lệnh (insert vào temp table, và join với nó), bộ Optimizer buộc phải thực hiện theo trình tự như vậy vì nó không có khả năng tối ưu hóa cho một đoạn gồm nhiều lệnh.

Ngoài cách viết câu lệnh join như trên bạn còn có thế viết theo hai cách khác, dùng subquery hoặc đưa filter vào mệnh đề WHERE, tất cả đều có hiệu năng tương đương nhau. Ví dụ, ba câu lệnh dưới đây cho cùng một phương án thực thi:

--kiểu 2
SELECT p.*, pm.Name AS Model
FROM Production.Product p
JOIN Production.ProductModel pm ON p.ProductModelID = pm.ProductModelID
AND MakeFlag = 1 AND SafetyStockLevel = 500 AND CLASS = 'H' AND Style = 'U'
 
--kiểu 3
SELECT p.*, pm.Name AS Model
FROM (SELECT * FROM Production.Product WHERE MakeFlag = 1 AND SafetyStockLevel = 500 AND CLASS = 'H' AND Style = 'U') p
JOIN Production.ProductModel pm ON p.ProductModelID = pm.ProductModelID
 
--kiểu 4
SELECT p.*, pm.Name AS Model
FROM Production.Product p
JOIN Production.ProductModel pm ON p.ProductModelID = pm.ProductModelID
WHERE MakeFlag = 1 AND SafetyStockLevel = 500 AND CLASS = 'H' AND Style = 'U'