Vấn đề để SQL Performance tốt nhất
Tôi đang viết mã T-SQL cho SQL Server và không biết nên sử dụng view, biểu thức chung (CTE), câu truy vấn con truyền thống, bảng tạm hay biến bảng để sql performance là tốt nhất. Làm cách nào để kiểm tra các tùy chọn này để xác định mã có hiệu suất tốt nhất?
Giải pháp cho SQL Performance
Tips trong bài viết này sẽ chỉ ra cách đo lường sự khác biệt về hiệu suất của các cấu trúc TSQL khác nhau để giúp tìm ra lựa chọn tốt nhất cho một câu truy vấn cụ thể. Phương pháp tinh chỉnh hiệu suất được trình bày cũng có thể áp dụng trong nhiều tình huống tinh chỉnh hiệu suất khác! Cuối cùng, mẹo này sẽ cung cấp một số ý kiến về cách lựa chọn giải pháp câu truy vấn con phù hợp cho từng tình huống.
Tất cả các ví dụ trong mẹo này sẽ sử dụng cơ sở dữ liệu mẫu WideWorldImporters, có thể tải về từ đây và sẽ chạy trên SQL Server 2019. Các hình ảnh có thể khác nhau, nhưng phương pháp này vẫn hoạt động trên các phiên bản cũ hơn của SQL Server.
Ví dụ về câu lệnh SQL
Tôi giả sử 1 Dev T-SQL đã được giao nhiệm vụ tìm ngày hóa đơn và số đơn đặt hàng cho giao dịch mua hàng lớn nhất của mỗi khách hàng trong quý đầu tiên năm 2014.
Để thực hiện công việc này, có 2 bước. Bước đầu tiên là xác định giao dịch mua hàng lớn nhất của mỗi khách hàng trong quý. Bạn có thể sử dụng truy vấn sau để thực hiện bước này.
SELECT BillToCustomerID, MAX(ExtendedPrice) Amt FROM Sales.Invoices i INNER JOIN Sales.InvoiceLines il ON i.InvoiceID = il.InvoiceID WHERE InvoiceDate BETWEEN '1/1/2014' AND '3/31/2014' GROUP BY BillToCustomerID;
Việc này không đơn giản như việc chọn ngày hóa đơn MAX() hoặc số đơn đặt hàng lớn nhất, vì giá trị kết quả có thể không thuộc cùng một hóa đơn như giá trị MAX(ExtendedPrice). Vì vậy, không thể thêm hai cột này vào mệnh đề GROUP BY vì có thể có nhiều hơn một dòng dữ liệu cho mỗi khách hàng. Để giải quyết vấn đề này, chúng ta có thể sử dụng một câu truy vấn con.
SELECT C.CustomerName, i.InvoiceDate, i.CustomerPurchaseOrderNumber FROM Sales.Invoices i INNER JOIN Sales.InvoiceLines il ON i.InvoiceID = il.InvoiceID INNER JOIN Sales.Customers c ON i.CustomerID = c.CustomerID INNER JOIN THAT_QUERY_FROM_ABOVE t ON t.BillToCustomerID = i.BillToCustomerID AND t.Amt = il.ExtendedPrice
Sau khi tính toán đã được thực hiện, hai cột kết quả phải được kết hợp lại với bảng hóa đơn để xác định ngày hóa đơn và số đơn đặt hàng của khách hàng cho hóa đơn chứa giao dịch mua hàng lớn nhất.
So sánh tùy chọn CTE với câu truy vấn con truyền thống
Dưới đây là 2 phiên bản của các truy vấn. Chúng sẽ được thực thi với cả STATISTICS IO và Include Actual Execution Plans được bật.
--CTE Version
WITH TopPurchase AS(
SELECT BillToCustomerID, MAX(ExtendedPrice) Amt
FROM Sales.Invoices i
INNER JOIN Sales.InvoiceLines il ON i.InvoiceID = il.InvoiceID
WHERE
InvoiceDate BETWEEN '1/1/2014' AND '3/31/2014'
GROUP BY
BillToCustomerID)
SELECT C.CustomerName, i.InvoiceDate, i.CustomerPurchaseOrderNumber
FROM Sales.Invoices i
INNER JOIN Sales.InvoiceLines il ON i.InvoiceID = il.InvoiceID
INNER JOIN Sales.Customers c ON i.CustomerID = c.CustomerID
INNER JOIN TopPurchase t ON t.BillToCustomerID = i.BillToCustomerID AND t.Amt = il.ExtendedPrice;
--Subquery Version
SELECT C.CustomerName, i.InvoiceDate, i.CustomerPurchaseOrderNumber
FROM Sales.Invoices i
INNER JOIN Sales.InvoiceLines il ON i.InvoiceID = il.InvoiceID
INNER JOIN Sales.Customers c ON i.CustomerID = c.CustomerID
INNER JOIN(
SELECT BillToCustomerID, MAX(ExtendedPrice) Amt
FROM Sales.Invoices i
INNER JOIN Sales.InvoiceLines il ON i.InvoiceID = il.InvoiceID
WHERE
InvoiceDate BETWEEN '1/1/2014' AND '3/31/2014'
GROUP BY
BillToCustomerID)
t ON t.BillToCustomerID = i.BillToCustomerID AND t.Amt = il.ExtendedPrice;
Kết quả đầu tiên cần được phân tích là các kế hoạch thực thi, trong đó có 2 truy vấn được hiển thị. Mỗi truy vấn được ước tính chiếm 50% (được tô màu vàng) công việc cho toàn bộ lô công việc. Các phần trăm này dựa trên ước tính và không luôn chính xác. Màn hình không đủ chỗ để hiển thị toàn bộ kế hoạch truy vấn, nhưng đoạn ảnh chụp màn hình này cho thấy một số toán tử khớp giữa 2 kế hoạch. Một sự kiểm tra kỹ lưỡng hơn sẽ xác nhận chúng khớp hoàn toàn.
Điều này cho thấy không có sự khác biệt về hiệu suất giữa hai phiên bản của truy vấn.
Chuyển sang tab “Messages”. Tab này hiển thị kết quả của STATISTICS IO và số lượng hàng của các truy vấn. Kết quả này cho thấy cả hai truy vấn đều trả về 521 hàng (màu đỏ). Văn bản nói “1 row affected line” là cho kế hoạch thực thi.
Các dòng khác trong màu vàng, xanh lá cây và tím cho thấy cả hai phiên bản truy vấn đã truy vấn các bảng giống nhau với cùng một lượng dữ liệu. Thông tin trên tab này xác nhận rằng không có sự khác biệt về hiệu suất giữa các truy vấn này và có nghĩa là ước tính 50% từ tab kế hoạch thực thi là chính xác.
Sử dụng view thay vì CTE hoặc câu truy vấn con truyền thống
Phiên bản tiếp theo của truy vấn sẽ được tạo bằng cách sử dụng view để xây dựng truy vấn đầu tiên. Đây là định nghĩa của view.
CREATE VIEW vTopPurchase AS SELECT BillToCustomerID, MAX(ExtendedPrice) Amt FROM Sales.Invoices i INNER JOIN Sales.InvoiceLines il ON i.InvoiceID = il.InvoiceID WHERE InvoiceDate BETWEEN '1/1/2014' AND '3/31/2014' GROUP BY BillToCustomerID;
Câu query mới sẽ như bên dưới:
SELECT C.CustomerName, i.InvoiceDate, i.CustomerPurchaseOrderNumber FROM Sales.Invoices i INNER JOIN Sales.InvoiceLines il ON i.InvoiceID = il.InvoiceID INNER JOIN Sales.Customers c ON i.CustomerID = c.CustomerID INNER JOIN vTopPurchase t ON t.BillToCustomerID = i.BillToCustomerID AND t.Amt = il.ExtendedPrice;
Thực thi truy vấn này trả về cùng số lượng hàng và cùng các thống kê IO giống như hai phiên bản truy vấn đầu tiên. Một kiểm tra kế hoạch truy vấn cho thấy lại có cùng một kế hoạch hoàn toàn giống nhau!
Điều đó có nghĩa là gì?
SQL Server engine tối ưu hóa mọi truy vấn được gửi cho nó. Khi gặp một CTE, câu truy vấn con truyền thống hoặc view, nó coi chúng như nhau và tối ưu hóa chúng theo cùng một cách. Điều này bao gồm xem xét các bảng cơ bản, xem xét các thông số thống kê và chọn cách tiếp cận tốt nhất. Trong hầu hết các trường hợp, chúng sẽ trả về cùng một kế hoạch và do đó thực hiện chính xác như nhau.
Temporary Tables Có làm cho SQL Performance tốt hơn?
Một lựa chọn khác cho truy vấn như vậy là sử dụng bảng tạm để lưu trữ kết quả trung gian trước khi tham gia (join) vào bảng tạm trong câu lệnh select cuối cùng. Việc sử dụng bảng tạm sẽ luôn tạo ra các kế hoạch truy vấn khác nhau, có thể nhanh hơn hoặc chậm hơn, tùy thuộc vào các truy vấn liên quan. Có 2 phương pháp để triển khai bảng tạm, đó là biến bảng (table variable) và bảng tạm trong TempDB (TempDB temporary table).
Sử dụng bảng tạm trong TempDB
CREATE TABLE #TopPurchase(BillToCustomerID INT, Amt DECIMAL(18,2)) INSERT INTO #TopPurchase(BillToCustomerID, Amt) SELECT BillToCustomerID, MAX(ExtendedPrice) Amt FROM Sales.Invoices i INNER JOIN Sales.InvoiceLines il ON i.InvoiceID = il.InvoiceID WHERE InvoiceDate BETWEEN '1/1/2014' AND '3/31/2014' GROUP BY BillToCustomerID; SELECT C.CustomerName, i.InvoiceDate, i.CustomerPurchaseOrderNumber FROM Sales.Invoices i INNER JOIN Sales.InvoiceLines il ON i.InvoiceID = il.InvoiceID INNER JOIN Sales.Customers c ON i.CustomerID = c.CustomerID INNER JOIN #TopPurchase t ON t.BillToCustomerID = i.BillToCustomerID AND t.Amt = il.ExtendedPrice;
Xem xét các thống kê IO cho truy vấn này, chúng ta thấy hai truy vấn khác nhau. Kết quả đầu tiên là cho việc điền dữ liệu vào bảng tạm và kết quả thứ hai là cho câu lệnh SELECT.
Để so sánh kết quả này với các lần thực thi trước, các giá trị cần được cộng lại. Kết quả là có 23.394 lần đọc dữ liệu từ bảng Invoices, 10.259 lần đọc dữ liệu từ bảng InvoiceLines và 40 lần đọc dữ liệu từ bảng Customers. Điều này rất giống nhau, nhưng đôi chút cao hơn so với phiên bản truy vấn đơn.
Cách mà hai truy vấn này được tối ưu hóa rất giống với các truy vấn ở phía trên. Đối với truy vấn chèn dòng vào bảng tạm, trình tối ưu hóa đã xem xét các thống kê của bảng và chọn cách tiếp cận tốt nhất. Trình tối ưu hóa thực tế đã tạo ra thống kê mới cho bảng tạm và sau đó sử dụng chúng để thực thi truy vấn thứ hai. Điều này mang lại hiệu suất rất tương tự nhau.
Sử dụng biến bảng (table variable)
Tùy chọn này liên quan đến việc tạo một bảng trong tempdb bằng cách sử dụng DECLARE @NAME TABLE. SQL Server sẽ cố gắng lưu trữ bảng này trong bộ nhớ.
DECLARE @TopPurchase TABLE(BillToCustomerID INT, Amt DECIMAL(18,2)) INSERT INTO @TopPurchase(BillToCustomerID, Amt) SELECT BillToCustomerID, MAX(ExtendedPrice) Amt FROM Sales.Invoices i INNER JOIN Sales.InvoiceLines il ON i.InvoiceID = il.InvoiceID WHERE InvoiceDate BETWEEN '1/1/2014' AND '3/31/2014' GROUP BY BillToCustomerID; SELECT C.CustomerName, i.InvoiceDate, i.CustomerPurchaseOrderNumber FROM Sales.Invoices i INNER JOIN Sales.InvoiceLines il ON i.InvoiceID = il.InvoiceID INNER JOIN Sales.Customers c ON i.CustomerID = c.CustomerID INNER JOIN @TopPurchase t ON t.BillToCustomerID = i.BillToCustomerID AND t.Amt = il.ExtendedPrice;
Chạy phiên bản truy vấn này trả về những thống kê IO đáng kinh ngạc này.
Câu lệnh chèn (insert) chạy tương tự như phiên bản sử dụng bảng TempDB, nhưng câu lệnh select sử dụng nhiều, nhiều tài nguyên hơn. Điều này bởi vì truy vấn đầu tiên có thể chạy cùng cách trong cả hai trường hợp, nhưng biến bảng không có bất kỳ thống kê bảng nào được tạo ra cho nó như bảng TempDB đã làm. Điều này có nghĩa là trình tối ưu hóa phải đoán mò về cách tiếp tục. Trong ví dụ này, trình tối ưu hóa đã đưa ra một quyết định rất tồi tệ.
Tuy nhiên, không nên loại bỏ hoàn toàn biến bảng. Chúng chắc chắn có vai trò của riêng họ như sẽ được thảo luận sau trong gợi ý.
Khi thông tin thống kê bảng bị thiếu (hoặc không chính xác), điều gì sẽ xảy ra?
Thông tin trên đầu bài viết đã đề cập đôi khi phần trăm ước tính chi phí truy vấn trong kế hoạch thực thi không chính xác. Hãy xem xét một lô truy vấn gọi một trong 3 truy vấn ở phần trên của bài viết này (các truy vấn này thực hiện giống nhau) và sau đó gọi cặp truy vấn từ phiên bản biến bảng tạm thời để thực hiện tổng cộng 3 truy vấn. Dựa trên dữ liệu ở trên, có thể mong đợi rằng truy vấn thứ ba sẽ chiếm phần lớn chi phí vì truy vấn đầu tiên thực hiện khoảng 33.000 lần đọc, truy vấn thứ hai khoảng 16.000 lần đọc và truy vấn thứ ba vượt quá 1.000.000 lần đọc.
Tuy nhiên, ảnh chụp màn hình dưới đây kể một câu chuyện hoàn toàn khác. Đúng là ước tính truy vấn thứ hai là khoảng một nửa công sức của truy vấn đầu tiên, nhưng không thể nói rằng truy vấn thứ ba chỉ chiếm 2% tổng công sức.
Vì vậy, khi tinh chỉnh hiệu suất của một truy vấn, cần xem xét các yếu tố khác (như thống kê IO) và không chỉ dựa vào ước tính chi phí này một cách tuyệt đối.
Làm thế nào để đưa ra quyết định lựa chọn đúng khi chọn câu SQL Performance tốt?
Khi lựa chọn giữa CTE, câu truy vấn con hoặc view, hiệu suất hiếm khi là một yếu tố quyết định. Vậy làm sao để quyết định sử dụng phương pháp nào? Khi nào thì nên sử dụng bảng tạm hay biến bảng?
Có rất nhiều lựa chọn và quyết định phong cách cá nhân ảnh hưởng đến việc sử dụng phương pháp nào, nhưng có những khác biệt rõ ràng giữa các lựa chọn đó có thể giúp hướng dẫn một nhà phát triển T-SQL đến một giải pháp.
Tác giả hiếm khi sử dụng câu truy vấn con truyền thống vì chúng khó đọc và không cung cấp các tính năng đặc biệt như các phương pháp khác. Chúng chỉ được ưu tiên cho câu truy vấn con rất nhỏ có thể viết trên một dòng mã T-SQL duy nhất.
CTE là một lựa chọn phổ biến vì, khác với câu truy vấn con truyền thống, chúng có thể được tổ chức ở đầu truy vấn, giúp truy vấn cuối cùng dễ đọc hơn. Chúng cũng có thể lồng nhau sao cho bất kỳ CTE nào bắt đầu từ CTE thứ hai đều có thể tham chiếu đến bất kỳ CTE trước đó trong truy vấn. Một CTE thậm chí có thể tham chiếu đến chính nó!
Views hữu ích khi truy vấn có khả năng được sử dụng lại. Dễ dàng chia sẻ logic giữa các truy vấn khi nó được lưu trữ như một đối tượng riêng trong cơ sở dữ liệu.
Bảng tạm và biến bảng hữu ích khi kết quả trung gian sẽ được sử dụng nhiều lần trong cùng một lô truy vấn. Thường thì việc lưu trữ những giá trị đó thay vì tính toán lại nhiều lần là hợp lý.
Việc lựa chọn giữa bảng tạm tạm trên TempDB và biến bảng khá đơn giản. Một bảng tạm sẽ được lưu trữ trên đĩa và có thống kê tính toán trên nó, trong khi biến bảng không có. Vì
sự khác biệt này, bảng tạm thích hợp khi số hàng dự kiến lớn hơn 100 và biến bảng thích hợp cho số hàng dự kiến nhỏ hơn, nơi thiếu thống kê ít có khả năng dẫn đến một kế hoạch truy vấn kém.