[SQL SERVER]提高動態查詢效能
現實世界中，使用者希望系統可以提供多個查詢條件欄位，
但系統只要越彈性，免不了就要付出一些效能代價，
所以這篇簡單示範一下我個人如何提高動態查詢效能。
1.使用 IF
這方法算是最常見的，基本上就是透過IF(is not null)判斷來組TSQL，
但當前端條件過多時TSQL代碼可讀性就變得很低
ps: is not null會多出篩選運算子
declare @orderid int,@TrackingNumber nvarchar(25),@unitp money,@mysql nvarchar(max),@mycondiction nvarchar(max),@para nvarchar(max)
,@breakline char(2)
set @breakline=char(13) + char(10)
set @orderid=43659
set @TrackingNumber=N'4911-403C-98'
set @unitp=12
set @para='@orderid int,@TrackingNumber nvarchar(25),@unitp money'
set @mysql='SELECT TOP 10000 t.SalesOrderID,t.SalesOrderDetailID,t.CarrierTrackingNumber,t.UnitPrice,t.OrderQty
FROM sales.SalesOrderDetail t join sales.SalesOrderHeader t2
on t.SalesOrderID=t2.SalesOrderID where 1=1 '+@breakline
set @mycondiction=''
--using if
if @orderid is not null
set @mycondiction+=' and t.SalesOrderID = @orderid '+@breakline
if @TrackingNumber is not null
set @mycondiction+=' and t.CarrierTrackingNumber = @TrackingNumber '+@breakline
if @unitp is not null
set @mycondiction+=' and t.UnitPrice >= @unitp '+@breakline
if @mycondiction>''
set @mysql+=@mycondiction+' option(recompile)'
exec sp_executesql @mysql,@para,@orderid,@TrackingNumber,@unitp
為什麼要使用recompile
如果你還不知道什麼是參數探測，那麼以下兩篇文章建議閱讀一下
一般我個人只要在SP中組動態SQL我大部分會加上 recompile 選項，
透過該選項主要是要避免參數探測問題(雖然大部分情況快取執行計畫是有幫助的)，
由於每一次都會重新編譯執行計畫，所以你可以很確定該計畫絕對是經過優化的，
但該選項建議測試過後才使用，以避免過度修正執行計畫造成反效果。
2.使用 or
如果不想使用IF的話，可以使用 or is null 來提高可讀性，
只要輸入參數null將使條件永遠true，但這裡還是要注意使用recompile來提高效能
declare @orderid int,@TrackingNumber nvarchar(25),@unitp money,@mysql nvarchar(max),@mycondiction nvarchar(max),@para nvarchar(max)
,@breakline char(2)
set @breakline=char(13) + char(10)
set @orderid=43659
set @TrackingNumber=N'4911-403C-98'
set @unitp=12
set @para='@orderid int,@TrackingNumber nvarchar(25),@unitp money'
set @mysql='SELECT TOP 10000 t.SalesOrderID,t.SalesOrderDetailID,t.CarrierTrackingNumber,t.UnitPrice,t.OrderQty
FROM sales.SalesOrderDetail t join sales.SalesOrderHeader t2
on t.SalesOrderID=t2.SalesOrderID where (t.SalesOrderID = @orderid or @orderid is null)
and (t.CarrierTrackingNumber = @TrackingNumber or @TrackingNumber is null)
and (t.UnitPrice >= @unitp or @unitp is null) '
exec sp_executesql @mysql,@para,@orderid,@TrackingNumber,@unitp
加上option(recompile)修正執行計畫
3.使用case when
這樣的寫法將浪費更多I/O(參考[SQL SERVER] case when 效能殺手)
declare @orderid int,@TrackingNumber nvarchar(25),@unitp money,@mysql nvarchar(max),@mycondiction nvarchar(max),@para nvarchar(max)
set @orderid=43659
set @TrackingNumber=N'4911-403C-98'
set @unitp=12
set @para='@orderid int,@TrackingNumber nvarchar(25),@unitp money'
set @mycondiction=''
--using case when
set @mysql='SELECT TOP 10000 t.SalesOrderID,t.SalesOrderDetailID,t.CarrierTrackingNumber,t.UnitPrice,t.OrderQty
FROM sales.SalesOrderDetail t join sales.SalesOrderHeader t2
on t.SalesOrderID=t2.SalesOrderID
WHERE 1=1
and t.SalesOrderID =case when @orderid IS NOT NULL then @orderid else t.SalesOrderID end
and t.CarrierTrackingNumber =case when @TrackingNumber IS NOT NULL then @TrackingNumber else t.CarrierTrackingNumber end
and t.UnitPrice>=case when @unitp IS NOT NULL then @unitp else t.UnitPrice end '
exec sp_executesql @mysql,@para,@orderid,@TrackingNumber,@unitp
要提高動態查詢效能就需搭配正確索引，
我針對動態TSQL設計索引規則是由多到少、觀察統計，
說白話一點就是先設計建立包含所有條件索引，
後調整寬鬆條件索引，並讓估計值和實際值差距<=10倍
create proc querysalesorder
(
@orderid int = NULL,
@fromorderdate datetime = NULL,
@toorderdate datetime = NULL,
@minprice money = NULL,
@maxprice money = NULL,
@salesfirstname nvarchar(50)=NULL,
@saleslastname nvarchar(50) = NULL
)
as
set nocount on
select distinct t.SalesOrderID,t.UnitPrice,t.orderqty
,t1.OrderDate
,t2.salesquota
,t3.FirstName,t3.MiddleName,t3.LastName
from SalesOrderDetail t
left join SalesOrderHeader t1 on t.SalesOrderID=t1.SalesOrderID
left join SalesPerson t2 on t1.SalesPersonID=t2.BusinessEntityID
left join Person t3 on t3.BusinessEntityID= t2.BusinessEntityID
where (t1.SalesOrderID = @orderid OR @orderid IS NULL)
and (t1.OrderDate >= @fromorderdate OR @fromorderdate IS NULL)
and (t1.OrderDate <= @toorderdate OR @toorderdate IS NULL)
and (t.UnitPrice >= @minprice OR @minprice IS NULL)
and (t.UnitPrice <= @maxprice OR @maxprice IS NULL)
and (t3.FirstName like @salesfirstname+'%' OR @salesfirstname IS NULL)
and (t3.LastName like '%'+@saleslastname OR @saleslastname IS NULL)
order by t.SalesOrderID
option(recompile)
設計建立索引
create index idx1 on SalesOrderDetail(SalesOrderID,UnitPrice)
include(orderqty)
create index idx1 on SalesOrderHeader(SalesOrderID,SalesPersonID,OrderDate)
create index idx1 on SalesPerson(BusinessEntityID)
include(salesquota)
create index idx1 on Person(BusinessEntityID,FirstName,LastName)
include(MiddleName)
測試索引效益
querysalesorder 43659,'2005-07-01 00:00:00','2006-07-01 00:00:00',1.2,100.5,'t','r'
參考