[SQL SERVER] case when 效能殺手

2014-05-30

28435
0
SQL_SERVER
2016-02-19

近2年我寫程式習慣有所改變，

前端BLL不在有任何複雜花式的查詢SQL，我把這些邏輯都壓在SP，

當初這麼做是因為要減少AP compile次數(現實世界改where條件和欄外頻率還真高)，

而且以80/20原則來看，使用SP(單傳送sp name..等優點)大部分效能都會比較好，

所以當前端頁面條件繁雜時，我大部分會使用case when讓自己快活一點，

下面我大概模擬測試一下，自己偷懶快活所付出的效能代價。

為了處理複雜多條件，我偷懶使用case when

select POLICY_NO,BILL_NO,TRAN_DATE,PO_YEAR,BILL_NO_TYPE,MODE_PREM
from QTPLG
where POLICY_TYPE=case when LEN(@POLICY_TYPE)>0 then @POLICY_TYPE else POLICY_TYPE end
and TRAN_DATE>=case when @tran_date is not null then @tran_date else TRAN_DATE end
and TRAN_DATE<=case when @tran_date is not null then DATEADD(M,10,@tran_date) else TRAN_DATE end
and PO_YEAR=case when LEN(@po_year)>0 then @po_year else PO_YEAR end
and BILL_NO_TYPE=case when LEN(@bill_no_type)>0 then @bill_no_type else BILL_NO_TYPE end

77筆資料，卻要掃描 5次，IO高達60128。

都走索引了，但沒想到是掃描和平行計畫執行計畫，完全浪費索引存在價值。

接下來看看不使用case when方式

前端BLL我會判斷條件式是否有值，然後組where 條件句

如

if ( POLICY_TYPE.length>0)

condiction+=” and POLICY_TYPE=@POLICY_TYPE”

if(po_year.length>0)

condiction+=” and po_year=@po_year”

…等

select POLICY_NO,BILL_NO,TRAN_DATE,PO_YEAR,BILL_NO_TYPE,MODE_PREM
from QTPLG
where
POLICY_TYPE=@POLICY_TYPE
and TRAN_DATE>=@tran_date
and TRAN_DATE<=DATEADD(M,10,@tran_date)
and PO_YEAR=@po_year

IO:5，掃描1次。

這次就讓索引真正發揮存在價值。

目前我打算都使用sp_executesql來處理

declare @POLICY_TYPE nvarchar(4),
@tran_date datetime,
@po_year numeric(5,0),@bill_no_type nvarchar(4)
,@mysql nvarchar(max),@param nvarchar(400)
set @POLICY_TYPE=N'J'
set @po_year=N'1'
set @tran_date='20020101'
set @param='@POLICY_TYPE nvarchar(4),@tran_date datetime,@po_year numeric(5,0),@bill_no_type nvarchar(4)'

set @mysql='select POLICY_NO,BILL_NO,TRAN_DATE,PO_YEAR,BILL_NO_TYPE,MODE_PREM
from QTPLG where 1=1'
if LEN(@POLICY_TYPE)>0
set @mysql+=' and POLICY_TYPE=@POLICY_TYPE'
if @tran_date is not null
set @mysql+=' and TRAN_DATE>=@tran_date and TRAN_DATE<=DATEADD(M,10,@tran_date)'
if LEN(@po_year)>0
set @mysql+=' and PO_YEAR=@po_year'
if LEN(@bill_no_type)>0
set @mysql+=' and BILL_NO_TYPE=@bill_no_type'

exec sp_executesql @mysql,@param,@POLICY_TYPE,@tran_date,@po_year,@bill_no_type