Jul 26, 2014

Các cách truy vấn để đạt hiệu quả


Một nguyên tắc bản trong SQL Server ta thể áp dụng trong rất nhiều trường hợp, đó cột cần tìm phải đứng một mìnhmột phía của biểu thức tìm kiếm, nói cách khác không hàm số hay phép tính toán nào áp dụng trên cột đó.

VD: Tìm kiếm trong bảng Part_Index RowID = 101

select Company,PartNum,ShortChar01,ShortChar02,Number01,Number03,Date01,RowID,RowKey from Part_Index

where RowID+1=101 (Câu lệnh không đạt Index)



Select Company,PartNum,ShortChar01,ShortChar02,Number01,Number03,Date01,RowID,RowKey from Part_Index

where RowID = 101 (Câu lệnh đạt Index)
VD: Tìm kiếm trong bảng Part_Index Date01 = '17/12/1987'

select Company,PartNum,ShortChar01,ShortChar02,Number01,Number03,Date01,RowID,RowKey from Part_Index

WHERE CONVERT(VARCHAR,Date01,103) = '17/12/1987'  (Câu lệnh không đạt Index)



select Company,PartNum,ShortChar01,ShortChar02,Number01,Number03,Date01,RowID,RowKey from Part_Index
WHERE Date01 between '19871216' and '19871218'  (Câu lệnh đạt Index)

VD: Tìm kiếm trong bảng Part_Index RowKey bắt đầu AC2033E7 (sẽ không tạo ra index nếu điều kiện không phải chuỗi bắt đầu like ‘%AC2033E7%’ hoặc like ‘%AC2033E7’ )


select Company,PartNum,ShortChar01,ShortChar02,Number01,RowID,Number03,RowKey from Part_Index

where substring(RowKey,1,8) = 'AC2033E7' (Câu lệnh không đạt Index)



select Company,PartNum,ShortChar01,ShortChar02,Number01,RowID,Number03,RowKey from Part_Index

where RowKey like 'AC2033E7%' (Câu lệnh đạt Index)

Giảm thiểu các câu truy vấn con (subquery) trong câu truy vấn chính

VD:

select Company,PartNum,ShortChar01,ShortChar02,Number01,RowID,Number03,RowKey from Part_Index

where

Number01 = (select MAX(Number01) from Part_Index)

and ShortChar02 = (select max(ShortChar02) from Part_Index)



select Company,PartNum,ShortChar01,ShortChar02,Number01,RowID,Number03,RowKey from Part_Index A

where exists

(

select Number01,ShortChar02 from

(

select top 1 Number01 ,Max(ShortChar02) ShortChar2 from Part_Index group by Number01 order by Number01 DESC

) T

where 

A.Number01=T.Number01
and A.ShortChar02=T.ShortChar2

Sử dụng điều kiện EXISTS thay cho IN (Thường thì sử dụng điều kiện IN sẽ làm chậm câu truy vấn hơn EXISTS)

VD:

select Company,PartNum,ShortChar01,ShortChar02,Number01,RowID,Number03,RowKey from Part_Index A

where PartNum in (select PartNum from Part_Index T where T.RowID between 4000 and 5000)



select Company,PartNum,ShortChar01,ShortChar02,Number01,RowID,Number03,RowKey from Part_Index A

where exists (select 'Lam' from Part_Index T where T.RowID between 4000 and 5000 and A.PartNum=T.PartNum)



Note: NOT IN không quan tâm tới giá trị NULL còn NOT EXIST thì .

Nên dùng UNION ALL thay cho UNION. Union phải tốn thời gian loại bỏ các bản ghi trùng lặp trước khi trả lại kết quả.

Xem xét lọc dữ liệu kiểu số chỉ cần khác thôi hay > hoặc <

VD:

SELECT id, first_name, age FROM student_details WHERE age > 10; 

Thay cho: 

SELECT id, first_name, age FROM student_details WHERE age != 10


 Sử dụng Like thay cho việc dùng 1 hàm để lọc dữ liệu

VD:

SELECT id, first_name, age

FROM student_details

WHERE first_name LIKE 'Chan%'; 

Thay cho: 

SELECT id, first_name, age

FROM student_details

WHERE SUBSTR(first_name,1,3) = 'Cha';
Nên sử dụng BETWEEN AND thay cho >= <=

VD:

SELECT product_id, product_name

FROM product

WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)



Thay cho: 


SELECT product_id, product_name

FROM product

WHERE unit_price >= MAX(unit_price)

and unit_price <= MIN(unit_price)


Nên sử dụng inner join thay cho subquery

VD: 

select top 100  A.Company,A.PartNum,A.ShortChar01,A.ShortChar02,A.Number01,A.RowID,A.Number03,B.RowKey

from Part_Index A inner join Part_NoIndex B ON A.PartNum=B.PartNum


Thay cho:


select top 100  Company,PartNum,ShortChar01,ShortChar02,Number01,RowID,Number03

,(select RowKey from Part_NoIndex B where B.PartNum=A.PartNum) RowKey

from Part_Index A

 
 
Một câu truy vấn điều kiện OR ta thể tách ra thành Union ALL để tăng hiệu suất khi các điều kiện lọc chứa Index.

VD: 

select Company,PartNum,ShortChar01,ShortChar02,Number01,Number03,Date01,RowID,RowKey from Part_Index where PartNum like 'Part-4072%'

Union all

select Company,PartNum,ShortChar01,ShortChar02,Number01,Number03,Date01,RowID,RowKey from Part_Index where ShortChar01 like 'ShortChar-4072%'

  Thay cho:

select Company,PartNum,ShortChar01,ShortChar02,Number01,Number03,Date01,RowID,RowKey from Part_Index

where PartNum like 'Part-4072%' OR ShortChar01 like 'ShortChar-4072%'

Hạn chế sử dụng con trỏ (CURSOR) càng nhiều càng tốt, chỉ sử dụng CURSOR khi không còn phương án nào khác.

Nguyên nhân:

CURSOR  tiêu hao bộ nhớ CPU của Server.

-  Khi dùng CURSOR để Update (đặc biệt lại Update với một số lượng lớn bản ghi) thì toàn bảng sẽ bị khóa lại gây tổn khó khăn trong việc truy suất dữ liệu của những người dùng khác, có khi làm treo hệ thống.


Giải pháp:

Ta có thể dùng bảng tạm + While Loop thay cho CURSOR khi câu truy vấn có thể đạt tới Index seek.
Sử dụng SP_EXECUTESQL theo cách tham số hoá cho câu lệnh

(Đây phương pháp thực hiện sql động được khuyến cáo của Microsoft)

  VD: SET @SqlStr = N'select * from Part_Index where RowID>@ID and   Number01>@Number01'

EXEC SP_EXECUTESQL @SqlStr, N'@ID int, @Number01 int', @RowID,@Number01



 Sp_ExecuteSql khắc phục được hai nhược điểm trên của EXEC() với điều kiện viết đúng cách.

 




 

0 comments:

Post a Comment

Nam Le © 2014 - Designed by Templateism.com, Distributed By Templatelib