Những thói quen tốt khi viết câu lệnh T-SQL- P4

Tham khảo tài liệu những thói quen tốt khi viết câu lệnh t-sql- p4, công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả
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- P4rằng bằng giá trị của @PARM.Phần thứ hai của điều kiện đó là “@PARM ISNULL”. Phần nà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ộcdữ liệu dựa trên tham số ấy.Cùng xem quá trình thực thi điển hình củaJackOfAllTrades SP. Giả sử ta thực thi SP với lệnhsau: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 đượctruyề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ộccộ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ôngminh để nhận ra rằng xử lý thủ tục lưu trữ đa năngbằng thao tác “tìm kiếm chỉ mục” sẽ nhanh hơn là laovà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 ISNULL”, nó như một hằng số đối với 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 “( =@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ăngcà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ỗitham 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ưutrữ đ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ấncủ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àocó phải NULL không. Nếu ta có thể loại bỏ yêu cầunà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ở đườngcho SQL injection vào giải pháp của mình. Tuynhiên, chúng ta sẽ chỉ xây dựng mã SQL động chophé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ểncâu lệnh T-SQL có chứa tham số, đồng thời cho phépbạ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 đượcthông số hóa. Có nhiều lý do để sử dụng SQL đượcthông số hóa, nhưng trong khuôn khổ bài viết nàychú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ệusuất của thủ tục lưu trữ đa năng. Sau đây là đoạncode tạo thủ tục lưu trữ đa năng được viết lại sử dụngSQL động được thông số hóa:CREATE PROCEDURE JackOfAllTrades_V2(@SalesOrderID int = NULL,@SalesOrderDetailID int = NULL,@CarrierTrackingNumbernvarchar(25) = NULL)ASDECLARE @CMD NVARCHAR(max)DECLARE @WHERE NVARCHAR(max)SET @CMD = SELECT * FROMAdventureWorks.Sales.SalesOrderDetail SET @WHERE = IF @SalesOrderID IS NOT NULL SET @WHERE = @WHERE + AND