[SQL Server] Tối ưu hóa câu lệnh trong SQL Server

Tối ưu hóa cơ sở dữ liệu là điều rất khó khăn, nó sẽ là thử thách cho các bạn làm việc với một cơ sở dữ liệu có quy mô đủ lớn. Lúc này, bài toán đặt ra là hiệu suất và thời gian thực thi câu lệnh bên trong ứng dụng là rất quan trọng. Vì khi đó bạn không thể đưa ra phương án là nâng cấp CPU, RAM hay ổ đĩa nó là quá tốn kém nếu không thật sự cần thiết. Vì vậy chúng ta nên có một số lưu ý khi thực hiện viết câu lệnh thực thi bên trong SQL Server để cải thiện hiệu suất và thời gian truy vấn.

Chọn lựa và tối ưu chỉ mục Index

Chỉ mục (Index) là bảng tra cứu đặc biệt mà Database Search Engine có thể sử dụng để tăng thời gian và hiệu suất truy vấn dữ liệu. Hiểu đơn giản, một chỉ mục là một con trỏ tới dữ liệu trong một bảng. Chỉ mục có thể được hiểu tương tự như một chỉ mục trong Mục lục của cuốn sách.
Index giúp tăng tốc các truy vấn SELECT và các mệnh đề WHERE, nhưng nó làm chậm việc UPDATE và INSERT dữ liệu và bảng. Các chỉ mục có thể được tạo hoặc xóa mà không ảnh hưởng tới dữ liệu.Việc sử dụng Index sẽ phù hợp với những DB có lượng dữ liệu lớn và ít có thay đổi (vd: như thông tin khách hàng của ngân hàng).
Tạo Index như sau :

CREATE INDEX index_name ON table_name;

Xóa Index:

DROP INDEX index_name;

Chọn lựa các thông tin cần thiết

Thói quen của chúng ta thường là thực hiện một câu query all dữ liệu mà không thực hiện lọc ra những dữ liệu thực sực cần thiết cho công việc. Điều này cũng làm tăng đáng kể thời gian truy vấn dữ liệu vì nó chiếm dụng nhiều bộ nhớ hơn, dữ liệu truyền tải từ server tới client cũng lớn hơn. Vì vậy hãy tạo thói quen phân tích và lọc ra những dữ liệu cần thiết trước khi thực hiện một câu truy vấn.

SELECT * FROM table_1 LEFTJOIN table_2 WHERE table_1.id = table_2.gid;

Hãy chuyển câu query trên về như sau:

SELECT table_1.id,table_2.username,table_2.lucky FROM table_1 LEFTJOIN table_2 WHERE table_1.id = table_2.gid;

Tối ưu các toán tử trong điều kiện where

Toán tử phủ định 

“IS NULL”, “!=”, “!>”, “!<“, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”,

Index không thể thực hiện với toán tử phủ định do đó các toán tử này sẽ làm chậm câu lệnh hãy hạn chế sử dụng.

Toán tử so sánh 2 lần

SELECT userid, username FROM user WHERE user_amount <=3000

câu lệnh bên trên sẽ khiến SQL phải so sánh 2 lần :
user_amount< 3000 OR user_amount=3000 do đó làm chậm truy vấn.Hãy dùng câu lệnh dưới đây cho tình huống tương tự.

SELECT userid, username FROM user WHERE user_amount < 3001

Hạn chế sử dụng function lên column

SELECT member_number, first_name, last_name

FROM members

WHERE DATEDIFF(yy,dateofbirth,GETDATE()) > 21

Việc sử dụng hàm DATEDIFF sẽ khiến column dateofbirth không thể đánh index được nữa. Vì vậy hãy tối ưu nó như sau:

SELECT member_number, first_name, last_name

FROM members

WHERE dateofbirth < DATEADD(yy,-21,GETDATE())

Tránh sử dụng hàm lên các column mà hãy chuyển đổi sử dụng nó lên dữ liệu không đánh index.

Loại bỏ những thao tác thừa

Với việc loại bỏ thao tác thừa này SQL có cung cấp cho chúng ta một công cụ hỗ trợ khá đắc lực đó là Execution Plan. Chúng ta sẽ kích hoạt Execution Plan trước khi chạy câu query bằng phím tắt Ctrl+M trong màn hình SQL Server Management Studio.

Trên đây là ví dụ của Execution Plan khi thực hiện câu lệnh Union và Union All. Chúng ta thấy cái cost để thực hiện câu query là cao hơn nhiều do có phát sinh thêm thao tác Sort (Distinct Sort). Trong trường hợp này việc sử dụng Union All sẽ cho tốc độ truy vấn nhanh hơn và performent cao hơn nhiều.

Xác định sự tồn tại của record

Sử dụng IF EXISTS thay cho COUNT(*) hoặc COUNT(DISTINCT).

vì hàm count sẽ phải quét toàn bộ bảng để đếm số record trong khi hàm if exists sẽ chỉ check có tồn tại 1 record trong bảng là thoát khỏi điều kiện sẽ nhanh hơn nhiều.

Tránh việc sử dụng Cursor để xử lý dữ liệu

Việc sử dụng cursor để duyệt qua từng record trong việc xử lý dữ liệu sẽ rất ổn nếu như đó là cơ sở dữ liệu với quy mô nhỏ. Nhưng khi làm việc với CSDL quy mô đủ lơn việc sử dụng cursor sẽ dẫn đến nhiều rủi ro cho người dùng. Vì khi thực hiện xử lý dữ liệu cursor sẽ khóa row lại cho đến khi nó được xử lý xong và trong một trường hợp rủi ro nào đó row dữ liệu bị khóa đó cần được update dữ liệu vào đúng lúc nó còn chưa được giải phóng điều này sẽ gây ra lỗi trầm trọng. Vì vậy việc sử dụng bảng tạm (temp) để thay thế cho cursor là việc chúng ta hoàn toàn nên làm để tránh lỗi cho hệ thống và cải thiện được hiệu suất của câu lệnh. Đặc biệt khi làm việc với CSDL đủ lớn.

Sử dụng SQL Store Procedurce

Đối với các thao tác được thực hiện 1 cách thường xuyên và có xử lý phức tạp chúng ta nên sử dụng SQL procedure(SP) với nhiều lợi ích như dưới đây.

1. Giảm lượng dữ liệu truyền đến Server
   SP được lưu sẵn ở phía server do đó không cần phải gửi cả câu lệnh SQL dài tới server mà chỉ cần gửi tham số.
2. SP được biên dịch ngay ở lần đầu chạy, những lần sau chạy SP sẽ sử dụng lại file đã biên dịch trước đó nên tốc độ sẽ nhanh hơn.
3. Khi sử dụng SP trong source có thể dùng vòng for để gọi nhiều câu lệnh SQL gửi lên server điều này giúp tái sử dụng source.

Trên đấy là một số lưu ý và chia sẻ về việc tối ưu hóa một câu lệnh SQL.