intTypePromotion=1
zunia.vn Tuyển sinh 2024 dành cho Gen-Z zunia.vn zunia.vn
ADSENSE
 » 
 » 

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

Chia sẻ: Pt Pt | Ngày: | Loại File: PDF | Số trang:40

Thêm vào BST
Báo xấu
146
lượt xem 29
download
 
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Hiệu năng ứng dụng phụ thuộc vào rất nhiều yếu tố, trong đó có một yếu tố rất quan trọng đó là thời gian để máy chủ SQL xử lý câu lệnh T-SQL. Đôi khi thiết kế cơ sở dữ liệu và các yêu cầu truy vấn phức tạp làm cản trở tốc độ thực thi của các câu lệnh T-SQL. Cách viết code từng câu lệnh T-SQL cũng có thể khiến máy chủ SQL phải làm việc nhiều hơn để xử lý truy vấn. Bài viết sau đây sẽ giới thiệu cho bạn những thói quen tốt nên tập luyện...

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

  1. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com Những thói quen tốt khi viết câu lệnh T-SQL
  2. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com Hiệu năng ứng dụng phụ thuộc vào rất nhiều yếu tố, trong đó có một yếu tố rất quan trọng đó là thời gian để máy chủ SQL xử lý câu lệnh T-SQL. Đôi khi thiết kế cơ sở dữ liệu và các yêu cầu truy vấn phức tạp làm cản trở tốc độ thực thi của các câu lệnh T-SQL. Cách viết code từng câu lệnh T-SQL cũng có thể khiến máy chủ SQL phải làm việc nhiều hơn để xử lý truy vấn. Bài viết sau đây sẽ giới thiệu cho bạn những thói quen tốt nên tập luyện khi viết code T- SQL. Qua đó bạn có thể hiểu làm thế nào để viết các câu truy vấn tối ưu, tận dụng tốt tài nguyên máy chủ SQL và cải thiện hiệu suất. Ghi rõ tên cột trong câu lệnh SELECT SELECT * FROM MyTable; Bạn đã từng viết câu lệnh như trên bao nhiêu lần? Việc sử dụng dấu sao (*) cho cơ sở dữ liệu biết rằng bạn muốn trả về tất cả các cột từ bảng (hoặc các bảng) được khai báo trong mệnh đề FROM. Đây
  3. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com không phải là một thói quen tốt ngay cả khi bạn muốn tất cả các cột được trả về ứng dụng. Tốt hơn bạn nên ghi rõ tên từng cột trong bảng như sau: SELECT ID, Description, DateModified FROM MyTable; Việc khai báo rõ ràng tên các cột trong câu lệnh SELECT mang lại rất nhiều lợi ích. Thứ nhất, máy chủ SQL sẽ chỉ trả về dữ liệu cần thiết cho ứng dụng chứ không phải là một đống dữ liệu mà trong đó có nhiều thứ ứng dụng của bạn không hề cần đến. Bằng cách chỉ yêu cầu trả về những dữ liệu cần thiết, bạn đã góp phần tối ưu hóa khối lượng công việc máy chủ SQL cần thực hiện để thu thập tất cả các cột của thông tin bạn yêu cầu. Ngoài ra, nhờ không sử dụng dấu sao (*) nên bạn đã giảm thiểu lưu lượng truyền tải qua mạng (số byte) cần thiết để gửi các dữ liệu liên quan đến câu lệnh SELECT tới ứng dụng.
  4. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com Trong trường hợp bạn dùng dấu sao (*) và có một ai đó thêm cột mới vào bảng, ứng dụng của bạn sẽ bắt đầu tiếp nhận dữ liệu cho cột này mà không thay đổi mã nguồn ứng dụng. Nếu ứng dụng của bạn chờ đợi số lượng cột nhất định được trả về, nó sẽ bị lỗi ngay khi có người bổ sung cột mới vào một trong các bảng tham chiếu. Bằng việc khai báo rõ ràng tên từng cột trong câu lệnh SELECT, ứng dụng sẽ luôn nhận được số lượng cố định các cột trả về, ngay cả khi có người thêm cột mới vào các bảng tham chiếu trong câu lệnh SELECT. Nhờ vậy, bạn đã giúp ứng dụng tránh khỏi những nguy cơ tiềm tàng liên quan đến các thay đổi cơ sở dữ liệu có thể xảy đến với bất kỳ bảng nào bạn tham chiếu tới trong câu lệnh SELECT. Ghi rõ tên cột trong câu lệnh INSERT Cũng giống như trên, bạn nên chỉ rõ tên từng cột bạn muốn chèn dữ liệu vào trong câu lệnh INSERT. Đừng viết câu lệnh INSERT như sau:
  5. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com INSERT INTO MyTable VALUES ('A','B','C'); Khi bạn viết kiểu này, máy chủ SQL đòi hỏi chỉ đúng ba cột được định nghĩa trong bảng MyTable, và giá trị “A” sẽ được chèn vào cột đầu tiên, “B” vào cột thứ hai, “C” vào cột cuối. Nếu ai đó thêm mới một cột vào bảng MyTable, ứng dụng của bạn sẽ bị lỗi: Msg 213, Level 16, State 1, Line 1 Column name or number of supplied values does not match table definition. (Tên cột hoặc số lượng giá trị không khớp với bảng) Vì thế, thay vì viết câu lệnh INSERT như trên, bạn nên viết như sau: INSERT INTO MyTable(So1, So2, So3) VALUES ('A','B','C'); Bằng cách viết trên, khi ai đó thêm mới một cột tên là “So4” vào bảng MyTable, câu lệnh INSERT vẫn tiếp
  6. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com tục làm việc với điều kiện cột “So4” được tạo với giá trị mặc định DEFAULT hoặc cho phép NULL. Thêm tiền tố cho wildcard để tăng tốc tìm kiếm Sử dụng các ký tự thay thế (wildcard) thích hợp có thể cải thiện hiệu suất câu truy vấn. Chẳng hạn bạn muốn tìm kiếm trong bảng AdventureWorks.Person.Contact tất cả LastNames kết thúc bằng “sen”. Giả dụ rằng bạn đã xây dựng một chỉ mục trên cột LastName. Nếu bạn viết câu lệnh tìm kiếm như sau: SELECT Distinct LastName FROM Person.Contact WHERE LastName LIKE '%sen' Câu lệnh sử dụng ký tự phần trăm (%) để thay thế cho không hoặc nhiều ký tự được theo sau bởi chuỗi “sen” trong trường LastName. Điều này khiến máy chủ SQL thực hiện thao tác quét chỉ mục nhằm tìm kiếm tất cả các tên kết thúc bằng “sen” để giải quyết
  7. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com câu truy vấn. Việc này rất có ý nghĩa bởi cho đến khi toàn bộ bảng được quét, máy chủ SQL không thể đảm bảo rằng đã tìm ra toàn bộ các bản ghi có LastName kết thúc bằng “sen”. Ngoài ra, nếu bạn đang tìm kiếm các bản ghi có LastName dài đúng sáu ký tự và kết thúc bằng “sen”, bạn có thể viết câu lệnh tìm kiếm như sau: SELECT Distinct LastName FROM Person.Contact WHERE LastName LIKE '___sen’ Ở đây, câu lệnh sử dụng ký tự gạch dưới (_) để thay thế cho một ký tự đơn. Ví dụ này tương tự với ví dụ ở phần trên và sử dụng thao tác quét chỉ mục để giải quyết. Một lần nữa, máy chủ SQL biết rằng cần phải quét toàn bộ chỉ mục trước khi đảm bảo đã tìm thấy tất cả các tên dài sáu ký tự và kết thúc bằng “sen” trong bảng Person.Contact.
  8. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com Máy chủ SQL có thể trả về kết quả nhanh hơn nếu nó không phải đọc toàn bộ chỉ mục bằng cách sử dụng cơ chế quét. Máy chủ SQL đủ thông minh để nhận biết khi bạn đặt thêm tiền tố trước ký tự thay thế (%, _, v.v..), nó có thể dùng một thao tác tìm kiếm chỉ mục để tiến hành giải quyết tiêu chí tìm kiếm. Sau đây là một ví dụ về câu lệnh tìm kiếm yêu cầu trả về tất cả bản ghi có LastName bắt đầu bằng ký tự “A” và kết thúc bằng “sen”: SELECT Distinct LastName FROM Person.Contact WHERE LastName LIKE 'A%sen' Bằng cách đặt ký tự “A” phía trước dấu phần trăm (%) trong câu lệnh tìm kiếm, bạn đã cho máy chủ SQL biết rằng nó có thể sử dụng một thao tác tìm kiếm chỉ mục để giải quyết câu truy vấn. Một khi
  9. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com máy chủ SQL đọc tới bản ghi cuối cùng có LastName bắt đầu bằng ký tự “A”, nó biết rằng không còn bản ghi nào có LastName bắt đầu bằng ký tự “A” nữa và sẽ dừng lại. Không phải tất cả ký tự thay thế đều cần thêm tiền tố để máy chủ SQL dùng một thao tác tìm kiếm chỉ mục nhằm giải quyết truy vấn. Sau đây là một ví dụ trong đó câu lệnh sử dụng biểu thức thể hiện một tập hợp ký tự thay thế và vẫn cho phép máy chủ SQL giải quyết câu truy vấn bằng cách dùng một thao tác tìm kiếm chỉ mục: SELECT Distinct LastName FROM Person.Contact WHERE LastName LIKE '[A-M]%sen' Câu lệnh T-SQL trên tìm kiếm toàn bộ LastName bắt đầu bằng một ký tự bất kỳ trong khoảng từ “A” đến
  10. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com “M” và kết thúc bằng “sen”. Các cú pháp sử dụng ký tự thay thế khác trong đó chỉ rõ tập hợp các ký tự cũng có thể gọi thao tác quét chỉ mục để giải quyết tiêu chí tìm kiếm. Chỉ dùng DISTINCT khi cần Đặt từ khóa DISTINCT trong câu lệnh SELECT sẽ loại bỏ các kết quả trùng lặp trong số những kết quả trả về của câu truy vấn. Nó khiến máy chủ SQL phải thực hiện thêm thao tác SORT để sắp xếp dữ liệu nhằm nhận biết và loại bỏ các bản trùng lặp. Vì thế, nếu bạn biết trước các kết quả trả về sẽ không trùng lặp thì không nên dùng từ khóa DISTINCT trong câu lệnh T-SQL. Với việc sử dụng từ khóa DISTINCT trong câu truy vấn, bạn đã yêu cầu máy chủ SQL thực hiện thao tác sắp xếp vào loại bỏ các kết quả trùng lặp. Đây là phần công việc phụ thêm của máy chủ
  11. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com SQL và không có ý nghĩa gì nếu tập hợp kết quả của bạn chỉ bao gồm những bản ghi độc nhất. Chỉ dùng UNION khi cần Cũng giống như trường hợp từ khóa DISTINCT, toán tử UNION đòi hỏi thêm thao tác SORT để máy chủ SQL có thể loại bỏ những kết quả trùng lặp. Nếu bạn biết trước danh sách kết quả trả về không có kết quả nào giống nhau thì thao tác sắp xếp mà máy chủ SQL phải thực hiện trở nên không cần thiết. Bởi vậy khi bạn cần dùng toán tử UNION để nối hai tập hợp bản ghi với nhau, trong đó các bản ghi là độc nhất không trùng lặp, tốt hơn bạn nên dùng toán tử UNION ALL. Toán tử UNION ALL không loại bỏ các bản ghi trùng lặp bởi vậy sẽ giảm nhẹ phần công việc cho máy chủ SQL trong quá trình xử lý do không phải
  12. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com thực hiện thao tác sắp xếp. Giảm bớt công việc cho máy chủ SQL đồng nghĩa với việc thao tác xử lý sẽ được thực hiện nhanh hơn. Áp dụng các thói quen tốt để code nhanh hơn Có rất nhiều lý do để tập luyện cho mình quen với lối viết code tối ưu. Khi bạn áp dụng thành thạo những thủ thuật nhỏ nêu trên và biến nó thành thói quen mỗi khi viết câu lệnh T-SQL, bạn sẽ tránh được rủi ro có thể xảy ra khi cơ sở dữ liệu thay đổi, đồng thời cải thiện hiệu suất làm việc của máy chủ nhờ giảm thiểu lưu lượng truyền tải qua mạng. Những thủ thuật rất đơn giản này còn giúp bạn tận dụng tốt hơn tài nguyên máy chủ trong quá trình xử lý câu lệnh.
  13. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com Để 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. Thủ tục lưu trữ đa năng Trướ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ệ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 đến thủ tục. Dựa trên các tham số được truyền vào, thủ
  14. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com 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) AS SELECT * FROM AdventureWorks.Sales.SalesOrderDeta il WHERE (SalesOrderID = @SalesOrderID or @SalesOrderID IS NULL) AND (SalesOrderDetailID = @SalesOrderDetailID or @SalesOrderDetailID IS
  15. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com 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ộ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ứ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
  16. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com 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:
  17. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com Ở đâ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
  18. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com 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ả
  19. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com 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
  20. Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com 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
ADSENSE

CÓ THỂ BẠN MUỐN DOWNLOAD

THÔNG TIN
TRỢ GIÚP
HỖ TRỢ KHÁCH HÀNG
Theo dõi chúng tôi

Chịu trách nhiệm nội dung:

Nguyễn Công Hà - Giám đốc Công ty TNHH TÀI LIỆU TRỰC TUYẾN VI NA

LIÊN HỆ

Địa chỉ: P402, 54A Nơ Trang Long, Phường 14, Q.Bình Thạnh, TP.HCM

Hotline: 093 303 0098

Email: support@tailieu.vn

Giấy phép Mạng Xã Hội số: 670/GP-BTTTT cấp ngày 30/11/2015 Copyright © 2022-2032 TaiLieu.VN. All rights reserved.

 

Đồng bộ tài khoản
4=>1