Những thói quen tốt khi viết
câu lệnh T-SQL (Phần 2)
Để nâng cao hiệu suất máy chủ SQL và giảm thiểu các lỗi tiềm tàng cho ứng dụng, chúng
ta cần phải tập viết code câu lệnh T-SQL một cách tối ưu nhất. Trong phần đầu của bài
viết, các bạn đã được giới thiệu một số thủ thuật hữu ích giúp máy chủ giảm bớt những
thao tác thừa. Phần hai này sẽ tập trung vào việc làm thế nào để tối ưu hóa thủ tục lưu
trữ đa năng.
Phần 1
Thủ tục lưu trữ đa năng
Trước khi bước vào vấn đề làm thế nào để tối ưu hóa thủ tục lưu trữ đa năng (Jack Of All
Trades Stored Procedure - SP), chúng ta cần có một chút khái niệm về loại thủ tục này.
Thủ tục lưu trữ đa năng là thủ tục chấp nhận nhiều tham số khác nhau có liên quan đến
thủ tục. Dựa trên các tham số được truyền vào, thủ tục lưu trữ đa năng xác định bản ghi
nào sẽ được trả về. Sau đây là một ví dụ về thủ tục lưu trữ đa năng:
CREATE PROCEDURE JackOfAllTrades (@SalesOrderID int = NULL
,@SalesOrderDetailID int = NULL
,@CarrierTrackingNumber nvarchar(25) = NULL)
AS
SELECT * FROM AdventureWorks.Sales.SalesOrderDetail
WHERE
(SalesOrderID = @SalesOrderID or @SalesOrderID IS NULL)
AND (SalesOrderDetailID = @SalesOrderDetailID or
@SalesOrderDetailID IS NULL)
AND (CarrierTrackingNumber = @CarrierTrackingNumber or
@CarrierTrackingNumber IS NULL)
GO
Ở đây SP JackOfAllTrades chấp nhận ba tham số khác nhau. Tất cả các tham số này có
giá trị mặc định là NULL. Khi một giá trị được truyền vào, nó sẽ được sử dụng như một
tham số trong mệnh đề WHERE để ràng buộc các bản ghi trả về. Mỗi tham số trong SP
được dùng để xây dựng một mệnh đề WHERE phức tạp chứa logic sau đây trong mệnh
đề WHERE đối với mỗi tham số truyền vào:
(<TableColumn> = @PARM or @PARM IS NULL)
Logic trên cho biết nếu @PARM được truyền giá trị non-null thì sẽ ràng buộc bản ghi trả
về để chắc chắn rằng <TableColumn> bằng giá trị của @PARM. Phần thứ hai của điều
kiện đó là “@PARM IS NULL”. Phần này có nghĩa nếu @PARM không có giá trị truyền
vào (bằng NULL) thì không ràng buộc dữ liệu dựa trên tham số ấy.
Cùng xem quá trình thực thi điển hình của JackOfAllTrades SP. Giả sử ta thực thi SP với
lệnh sau:
EXEC JackOfAllTrades @SalesOrderID = 43659
Khi chạy câu lệnh, sơ đồ thực thi trông như sau:
Ở đây bạn có thể thấy đối với mỗi tham số đơn được truyền vào, máy chủ quyết định sử
dụng thao tác “quét chỉ mục”. Câu lệnh SELECT của SP ràng buộc cột duy nhất
@SalesOrderID - một phần của khóa chỉ mục cụm. Bạn có thể nghĩ máy chủ SQL đủ
thông minh để nhận ra rằng xử lý thủ tục lưu trữ đa năng bằng thao tác “tìm kiếm chỉ
mục” sẽ nhanh hơn là lao vào chỉ mục cụm. Thế nhưng như ta thấy trên sơ đồ thực thi,
máy chủ SQL không thông minh đến vậy. Tại sao thế?
Khi máy chủ nhìn thấy điều kiện “@PARM IS NULL”, nó như một hằng số đối với máy
chủ SQL. Vì thế máy chủ coi như không có chỉ mục nào hữu ích giúp xử lý điều kiện
“(<TableColumn> = @PARM1 or @PARM1 IS NULL)” bởi lẽ hằng số đang ở trong
mệnh đề WHERE. Chính vì vậy mà máy chủ SQL quyết định sử dụng thao tác “quét chỉ
mục” để giải quyết vấn đề. Thủ tục lưu trữ đa năng càng có nhiều tham số, hiệu suất càng
giảm do tác động của số lượng thao tác quét cần thiết cho mỗi tham số truyền vào.
Tối ưu hóa thủ tục lưu trữ đa năng
Bạn không cần phải chấp nhận sử dụng thủ tục lưu trữ đa năng rất kém hiệu quả như SP
đã viết trong ví dụ trên. Hãy cùng khám phá xem SP sau đây có thể làm những gì và viết
lại nó để công cụ tối ưu truy vấn của máy chủ SQL có thể tạo kế hoạch thực thi tối ưu
hơn.
Như đã nói ở trên, vấn đề thực sự với thủ tục lưu trữ đa năng đó là đối với mỗi tham số
bạn cần có một điều kiện “OR” để kiểm tra xem tham số truyền vào có phải NULL
không. Nếu ta có thể loại bỏ yêu cầu này, máy chủ SQL sẽ có khả năng lên kế hoạch sử
dụng thao tác “tìm kiếm chỉ mục”. Vậy làm thế nào để loại bỏ điều kiện “@PARM IS
NULL”? Câu trả lời đó là sử dụng SQL động được thông số hóa (parameterized dynamic
SQL).
Đến đây có thể các bạn nghĩ tôi chuẩn bị mở đường cho SQL injection vào giải pháp của
mình. Tuy nhiên, chúng ta sẽ chỉ xây dựng mã SQL động cho phép truyền tham số SP tới
một SP hệ thống khác là “sp_executesql”. SP này sẽ sử dụng các tham số trong đoạn mã
SQL động ta xây dựng.
SP hệ thống “sp_executesql” cho phép bạn phát triển câu lệnh T-SQL có chứa tham số,
đồng thời cho phép bạn định nghĩa và truyền giá trị cho các tham số tới SQL động bằng
cách truyền tham số tới SP “sp_executesql” khi chạy SP này. Câu lệnh T-SQL được thực
thi theo cách này thường gọi là SQL được thông số hóa. Có nhiều lý do để sử dụng SQL
được thông số hóa, nhưng trong khuôn khổ bài viết này chúng ta chỉ tập trung vào việc
làm thế nào để sử dụng SQL được thông số hóa nhằm cải thiện hiệu suất của thủ tục lưu
trữ đa năng. Sau đây là đoạn code tạo thủ tục lưu trữ đa năng được viết lại sử dụng SQL
động được thông số hóa: