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.
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ú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 hainà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ữ đanăng.Thủ 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ữ đanăng (Jack Of All Trades Stored Procedure - SP), chúng ta cần có mộtchú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ấpnhận nhiều tham số khác nhau có liên quan đến thủ tục. Dựa trên cáctham 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 (@SalesOrderIDint = NULL ,@SalesOrderDetailID int = NULL ,@CarrierTrackingNumber nvarchar(25)= NULL)ASSELECT * FROMAdventureWorks.Sales.SalesOrderDetailWHERE (SalesOrderID = @SalesOrderID or@SalesOrderID IS NULL) AND (SalesOrderDetailID = @SalesOrderDetailIDor @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áctham số này có giá trị mặc định là NULL. Khi một giá trị được truyềnvào, nó sẽ được sử 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âydự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àngbuộ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ều kiện đó là “@PARM IS NULL”. Phầnnày có nghĩa nếu @PARM không có giá trị truyền và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ử tathực thi 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 SPràng buộc cột duy nhất @SalesOrderID - một phần của khóa chỉ mụccụ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ằngsố đối với máy chủ SQL. Vì thế máy chủ coi như không có chỉ mục nàohữ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àng có nhiều thamsố, 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ếtcho 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émhiệ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áychủ 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ớimỗ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áychủ 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âutrả lời đó là sử dụng SQL động được thông số hóa (parameterizeddynamic SQL).Đến đây có thể các bạn nghĩ tôi chuẩn bị mở đường cho SQL injectionvà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áctham 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ọilà 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ấtcủa thủ tục lưu trữ đa năng. Sau đây là đoạn code tạo thủ tục lưu trữ đanăng được viết lại sử dụng SQL động được thông số hóa:CREATE PROCEDURE JackOfAllTrades_V2(@SalesOrderID int = NULL ...