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:
= @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. 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?
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.
(1 row(s) affected)
Khi nhìn kết quả trên, ta có thể thấy hiệu suất của SP lưu trữ đa năng đầu tiên là 1 lần quét và 264 lần đọc logic. Ngược lại phiên bản V2 có cùng số lần quét chỉ mục nhưng chỉ cần thực hiện 3 lần đọc logic để xử lý truy vấn. Khoản I/O tiết kiệm đc là 261. Con số này có vẻ không thấm tháp gì, tuy nhiên với trường hợp bạn phải gọi đi gọi lại SP trong một vòng lặp nào đó chẳng hạn, hiệu suất sẽ được cải thiện một cách rõ rệt giữa hai phiên bản SP.
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.