Phần 1: Tối ưu hóa cơ sở dữ liệu (edit)

Tối ưu hóa Cơ sở dữ liệu là một công việc thật sự thử thách, nhất là khi bạn làm việc trên các CSDL có quy mô đủ lớn. Lúc này ngoài yêu cầu về tính đúng đắn thì bài toán hiệu suất, thời gian thực thi của ứng dụng là rất quan trọng. Bạn không thể nói người dùng là do dữ liệu lớn nên họ buộc phải chờ, bạn phải có phương án xử lý. Bạn sẽ thấy mình không thể cứ nâng cấp CPU, RAM, đĩa cứng,… là giải quyết được tất cả mà đôi lúc chỉ cần thực hiện một số thay đổi nhỏ trong thuật giải, cách xử lý đọc-ghi-tính toán nhưng lại có tác động đáng kể đến hiệu suất, thời gian thực thi của hệ thống. Thường thì công việc này sẽ do các chuyên gia quản trị CSDL (DBA) thực hiện dựa trên quá trình kiểm soát vận hành của hệ thống CSDL. Tuy nhiên, bạn - developer cũng nên biết qua về những kiến thức này để có thể áp dụng một số kỹ thuật tối ưu hiệu suất CSDL ngay từ lúc phát triển ứng dụng, giúp ứng dụng xây dựng được hiệu quả hơn.

1) Chọn lựa và tối ưu hóa các chỉ mục (Index) 

Tuy nhiên, bạn cũng nên lưu ý rằng nếu bảng của bạn thường xuyên có các thao tác cập nhật dữ liệu với cách lệnh INSERT, UPDATE và DELETE thì Index sẽ làm tăng thời gian thực hiện các lệnh cập nhật dữ liệu. Lúc này, bạn sẽ phải cân nhắc sao cho tối ưu, không nên có quá nhiều Index trên bảng, còn nếu dữ liệu của bạn có tính ổn định cao, ít có thao tác cập nhật và thao tác đọc là chính thì bạn có thể dễ dàng tăng số lượng index.

Quan sát kết quả thực hiện câu lệnh SQL với Excution Plan, bạn sẽ biết được ở mỗi bước hệ thống thực hiện lấy dữ liệu bằng cách sử dụng hay không sử dụng Index, thời gian thực hiện truy vấn nhiều nhất là ở bước nào để từ đó có cải tiến Index cho phù hợp.

2) Tránh các truy vấn tương quan (correlated sub query)

Truy vấn tương quan là dạng truy vấn con trong đó có sử dụng các giá trị từ các truy vấn cha. Ví dụ như: 

Đây là loại truy vấn có xu hướng chạy từng dòng một, tức là cứ mỗi hàng được trả về bởi các truy vấn bên ngoài và truy vấn con sẽ thực hiện, và do đó làm giảm hiệu suất truy vấn SQL. Có nhiều developer lựa chọn cách viết này vì dễ viết.

Bạn sẽ thấy truy vấn con được gọi nhiều lần, tương ứng với số dòng kết quả của truy vấn cha và đó là lý do mà tốc độ thực hiện truy vấn khá chậm. Bạn có thể hoàn toàn có cách viết khác thông qua  JOIN cho kết quả đúng như vậy nhưng hiệu suất được cải tiến lên rất nhiều.

Lúc này, bảng Cong_ty chỉ phải duyệt một lần, hiệu quả hơn rất nhiều, đặc biệt khi bạn thực hiện truy vấn trên bảng có dữ liệu lớn. 

3) Chỉ chọn các cột dữ liệu cần thiết

Một lời khuyên trong tối ưu hóa SQL đáng lưu ý nữa là bạn nên tránh lấy hết các cột của bảng với cú pháp SELECT *. Bạn nên xác định rõ các cột nào bạn cần, chỉ lấy những dữ liệu nào bạn cần vì dữ liệu chính là tài nguyên, là bộ nhớ, là dung lượng trên đường truyền,… Bạn sẽ mất thời gian nếu lấy quá nhiều dữ liệu mà thật sự không cần thiết. Bạn sẽ thấy chẳng có vấn đề gì nếu là dữ liệu ít, nhưng hãy xem xét một bảng với hàng trăm cột và hàng triệu dòng dữ liệu. Bạn có thật sự cần hết tất cả với câu lệnh SELECT *. Đó thật sự là một sự lãng phí lớn!

4) Nên sử dụng EXISTS() thay vì COUNT(*) để kiểm tra truy vấn có trả về dữ liệu không?

Kỹ thuật tối ưu hóa SQL này liên quan đến việc sử dụng EXISTS(). Nếu bạn muốn kiểm tra xem kết quả truy vấn có dữ liệu theo điều kiện nào đó không, bạn nên sử dụng EXISTS() thay cho COUNT(*). Lý do là vì khi bạn dùng COUNT(*), toàn bộ bảng dữ liệu sẽ được quét và đếm tất cả các dòng thỏa điều kiện bạn đưa vào. Trong khi đó, lệnh EXISTS() sẽ thoát ngay khi nó thấy kết quả thỏa điều kiện. Như vậy, rõ ràng sử dụng lệnh EXISTS() sẽ hiệu quả hơn nhiều so với COUNT(*) mà nó còn giúp cho mã lệnh của bạn cũng rõ ràng, dễ hiểu hơn.

5) Tránh dùng cursor khi cần xử lý từng dòng dữ liệu 

Dữ liệu SQL trả về thường là tập hợp dữ liệu, gồm dòng và cột. Tuy nhiên, trong một số tình huống chúng ta cần xử lý dữ liệu từng dòng như trong bài toán xử lý số liệu nhập-xuất-tồn kho của hàng hóa (hàng có nhập – không xuất, có xuất – không nhập, hàng có tồn đầu kỳ nhưng không có nhập xuất trong kỳ) , bài toán tính đạt/không đạt trên phiếu điểm của sinh viên (tương ứng với mỗi môn học là quy tắc khác nhau),… Trong trường hợp đó, thông thường chúng ta có thể lựa chọn kiểu cursor để duyệt và xử lý từng dòng dữ liệu. Bạn sẽ thấy trên CSDL dữ liệu nhỏ, cùng một thời điểm không có nhiều người dùng cùng cập nhật trên bảng thì cursor làm rất tốt các xử lý này. Nhưng mọi chuyện sẽ khác khi dữ liệu đủ lớn và cần xử lý khóa tranh chấp vì lúc này cursor sẽ khóa dòng dữ liệu cho đến khi dòng đó được duyệt và xử lý xong. Ngoài ra, trong quá trình xử lý của cursor nếu có một xử lý khác muốn cập nhật bảng dữ liệu nguồn của cursor thì hệ thống sẽ báo lỗi.

Có nhiều cách để bạn có thể dùng thay thế cho cursor như dùng bảng tạm, kết hợp truy vấn UNION và truy vấn con, sử dụng truy vấn CASE,… Nhưng thực tế qua nhiều dự án cho thấy, chúng tôi nhận thấy bạn hoàn toàn có thể sử dụng bảng tạm thay thế cho cursor và đặc biệt bảng tạm giúp cho tốc độ xử lý được cải tiến đáng kể, đặc biệt khi làm việc trên CSDL đủ lớn.

Gửi bạn…

Chắc hẳn bạn đã quen với các bài tập CSDL ở trường, cũng đã từng có cảm giác vui vui sau một hồi loay hoay với các bài tập truy vấn SQL đã  thấy kết quả thực hiện đúng. Tuy nhiên, trong thực tế, giải quyết được vấn đề đúng vẫn chưa đủ mà còn cần phải tối ưu hơn về mặt tài nguyên, đặc biệt là tối ưu về tốc độ xử lý. Càng làm nhiều bạn sẽ càng thấy cùng một truy vấn SQL nhưng bạn sẽ có nhiều cách giải quyết khác nhau và nhiệm vụ của bạn là làm sao cho tối ưu nhất. Bạn sẽ hiểu rõ hơn những nguyên tắc nền tảng của SQL qua mỗi lần trải nghiệm và rồi bạn sẽ phát hiện ra thêm được nhiều điều thú vị về SQL, SQL Server để làm tốt hơn công việc của mình.

Phần 2: Phím tắt trong SQL Server

SQL server Management Studio hỗ trợ rất nhiều phím tắt, sau đây là một số phím tắt đặc biệt.

1. Mở cửa sổ query mới (Ctrl + N)

Tổ hợp phím này sẽ giúp bạn mở cửa sổ mới một cách nhanh chóng

2. Hiển thị các cửa sổ SQL đang làm việc (Ctrl + Tab)

Tổ hợp phím này sẽ hỗ trợ cho việc hiển thị tất cả các cửa sổ đang làm việc.

3. Ẩn hiện kết quả câu lệnh (Ctrl + R)

Khi muốn ẩn kết quả câu query thì bạn chỉ cần nhần tổ hợp phím Ctrl + R

4. Chạy câu lệnh SQL đang được chọn (Ctrl + E)

Tổ hợp phím Ctrl + E sẽ giúp bạn chạy câu query đang được chọn

5. Hủy bỏ câu query  đang chạy (Alt + Break or Alt + Scroll Lock)

Trường hợp câu query của bạn đang chạy mất quá nhiều thời gian, bạn có thể sử dụng tổ hợp phím này để hủy bỏ nhanh chóng.

6. Chuyển câu lệnh đang chọn thành chữ hoa, chữ thường (Ctrl + Shift + U, Ctrl + Shift + L)

7. Hiển thị quá trình chạy câu query (Ctrl + L)

Hiển thị quá trình chạy câu query  với tổ hợp Ctrl + L

8. Hiển thị kết quả cùng quá trình chạy câu query (Ctrl + M)

9. Gợi ý những câu lệnh, bảng ... (Ctrl + Space, Tab)

10. Chuyển nhanh đến dòng (Ctrl + G)

Bạn có thể chuyển nhanh đến dòng code bao nhiêu băng cách dùng tổ hợp Ctrl + G

11. Comment và bỏ comment dòng lệnh ( Ctrl + K & Ctrl + C; Ctrl + K & Ctrl + U)

Phần 3: Cách sử dụng SQL Server Agent

http://phuongnguyentrung.blogspot.com/2015/06/sql-tips-create-sql-job-to-backup.html

Phần 4: Tìm thông tin về parameters trong Stored Procedure

EXEC SP_HELP 'test_procedure'

SELECT parameter_name, data_type, ordinal_position FROM information_schema.parameters WHERE specific_name='test_procedure'

Phần 5: Lưu ý đối với các câu lệnh sử dụng thủ tục sp_executesql:

Phải khai báo biến kiểu NVarchar để tránh lỗi

Câu lệnh sai:

DECLARE @SQL VARCHAR(100)
SET @SQL = 'SELECT TOP 1 * FROM sys.tables'
EXECUTE sp_executesql @SQL

Câu lệnh đúng:

DECLARE @SQL NVARCHAR(100)
SET @SQL = 'SELECT TOP 1 * FROM sys.tables'
EXECUTE sp_executesql @SQL