Chưa phân loại

SQL Optimization Tips and Index Optimization

Quy tắc chung

– Bảng (table) phải có khóa chính (Primary Key).
– Bảng (table) phải có ít nhất 01 clustered index.
– Bảng (table) phải có số lượng non-clustered index phù hợp.
– Non-clustered index phải được tạo trên các cột (column) của bảng (table) dựa vào nhu cầu truy vấn.
– Dựa theo sự sắp xếp thứ tự như sau khi có bất kỳ index được tạo: a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
– Không nên dùng Views thay cho bảng (table) gốc.
– Triggers không nên sử dụng nếu không cần thiết, nên nhập những xử lý từ trigger vào trong thủ tục (stored procedure).
– Gỡ bỏ những câu lệnh query trực tiếp và thay bằng thủ tục (stored procedure).
– Phải có ít nhất 30% không gian đĩa cứng trên phân vùng chứa database.
– Nếu có thể hãy chuyển UDF (user defined function) sang SP (stored procedure) .
– Chỉ SELECT những cột cần thiết, không nên SELECT *.
– Gỡ bỏ các joins từ các bảng (table) không cần thiết.
– Hạn chế sử dụng con trỏ (cursor)
– Đảm bảo phần cứng đáp ứng nhu cầu của hệ thống.

Tối ưu Stored Procedure

– Sử dụng câu lệnh SET NOCOUNT ON: với mỗi câu lệnh SELECT, SQL Server trả về thông tin thông báo số record bị ảnh hưởng bởi câu lệnh. Thông tin này hữu ích cho việc debug, ngoài ra thì ko. Bằng cách thiết lập SET NOCOUNT ON, chúng ta bỏ đi chức năng trả về các câu thông báo như vậy. Cho các stored procedures chứa các câu lệnh trong vòng lặp, việc thiết lập SET NOCOUNT to ON làm tăng việc thực thi bởi nó sẽ giảm luồng traffic không cần thiết.

 CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
-- Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

– Sử dụng schema name với object name: Tên object sẽ đầy đủ nếu nó đi cùng với schema name. Schema name nên được dùng với stored procedure name và với tất cả các đối tượng ở trong stored procedure. Điều này giúp quá trình biên dịch sẽ tìm thấy đối tượng trực tiếp thay vì tìm kiếm các đối tượng trên các schema trước khi quyết định đưa vào cached. Việc tìm kiếm và quyết định sử dụng schema cho một đối tượng sẽ làm stored procedure bị lock và giảm độ thực thi của stored procedure. Do vậy, nên luôn dùng tên đầy đủ cho đối tượng trong các stored procedure.

 SELECT * FROM dbo.MyTable – Preferred method
-- Instead of
SELECT * FROM MyTable – Avoid this method
-- And finally call the stored procedure with qualified name like:
EXEC dbo.MyProc -- Preferred method
-- Instead of
EXEC MyProc -- Avoid this method

– Không dùng tiếp đầu ngữ “sp_” trong tên stored procedure: Nếu một tên stored procedure bắt đầu với “sp_”, SQL Server đầu tiên sẽ tìm kiếm trong master database và sau đó trong database hiện tại. Việc tìm kiếm trong master database dẫn đến sự tốn kém tài nguyên không cần thiết và có thể chạy sai nếu có stored procedure cùng tên ở trong master database/

– Dùng IF EXISTS (SELECT 1) thay vì (SELECT *): Để kiểm tra sự tồn tại của dữ liệu trong table, chúng ta dùng IF EXISTS, nó trả về kết quả là TRUE nếu tìm thấy, không quan tâm đến việc trả về một hay nhiều cột dữ liệu. Dữ liệu trả về không được dùng đến. Vì vậy, để tối giản việc sử dụng dữ liệu cũng như việc truyền dữ liệu qua môi trường mạng, chúng ta nên dùng “1″ trong câu lệnh SELECT giống như dưới đây:

 IF EXISTS (SELECT 1 FROM sysobjects WHERE name = ‘MyTable’ AND type = ‘U’)

– Dùng thủ tục sp_executesql thay vì cầu lệnh EXECUTE: Thủ tục sp_executesql hỗ trợ tham số. Vì vậy, dùng sp_executesql thay vì EXECUTE làm tăng việc tái sử dụng lại code. Execution plan của các câu lệnh động chỉ được tái sử dụng nếu mỗi và tất cả các ký tự, bao gồm hoa thường, khoảng trống, chú thích, tham số là giống nhau.

 DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET @Age = 25
SET @Query = ‘SELECT * FROM dbo.tblPerson WHERE Age = ’ + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

Nếu chúng ta thực thi lại câu lệnh trên với giá trị @Age khác, thì execution plan cho câu lệnh với tham số @Age = 25 sẽ không được tái sử dụng. Tuy nhiên, nếu chúng ta viết lại như sau:

 DECLARE @Query NVARCHAR(100)
SET @Query = N’SELECT * FROM dbo.tblPerson WHERE Age = @Age’
EXECUTE sp_executesql @Query, N’@Age int’, @Age = 25

thì compiled plan sẽ sử dụng lại câu lệnh cả cho giá trị @Age khác. Việc tái sử dụng một compiled plan sẽ tăng hiệu quả thực thi của câu truy vấn

– Tránh dùng cursor khi có thể: Cursor dùng rất nhiều tài nguyên để xử lý các dữ liệu trong tập dữ liệu và nó làm giảm độ thực thi của thủ tục. Nếu chúng ta cần xử lý từ ng dữ liệu trong vòng lặp, chúng ta nên dùng câu lệnh WHILE. Bất cứ khi nào được, chúng ta nên thay thế việc dùng cursor bằng phương thức SET (gán giá trị). Bởi vì SQL Server được thiết kế và tối ưu để thực thi các câu lệnh SET rất nhanh. Ngoài ra, bạn cũng chú ý là cursor cũng là một loại vòng lặp WHILE

– Giữ Transaction ngắn khi có thể: Một transaction sẽ ảnh hưởng đến việc blocking và deadlocking. Các lock sẽ không được kết thúc cho đến khi kết thúc transaction. Với hệ thống hiệu suất cao, shared lock cũng được dùng với transaction. Do vậy, một transaction dài có nghĩa sẽ thực hiện việc lock trong một khoảng thời gian dài, và khi lock ở một thời gian dài sẽ trở thành block, đổi khi block sẽ trở thành deadlock. Vì vậy, để thực thi nhanh và ít blocking, transaction nên giữ ngắn nếu được.

– Dùng TRY-Catch để quản lý lỗi: Trước phiên bản 2005, nếu muốn kiểm tra lỗi phải kiểm tra sau mỗi lần thực hiện câu lệnh sql. Trong SQL Server 2005, việc quản lý lỗi đã được hỗ trợ :

 BEGIN TRY
-- Your t-sql code goes here
END TRY
BEGIN CATCH
-- Your error handling code goes here
END CATCH

From http://bobiasg.com/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s