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

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:5

Thêm vào BST

Báo xấu

123
lượt xem 28
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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ả

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

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

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 @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ử ta thực thi SP với lệnh sau: EXEC JackOfAllTrades @SalesOrderID = 43659 Khi 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á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à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ỗi tham số truyền vào. Tối ưu hóa thủ tục lưu trữ đa năng 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: CREATE PROCEDURE JackOfAllTrades_V2 (@SalesOrderID int = NULL ,@SalesOrderDetailID int = NULL ,@CarrierTrackingNumber nvarchar(25) = NULL) AS DECLARE @CMD NVARCHAR(max) DECLARE @WHERE NVARCHAR(max) SET @CMD = 'SELECT * FROM AdventureWorks.Sales.SalesOrderDeta il ' SET @WHERE = '' IF @SalesOrderID IS NOT NULL SET @WHERE = @WHERE + 'AND