Một Vài Kiểu Viết Join

Vũ Huy Tâm

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'



Tags: , ,

12 Comments
Posted on 15/3/2011 | Categories: Performance tuning, SQL Server Programming

Các bài viết tương tự

Comments
  • Nguyen Hai (18/04/2011 8:50 am)

    Chào bạn! bạn ơi cho mình hỏi chút

    CREATE TABLE test(id int, txt nvarchar(10))

    INSERT INTO test(id,txt) VALUES (1,'a')

    INSERT INTO test(id,txt) VALUES (2,'b')

    INSERT INTO test(id,txt) VALUES (3,'b')

    INSERT INTO test(id,txt) VALUES (2,'c')

    INSERT INTO test(id,txt) VALUES (1,'a')

    …….

    Mình muốn lấy 1 row có id = 3 trong bảng test

    Select * from test where id = 3 <– SQL sẽ dọc toàn bộ table từ row đầu tiên đến row cuối cùng rồi mới trả kết quả

    Như bạn thấy đấy sql đã tìm thấy row có id = 3 rồi mà nó vẫn tìm tiếp

    - Mình muốn hỏi bạn là phải viết code sao để sql nó tìm thấy row có id = 3 cái là nó trả về kết quả luôn không tìm nữa

    - Select top 1 * from test where id = 3 (Mình muốn hỏi là sql tìm thấy row có id = 3 nó sẽ ngừng tìm kiếm luôn hay là nó vẫn tìm cho khi đến row cuối cùng rồi nó mới lấy 1 row rồi trả về kết quả)

  • Nguyen Hai (18/04/2011 9:30 am)

    lang thang một vong quanh google mình đã tìm được hết quả (Tự hỏi tự trả lời vậy :D )

    Select top 1 * from test where id = 3 (Tìm thấy id=3 nhừng tìm kiếm và hiện thị luôn) một câu trả lời cho 2 câu hỏi của mình luôn :D

  • Vũ Huy Tâm (19/04/2011 2:23 pm)

    @Hai: đúng rồi, nhưng quan trọng nhất vẫn là phải có index trên trường id

  • kl (19/04/2011 6:08 pm)

    thanks

  • bimateva (20/04/2011 1:21 am)

    ngoài lề hỏi bạn 1 chút:

    do quá trình update trục trặc nên khi insert thông tin nhân viên vào bảng chấm công (X) không kèm theo mã phòng (default: DeptID = 0), giờ mình muốn cập nhật mã phòng cho số bản ghi lỗi trong bảng X này theo như mã phòng ghi nhận được từ bảng nhân viên. Bạn có thể chỉ giúp mình cách giải quyết được không? Thanks

  • Vũ Huy Tâm (21/04/2011 10:01 am)

    @bimateva: có vẻ như bạn muốn update bảng chấm công từ bảng nhân viên? Vì không có thông tin chi tiết nên tôi chỉ có thể phác ra một chút:

    bước 1: xác định số bản ghi vừa được insert, ví dụ dựa vào trường NgayCapNhat hoặc nếu DeptID=0 thì biết ngay là do vừa được insert bởi quá trình update lỗi. Giả sử có thệ xác định bằng cách DeptID=0

    bước 2: viết câu lệnh đại loại như sau:

    UPDATE CC

    SET DeptID = NV.DeptID

    FROM ChamCong CC

    JOIN NhanVien NV ON CC.NhanVienID = NV.NhanVienID

    WHERE CC.DeptID=0

  • Trịnh Đức To&ag (21/04/2011 10:12 am)

    Anh Vũ Huy Tâm ơi!

    em đang học T-SQl 2005 (học aptech-bachkhoa)

    học được các phần cơ bản rồi, sang cả index, view, stored proceduces, trigger

    nhưng mà loạn quá không biết diễn giải thế nào về những kiến thức đã học

    em thấy blog của anh rất hay nhưng không biết phải bắt đầu từ đâu để học và soát lại kiến thức.

    không biết anh có thể cho em yahoo, và hướng dẫn em được không?

    có gì em xin hậu tạ

  • Trịnh Đức To&ag (21/04/2011 10:15 am)

    àh còn cái này nữa. em thường tổng hợp kiến thức theo dạng .doc

    anh xem thế nào dùng em chỗ nào cần lưu ý học anh đánh dấu giúp em

    còn thiếu thì chỉ chỗ cho em học với
    http://www.mediafire.com/?voop5ehd45a3wc1

  • Vũ Huy Tâm (21/04/2011 10:29 am)

    @Toàn: Cám ơn em đã ghé thăm. Quả thật anh rất bận, thời gian gần đây còn không có lúc nào ngồi viết blog được nữa nên chắc không giúp em được việc đó, sorry nhé. Nếu có câu hỏi cụ thể nào thì hãy gửi cho anh. Chúc học tôt nhé :)

    • Trần Quyết (09/11/2011 2:36 pm)

      Ai giúp mình cái vụ join 3 bảng nhé… cho mình xin ví dụ cụ thể… cảm ơn nhìu

  • Trần Quyết (09/11/2011 2:35 pm)

    Ai giúp mình cái vụ join 3 bảng nhé… cho mình xin ví dụ cụ thể… cảm ơn nhìu

  • da (20/06/2012 5:34 am)

    da

Leave a Reply

Hướng dẫn: Để nhập mã T-SQL bạn dùng thẻ <pre lang="tsql"> và </pre>.
Ví dụ: <pre lang="tsql">SELECT * FROM MyTable</pre>