Những thói quen tốt khi viết câu lệnh T-SQL (Phần 2).

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. ...
Nội dung trích xuất từ tài liệu:
Những thói quen tốt khi viết câu lệnh T-SQL (Phần 2).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úngta 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àiviế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ữngthao 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ưutrữ đa năng. Phần 1Thủ tục lưu trữ đa năngTrướ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 AllTrades 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 đếnthủ 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 ghinà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)ASSELECT * FROM AdventureWorks.Sales.SalesOrderDetailWHERE (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ộttham 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:( = @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 bằng giá trị của @PARM. Phần thứ hai của điềukiện đó là “@PARM IS NULL”. Phần này có nghĩa nếu @PARM không có giá trị truyềnvà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ớilệnh sau:EXEC JackOfAllTrades @SalesOrderID = 43659Khi 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áychủ 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“( = @PARM1 or @PARM1 IS NULL)” bởi lẽ hằng số đang ở trongmệ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ànggiả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ăngBạ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ếtlạ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 ưuhơ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 NULLkhô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 ISNULL”? Câu trả lời đó là sử dụng SQL động được thông số hóa (parameterized dynamicSQL).Đế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ủamì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ớimộ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ằngcách truyền tham số tới SP “sp_executesql” khi chạy SP này. Câu lệnh T-SQL được thựcthi 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ệclà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ưutrữ đ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 (@SalesOrderI ...