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: 9 (2 votes)
Ảnh của Tommy Tran

Tommy 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

Bình luận (0)

 

Add Comment

Filtered HTML

  • Các địa chỉ web và email sẽ tự động được chuyển sang dạng liên kết.
  • Các thẻ HTML được chấp nhận: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Tự động ngắt dòng và đoạn văn.

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.

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

 
Hướng dẫn tạo 1 Drupal 8 block năm 2015

Hướng dẫn tạo 1 Drupal 8 block năm 2015

We will generate a block that will allow us to display a welcome text and the name of the user logged in. Execute the following command

Microsoft Launches Xbox App for iPhone

Microsoft Launches Xbox App for iPhone

Microsoft has launched a new Xbox LIVE app for iOS devices including the iPhone and iPad. The app, called My Xbox LIVE, allows users to edit their Xbox profile as well as their friends list, and compare achievements with other users.

Hứơng dẫn tạo theme riêng cho Google Chrome

Hứơng dẫn tạo theme riêng cho Google Chrome

Hôm nay xeko xin giới thiệu một plugin miễn phí từ google có tên là Chrome Theme, bạn có thể tạo nên một Chrome hoàn hảo hơn cho chính bản thân mình.

Công ty diệt chuột T&C

 

Diet con trung