Những thói quen tốt khi viết câu lệnh T-SQL (Phần 2)
Ngu
ồ
n:quantrimang.com
Quản Trị Mạng - Để 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)

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ổ

, N'@SalesOrderID int
,@SalesOrderDetailID int
,@CarrierTrackingNumber nvarchar(25)'
,@SalesOrderID = @SalesOrderID
,@SalesOrderDetailID =
@SalesOrderDetailID
,@CarrierTrackingNumber =
@CarrierTrackingNumber
Tiếp theo chúng ta sẽ đi sâu vào chi tiết nhằm giúp bạn hiểu rõ phần động và
phần thông số hóa của đoạn mã trên. SP này bắt đầu bằng việc gán biến @CMD
vào câu lệnh SELECT không có mệnh đề WHERE. Tiếp theo ta gán biến
@WHERE cho một chuỗi rỗng. Tiếp đó là bốn câu IF khác nhau. Ba câu IF đầu
tiên kiểm tra xem mỗi tham số truyền vào có thỏa mãn điều kiện NOT NULL hay
không. Nếu một tham số
NOT NULL, ta sẽ gắn điều kiện vào biến @WHERE đối
với tham số ấy. Do ta đã kiểm tra và xác định tham số đó NOT NULL, ta không
cần thêm điều kiện IS NULL vào mệnh đề WHERE như đoạn code SP ban đầu ở
phần trên. Thay vào đó, tất cả những gì ta cần là thêm điều kiện <TableColumn>
= @PARM vào biến @WHERE. Câu If cuối cùng xác định xem biến @WHERE
có thỏa mãn ít nhất một điều kiện hay không, và nếu có thì nó sẽ nố
i biến
@WHERE với biến @CMD.
Lưu ý rằng biến @WHERE là phần động của đoạn code. Nhưng tôi không đặt
phần text thực của tham số vào biến @WHERE, thay vào đó chỉ đặt một tham
chiếu tới các tham số trong điều kiện WHERE. Vì thế câu lệnh T-SQL động cơ
bản chỉ bao gồm câu lệnh SELECT ban đầu và mệnh đề WHERE không còn cần
điều kiện IS NULL để ràng buộc dữ liệu nữ
a.
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.

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?
Việc đánh giá khoản I/O tiết kiệm được nhờ dùng phiên bản SP lưu trữ đa năng
V2 có thể thực hiện bằng nhiều cách. Ta sẽ chạy đoạn T-SQL sau đây:
SET STATISTICS IO ON
GO
EXEC JackOfAllTrades @SalesOrderID = 43659
GO
EXEC JackOfAllTrades_V2 @SalesOrderID = 43659
GO
Ở đây tôi sử dụng lệnh “SET STATISTICS IO ON” nên kết quả của 2 SP đang
thực thi sẽ hiển thị số lượng I/O mỗi lệnh đòi hỏi để xử
lý truy vấn. Dưới đây là
kết quả nhận được:
(12 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 264,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-
ahead reads 0.

(1 row(s) affected)

(12 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 3,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-
ahead reads 0.