[SQL SERVER][Performance]查詢調校--計算的資料行
網友問題，由於該網友一定要使用substring函數，
這裡我用SQL2008大概模擬整個調校過程。
調校後，經過時間改善約7.8倍、邏輯讀取改善約6.8倍、整體成本改善約5.3倍。
原本查詢statement
select t1.BILL_NO,t1.POLICY_NO,t2.ACCOUNT_BANK,t1.BL_DATE from dbo.bill_test t1
left join dbo.blxx_test t2
on t1.BILL_NO=t2.BILL_NO
where t1.BL_DATE between '20090101' and '20091010'
and SUBSTRING(t1.BILL_NO,4,7) in
(
select SUBSTRING(BILL_NO,4,7)
from dbo.blxx_test
where METHOD='B'
group by BILL_NO
)
Go
資料表筆數
已存在的索引
CREATE NONCLUSTERED INDEX nidx_1
ON dbo.bill_test (BILL_NO asc)
include (POLICY_NO,BL_DATE)
go
CREATE NONCLUSTERED INDEX nidx_2
ON dbo.bill_test (BL_DATE asc)
include (BILL_NO,POLICY_NO)
go
CREATE NONCLUSTERED INDEX nidx_1
ON dbo.blxx_test (BILL_NO asc)
include (METHOD,ACCOUNT_BANK)
go
drop index nidx_2 on dbo.blxx_test
CREATE NONCLUSTERED INDEX nidx_2
ON dbo.blxx_test (METHOD asc)
include (BILL_NO_P,ACCOUNT_BANK)
go
原本查詢經過時間和邏輯讀取
執行計畫(整體成本:17.5885)
可以看到執行計畫相當複雜。
使用計算的資料行準備改善查詢效能
新增計算的資料行
ALTER TABLE dbo.bill_test ADD
BILL_NO_P AS (SUBSTRING(BILL_NO,4,7)) PERSISTED
go
ALTER TABLE dbo.blxx_test ADD
BILL_NO_P AS (SUBSTRING(BILL_NO,4,7)) PERSISTED
Go
建立新索引
CREATE NONCLUSTERED INDEX nidx_3
ON dbo.bill_test (BILL_NO_P asc)
INCLUDE (BILL_NO,POLICY_NO,BL_DATE)
Go
CREATE NONCLUSTERED INDEX nidx_3
ON dbo.blxx_test (BILL_NO_P asc)
INCLUDE (BILL_NO,METHOD,ACCOUNT_BANK)
go
改寫原本查詢Statement
select t1.BILL_NO,t1.POLICY_NO,t2.ACCOUNT_BANK,t1.BL_DATE
from dbo.bill_test t1
left join dbo.blxx_test t2
on t1.BILL_NO=t2.BILL_NO
where t1.BL_DATE between '20090101' and '20091010'
and t1.BILL_NO_P in
(
select BILL_NO_P
from dbo.blxx_test
where METHOD='B'
group by BILL_NO_P
)
go
清除Buffers
再次執行查詢
執行計畫(整體成本:3.28328)
使用計算的資料行將原本查詢statement改成符合SARG格式，
並建立相對應正確索引來提高查詢效能，減少IO讀取，
並降低執行計畫複雜度(減少多餘運算子)。
當然你還可在建立Indexed View再度加強查詢效能，但Indexed View請謹慎使用。
結果比較表
