Cách tối ưu câu truy vấn SQL trong lập trình

Một số kinh nghiệm trong thực tế để tối ưu câu truy vấn SQL

Những câu truy vấn chậm có thể phát sinh từ rất nhiều nguyên nhân bao gồm:

· Không/thiếu sử dụng các lợi ích của Indexes.

· Không/thiếu tận dụng được I/O striping.

· Các thống kê lỗi thời hoặc thiếu các thống kê hữu ích.

· Thiếu bộ nhớ vật lý.

· Kết nối mạng chậm.

· Các câu truy vấn Transact-SQL chuyển số lượng dữ liệu lớn từ server đến client.

· Locks or deadlocks bị cấm.

· Thực hiện các câu truy vấn OLTP and OLAP trên cùng 1 máy server.

· Trả về các dữ liệu không cần thiết.

· Các câu truy vấn được viết nghèo nàn.

– Bạn nên luôn luôn thêm vào mệnh đề WHERE trong câu lệnh SELECT để thu hẹp số dòng trả về. Nếu bạn không sử dụng mệnh đề WHERE trong câu SELECT thì SQL Server sẽ thực hiện việc quét toàn table và trả về mọi bản ghi trong table. Điều này sẽ gây lãng phí tài nguyên I/O khi trả về các dòng không cần thiết.

– Bạn không phải lo lắng khi có sử dụng in-line hay block comments trong code vì chúng sẽ không ảnh hưởng tới việc xử lý hiệu năng trong ứng dụng của bạn. Ngoài ra, những block comments còn làm rỏ hơn các đoạn code của bạn.

– Nếu có thể, bạn nên tránh sử dụng SQL Server Cursors. Chúng thường sử dụng nhiều tài nguyên SQL Server, giảm hiệu năng và tính khả mở của ứng dụng bạn. Nếu bạn buộc phải thực hiện các thao tác hàng theo hàng thì hãy cố tìm cách giải quyết khác để thực hiện. Một số lựa chọn thực hiện tác vụ ở client là sử dụng các bảng trong tempdb, các bảng dẫn xuất, câu truy vấn có tương quan với nhau, hay sử dụng lệnh CASE. Thường thì tất cả kỹ thuật non-cursor đều có thể sử dụng để thực hiện cùng các tác vụ như SQL Server cursor.

– Để xác định những câu truy vấn chậm chạp bạn có thể sử dụng vết ‘TSQL by Duration’ trong SQL Server Profiler để theo dõi thời gian xử lý của các câu truy vấn.

– Bạn hãy cận thận cân nhắc có cần mệnh đề DISTINCT trong câu truy vấn hay không. Mệnh đề DISTINCT làm chậm quá trình truy vấn dữ liệu. Một số người thường đưa DISTINCT vào câu truy vấn ngay cả khi nó không cần thiết. Đây là thói quen xấu cần nên bỏ.

– Khi bạn dùng câu lệnh UNION, bạn hãy nhớ rằng ngầm định nó thực hiện tương đương như kết quả từ câu SELECT DISTINCT. Do đó nếu bạn biết chắc rằng chẳng có 1 hàng nào trùng lắp được tạo ra từ kết quả của UNION thì bạn nên sử dụng câu lệnh UNION ALL thay thế.

– Trong câu truy vấn bạn không nên trả về các cột không cần thiết, như câu SELECT *, nó sẽ trả về mọi cột trong bảng và ngoài ra nó còn cản trở việc sử dụng indexes.

– Nếu người sử dụng thực hiện nhiều câu truy vấn đặc biệt trên SQL Server và bạn nhận thấy rằng các câu truy vấn được viết nghèo nàn này chiếm quá nhiều tài nguyên, bạn nên cân nhắc sử dụng tuỳ chọn cấu hình ‘query governor cost limit’ để giới hạn thời gian câu truy vấn thực hiện. Bạn coá thể gán option này với giá trị là seconds và có 2 cách để xác định. Một là bạn thay đổi nó ở cấp độ server bằng việc sử dụng sp_configure “query governor cost limit’” hoặc có thể gán nó ở cấp độ kết nối (chỉ có kết nối này có ảnh hưởng) bằng cách sử dụng lệnh SET QUERY_GOVERNOR_COST_LIMIT.

– Nếu trong ứng dụng cho phép người sử dụng chạy các câu truy vấn nhưng bạn không thể ngăn cản được người sử dụng lấy về hàng trăm, thậm chí hàng ngàn records mà họ không cần thì bạn hãy xem xét việc sử dụng toán tử TOP trong câu lệnh SELECT, chẳng hạn SELECT TOP 100 fullname, address FROM Customers. Ngoài việc sử dụng TOP, SQL Server còn hổ trợ lệnh SET ROWCOUNT cũng nhằm hạn chế số records truy xuất từ câu SELECT nhưng lệnh SET ROWCOUNT này không hiệu quả bằng TOP.

– Bạn hãy cố gắng tránh sử dụng các toán tử sau trong mệnh đề WHERE: “IS NULL”, “<>”, “!=”, “!>”, “!<“, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%abc'”. Vì những toán tử này nó không sử dụng đặc tính index mà thay vì thế nó sẽ dò tìm toàn bảng gây ảnh hưởng đến tốc độ của câu truy vấn. Ví dụ:

SELECT lname, fname, address FROM Customers WHERE fname LIKE ‘%V%’

Thay vì thế, bạn nên sử dụng

SELECT lname, fname, address FROM Customers WHERE fname LIKE ‘V%’

– Nếu bạn có sự chọn lựa giữa IN và EXISTS trong câu truy vấn, bạn hãy chọn EXISTS là tốt nhất. Tương tự như IN và BETWEEN, hãy chọn BETWEEN.

– Nếu bạn nhận thấy rằng SQL Server sử dụng Table Scan thay vì INDEX SEEK trong câu truy vấn có IN hay OR, ngay cả cột tìm kiếm đó đã tạo index. Bạn nên sử dụng Index hint để bắt buộc trình tối ưu hoá truy vấn sử dụng index, như ví dụ sau:

SELECT lname, fname, address FROM Customers WHERE CusID in (1, 5, 10)

Câu truy vấn sau sẽ chạy nhanh hơn câu trước vì ép sử dụng index

SELECT lname, fname, address FROM Customers (INDEX = IX_CusID) WHERE CusID in (1, 5, 10)

– Bạn nên tránh sử dụng Functions hoạt động trực tiếp vào cột vì index sẽ không được sử dụng như trong ví dụ sau:

SELECT member_number, first_name, last_name
FROM members
WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21

Câu truy vấn sau sẽ sử dụng index

SELECT member_number, first_name, last_name
FROM members
WHERE dateofbirth < DATEADD(yy,-21,GETDATE())

– Đừng cài đặt các đặt tính ràng buộc dữ liệu dư thừa trong database. Chẳng hạn, nếu bạn cài đặt các ràng buộc tham chiếu bằng khoá chính, khoá ngoại thì bạn đừng thêm vào các triggers có cùng mục đích. Tương tự như vậy, đối với constraints và defaults hay constraints và rules.

– Nếu ứng dụng của bạn cần thêm giá trị nhị phân vào cột dữ liệu hình ảnh (rất ít ai thiết kế database lại lưu trữ dữ liệu hình ảnh). Bạn hãy sử dụng Stored Procedure để thay thế cho câu lệnh INSERT được nhúng trong ứng dụng. Có lựa chọn lựa này vì đầu tiên ứng dụng phải chuyển giá trị nhị phân sang chuổi ký tự trưới khi nó gởi tới Server. Và khi Server nhận được chuổi ký tự nó lại chuyển ngược lại dạng nhị phân. Hãy sử dụng SP để tránh vấn đề này.

– Nếu có thể, bạn nên tránh sử dụng các hàm chuyển đổi kiểu dữ liệu trong mệnh đề WHERE.

– Bạn nên đóng gói mã T_SQL của bạn trong 1 transaction nếu bạn định sửa đổi database để bảo đảm tính nhất quán của dữ liệu. Còn đối với code chỉ cho việc reports thì đừng nên đóng gói thành 1 transaction vì khi mở và đóng 1 transaction sẽ tốn thêm 1 ít chi phí.

– Bạn không nên sử dụng các optimizer hint trong câu truy vấn vì thường thì rất khó đoán trước được Query Optimizer làm những gì. Optimizer hints là những từ khóa đặc biệt dùng để ép Query Optimizer chạy theo các từ khoá này.

– Trong các câu truy vấn có 1/nhiều OR bạn có thể viết lại bằng cách kết hợp UNION ALL để tăng tốc độ truy vấn. Ví dụ:

SELECT employeeID, firstname, lastname
FROM Employees
WHERE dept = 'prod' or city = 'Orlando' or division = 'food'

Câu truy vấn sau cùng mục đích nhưng có tốc độ nhanh hơn

SELECT employeeID, firstname, lastname FROM Employees WHERE dept = 'prod'
UNION ALL
SELECT employeeID, firstname, lastname FROM Employees WHERE city = 'Orlando'
UNION ALL
SELECT employeeID, firstname, lastname FROM Employees WHERE division = 'food'

– Trong khi Views rất thuận tiện trong việc hạn chế người sử dụng xem dữ liệu nhưng về vấn đề hiêu năng, sử dụng Views không tốt cho lắm. Vì Views không giống như SP, nó không được tối ưu hoá trước và ngay cùng câu lệnh SELECT bỏ trong View thì chạy chậm hơn câu lệnh đó chạy trong Query Analyzer. SQL Server không cấm việc sử dụng các Views lồng nhau nhưng bạn nên tránh sử dụng các Views lồng nhau.

– Đừng sử dụng DISTINCT và ORDER BY trong câu lệnh SELECT trừ khi thật sự cần thiết đến chúng vì chúng làm tăng chi phí xử lý câu truy vấn.

– Nếu câu SELECT của bạn có sử dụng HAVING bạn nên có thêm việc sử dụng WHERE để hạn chế những hàng không cần thiết. Trình tự thực hiện câu truy vấn có cú pháp SELECT … WHERE … GROUP BY … HAVING …. Đầu tiên WHERE được sử dụng để chọn ra những dòng thích hợp mà cần phải group. Kế đến GROUP BY chia các rows thành các tập records được nhóm và cuối cùng HAVING mới được sử dụng.

– Nếu ứng dụng của bạn sử dụng nhiều ký tự đại diện (wildcard) như % để tìm các chuổi dữ liệu, bạn nên cân nhắc sử dụng tuỳ chọn SQL Server full-text.

– Trong SQL Server 2000 cung cấp 1 kiểu dữ liệu mới gọi là ‘table’. Mục đích của nó là lưu trữ tạm thời tập các records. Nếu có thể, bạn hãy sử dụng biến table thay vì sử dụng bảng tạm.

– Nếu bạn cần xác định sự tồn tại của records trong bảng, đừng nên sử dụng SELECT COUNT(*) vì nó không hiệu quả và lãng phí tài nguyên thay vì thế bạn nên sử dụng IF EXISTS thì hiệu quả hơn. Ví dụ:

IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')

Hãy sử dụng IF EXISTS sẽ nhanh hơn:

IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

– Nếu được hãy sử dụng SP thay vì User defined Function trong 1 tập dữ liệu lớn mà bạn muốn trả về. Bởi vì 1 User defined Function phức tạp thì chiếm nhiều chi phí hơn là 1 SP có cùng chức năng.

– Đôi khi bạn muốn tìm phiên bản service pack mà bạn đang chạy trên SQL Server, bạn hãy sử dụng lệnh sau trong ISQL/W hay Query Analyzer:

SELECT @@Version

– Hãy sử dụng bảng dẫn xuất (derived table) để thay thế bảng tạm hay bảng chính. Ví dụ sau được copy từ Books Online:

SELECT Ord.OrderID, Ord.OrderDate, maxUnitPrice
FROM Northwind.dbo.Orders AS Ord INNER JOIN
( SELECT orderID,
MAX(UnitPrice) AS maxUnitPrice
FROM Northwind.dbo.[Order Details]
GROUP BY OrderID ) AS OrdDet ON ordDet.orderID = Ord.orderID
ORDER BY Ord.OrderID DESC, Ord.OrderDate, maxUnitPrice

Câu truy vấn màu đỏ được gọi là derived table. Derived table chiếm không gian ít hơn bảng nguồn hay bảng tạm bởi vì nó đã được lọc bớt đi. Nếu bạn sử dụng bảng nguồn bằng INNER JOIN sẽ chậm hơn câu trên rất nhiều.