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

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:
CREATE PROCEDURE JackOfAllTrades_V2 (@SalesOrderID int = NULL
,@SalesOrderDetailID int = NULL
,@CarrierTrackingNumber nvarchar(25) = NULL)
AS
DECLARE @CMD NVARCHAR(max)
DECLARE @WHERE NVARCHAR(max)
SET @CMD = 'SELECT * FROM AdventureWorks.Sales.SalesOrderDetail '
SET @WHERE = ''
IF @SalesOrderID IS NOT NULL
SET @WHERE = @WHERE + 'AND SalesOrderID = @SalesOrderID '

Cuối cùng tôi sử dụng SP “sp_executesql” để thực thi câu lệnh T-SQL động thông số
hóa. Để thực hiện điều này, tôi truyền năm tham số vào SP hệ thống. Tham số đầu tiên là
biến T-SQL động @CMD. Tham số thứ hai khai báo tất cả các biến có thể có trong đoạn
truy vấn được thông số hóa, cùng với loại dữ liệu của chúng. Với ba tham số cuối cùng,
chúng chỉ được truyền vào SP hệ thống giống như chúng được truyền vào SP lưu trữ đa
năng trong phần đầu. Như bạn có thể thấy, tôi hoàn toàn không làm đoạn mã SQL động
của mình trở nên dễ bị tấn công bằng SQL injection hơn SP ban đầu. Lý do là vì tôi
không sử dụng giá trị thực của tham số để chuyển tới biến @WHERE. Tôi chỉ truyền
tham số như các biến vào SQL động qua SP hệ thống “sp_executesql”.
Bây giờ hãy chạy đoạn code tạo thủ tục lưu trữ đa năng mới viết lại bằng cách chạy câu
lệnh sau:
EXEC JackOfAllTrades_V2 @SalesOrderID = 43659
Khi chạy thử nghiệm với cơ sở dữ liệu AdventureWorks trên server, tôi nhận được sơ đồ
thực thi như sau:

Khi so sánh sơ đồ này với sơ đồ ở phần đầu, bạn có thể thấy nó đơn giản hơn và sử dụng
thao tác “tìm kiếm chỉ mục cụm” để xử lý SP. Sở dĩ máy chủ SQL có thể sử dụng thao
tác này là vì đoạn code SQL động không còn điều kiện “@PARM IS NULL” nữa. Do
câu lệnh T-SQL đã được đơn giản hóa nhờ sử dụng SQL động và loại bỏ ràng buộc IS
NULL, máy chủ SQL giờ đây có thể đưa ra kế hoạch thực thi tối ưu hơn cho thủ tục lưu
trữ đa năng phiên bản V2.
Kết lại, thực tế thì hiệu quả chúng ta thu được ở mức nào? Nên nhớ trên đây ta chỉ mới
xét những bản ghi từ bảng SalesOrderDetail có SalesOrderID bằng 43659. SP lưu trữ đa
năng ban đầu sử dụng thao tác “quét chỉ mục” để xử lý truy vấn. Điều đó có nghĩa nó
phải đọc lần lượt toàn bộ chỉ mục trước khi có thể hoàn thành yêu cầu truy vấn và trả về
bản ghi chứa một giá trị SalesOrderID. Ngược lại, phiên bản V2 của SP lưu trữ đa năng
có thể sử dụng thao tác “tìm kiếm chỉ mục” với khóa chỉ mục cụm trên bảng
SalesOrderDetail để lấy trực tiếp những bản ghi nhất định có chứa SalesOrderID bằng
43659 một cách nhanh chóng. Thao tác “tìm kiếm chỉ mục” tối ưu hơn thao tác “quét chỉ
mục” rất nhiều, nhưng cụ thể nhiều như thế nào?

Cải thiện lượng I/O nhờ sử dụng SQL động được thông số hóa
Sau khi đọc hết phần này, bạn cần hiểu được lý do vì sao máy chủ SQL lại đưa ra bản sơ
đồ thực thi kém hiệu quả. Trên đây máy chủ SQL đã coi logic “@PARM IS NULL” như
một hằng số. Bởi vậy nó quyết định cần phải thực hiện thao tác “quét chỉ mục” để xử lý
phiên bản thủ tục lưu trữ đa năng đầu tiên. Như chúng ta đã biết, thao tác quét (SCAN)
luôn chậm hơn thao tác tìm kiếm (SEEK). Bằng cách viết lại phiên bản SP lưu trữ đa
năng V2 có sử dụng T-SQL động, tôi đã loại bỏ được biểu thức hằng số trong mệnh đề
WHERE của câu lệnh T-SQL. Nhờ vậy máy chủ SQL đã tìm được phương pháp đúng
đắn hơn đó là sử dụng thao tác “tìm kiếm chỉ mục cụm”. Nếu trang web của bạn có sử
dụng thủ tục lưu trữ đa năng, hãy thử viết lại nó bằng SQL động được thông số hóa và
chờ xem hiệu suất sẽ được cải thiện thế nào.