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

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'
Bạn thấy bài viết này như thế nào?: 
No votes yet
Ảnh của Tommy Tran

Tommy Tran owner Express Magazine

Drupal Developer having 9+ year experience, implementation and having strong knowledge of technical specifications, workflow development. Ability to perform effectively and efficiently in team and individually. Always enthusiastic and interseted to study new technologies

  • Skype ID: tthanhthuy
  • Phone/Zalo: (+84) 944 225 212
  • WhatsApp: (+84) 944 225 212
  • Line Messenger: (+84) 944 225 212
  • Email: asaleotestf@gmail.com
  • Telegram Messenger: https:/t.me/tommytran0401

Quảng cáo việc làm

 

Thích hợp các bạn nữ mảng thợ may làm việc tại nước NGA

Đơn hàng Tuyển dụng 100 Thợ may đi Nga(đợt 1 tháng 3.2021, đợt 2 tháng 5.2021). Lương thực lãnh 800 USD, bao ăn ở, vé máy bay và visa, phí xuất cảnh(1800 USD)trả khi đi làm có lương. Bạn có thể liên hệ CÔNG TY qua Phone/Zalo: (+84) 944 225 212. Công ty sẽ tư vấn cho bạn.

Xem chi tiết: >>> https://bit.ly/3o9NOfR

Advertisement

 

jobsora

Dich vu khu trung tphcm

Dich vu diet chuot tphcm

Dich vu diet con trung

Quảng Cáo Bài Viết

 
HTC One vẫn chỉ dừng lại ở rò rỉ thông tin giá bán

HTC One vẫn chỉ dừng lại ở rò rỉ thông tin giá bán

Thông tin về "siêu phẩm" điện thoại sắp ra mắt của HTC tiếp tục xuất hiện, lần này là hình ảnh về phiên bản màu đen và giá bán thông qua 4 nhà mạng lớn nhất thị trường Mỹ.

20 mẹo cho Google Docs

20 mẹo cho Google Docs

Gần đây bạn có dùng đến Google Docs không? Những nhà phát triển ứng dụng này đã tiến những bước rất chậm nhưng rất chắc để đưa Google Docs trở nên...

Giới thiệu 2 base Themes in Drupal 8 core

Giới thiệu 2 base Themes in Drupal 8 core

My skin crawls when I see HTML filled with divs tucked inside each other over and over again like a Russian nesting doll