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.
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 2Những thói quen tốt khi viết câu lệnh T-SQL (Phần 2)Nguồn:quantrimang.comQuả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ềmtà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ậptrung 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 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 All Trades Stored Procedure - SP), chúng ta cần có một chút khái niệmvề 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)ASSELECT * FROM AdventureWorks.Sales.SalesOrderDetailWHERE (SalesOrderID = @SalesOrderID or @SalesOrderIDIS 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 thamsố này có giá trị mặc định là NULL. Khi một giá trị được truyền vào, nó sẽ đượcsử 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ứctạ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ảnghi trả về để chắc chắn rằng 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 @PARMkhông có giá trị truyền vào (bằng NULL) thì không ràng buộc dữ liệu dựa trêntham số ấy.Cùng xem quá trình thực thi điển hình của JackOfAllTrades SP. Giả sử ta thựcthi SP với lệ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ộtduy 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ằngthao tác “tìm kiếm chỉ mục” sẽ nhanh hơn là lao vào chỉ mục cụm. Thế nhưngnhư ta thấy trên sơ đồ thực thi, máy chủ SQL không thông minh đến vậy. Tại saothế?Khi máy chủ nhìn thấy điều kiện “@PARM IS NULL”, nó như một hằng số đối vớimá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 “( = @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àngcó 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étcầ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àmnhữ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ỗitham 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ạibỏ điều kiện “@PARM IS NULL”? Câu trả lời đó là sử dụng SQL động đượcthô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ảipháp của mình. Tuy nhiên, chúng ta sẽ chỉ xây dựng mã SQL động cho phéptruyề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ứatham số, đồng thời cho phép bạn định nghĩa và truyền giá trị cho các tham số tớiSQL độ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 đượcthô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 đượcthông số hóa:CREATE PROCEDURE JackOfAllTrades_V2 (@SalesOrderID i ...