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'

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

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'

, đồ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

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'
Tags: 
Bạn thấy bài viết này như thế nào?: 
Average: 10 (1 vote)
Ả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: [email protected]
  • Telegram Messenger: https:/t.me/tommytran0401

Bình luận (0)

 

Add Comment

Plain text

  • No HTML tags allowed.
  • Các địa chỉ web và email sẽ tự động được chuyển sang dạng liên kết.
  • Tự động ngắt dòng và đoạn văn.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
5 + 0 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.

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

 
Drupal SEO - Vấn đề vẫn là trung lặp nội dung (duplicate content)

Vấn đề trùng lặp nội dung trong Drupal

Nói đến Drupal, một số có thể cho rằng nó quá phức tạp để phát triển website vì lý do những thuật ngữ được sử dụng trong drupal khác rất nhiều so với joomla và wordpress.

Thị trường bất động sản 2019 có thể xảy ra tình trạng tăng giá đất nền tại một số khu vực

Thị trường bất động sản 2019 có thể xảy ra tình trạng tăng giá đất nền tại một số khu vực

Dòng vốn ngoại tiếp tục đổ bộ, nhu cầu nhà ở của người dân còn lớn… là những yếu tố sẽ tạo nên gam màu sáng cho thị trường bất động sản năm 2019. Tuy nhiên, thị trường vẫn còn nhiều thách thức

HTML Injection – The Cross-Site Scripting (XSS)

HTML Injection – The Cross-Site Scripting (XSS)

XSS – Có lẽ mọi người đã biết. Mình chỉ xin tổng hợp lại một số kiến thức cơ bản dưới đây .

BLOG POSTS

 

Wordpress Freelancer

 

Wordpress Freelancer