SQL 效能優化案例

2016-09-20

以 Merge 表格方式，取代單筆資料個別查詢資料庫

這次要優化的 SQL

Select
o.CreatedOn,
c.Username,
c.RealUserName,
o.OrderGuid, 
o.OrderSource,
[dbo].[GetPaymentMethodBySystemName](o.PaymentMethodSystemName) as PaymentMethod,
case when o.PaymentMethodSystemName in ('CreditCard_Fax','CreditCard_Web_NCC_P3','CreditCard_Web','CreditCard_Web_CitiBank_P3','RetailStoreCreditCard') then o.AuthorizationTransactionCode when o.PaymentMethodSystemName = 'VirtualATM' then o.VirtualATMCode end as PaymentCode,
opv.Price as PayTotal, 
o.Billing_InvoiceNumber
From 
[Order] o,
OrderProductVariant opv, 
Customer c
where
o.CustomerId = c.Id and
o.OrderStatus not in ('Pending', 'Cancelled') and
o.PaymentStatus = 'Paid' and
opv.OrderId = o.Id and
opv.CategoryType in ('Confinment', 'StoredValueCard') and
o.CreatedOn >= '2016-08-01' and o.CreatedOn <= '2016-08-31'

一開始很直覺看到 where 子句中，不符合 SARG STATEMENT，但是 Order Table 裡沒有設定 OrderStatus ,CategoryType 的索引，所以改成符合 SARG STATEMENT 也不會提升效益。

仔細觀察 select 欄位裡，有用到函式 GetPaymentMethodBySystemName

ALTER FUNCTION [dbo].[GetPaymentMethodBySystemName]( @systemName varchar(100))
RETURNS varchar (100)
AS
BEGIN
    RETURN (Select Top 1 item2  From ItemMaster where Groupname like '%PaymentMethod%' and Item1 = @systemName)
ENd

這函式會再去讀取另一個 table ItemMaster

也就是說，每次搜尋完成後，每筆資料會再去讀取這個資料庫，要是搜尋筆數 1000 筆，

就會另外再讀取資料庫 1000 次....

bingo, 找到速度慢的原因了!!

解決方式 :

將 ItemMaster table 直接 join 在原表格內，SQL 改成以下

Select
o.CreatedOn,
c.Username,
c.RealUserName,
o.OrderGuid,
o.OrderSource,
p.Item2 as PaymentMethod,
case when o.PaymentMethodSystemName in ('CreditCard_Fax','CreditCard_Web_NCC_P3','CreditCard_Web','CreditCard_Web_CitiBank_P3','RetailStoreCreditCard') then o.AuthorizationTransactionCode when o.PaymentMethodSystemName = 'VirtualATM' then o.VirtualATMCode end as PaymentCode,
opv.Price as PayTotal,
o.Billing_InvoiceNumber
From
[Order] o,
OrderProductVariant opv,
Customer c,
(select Item1,Item2 from (SELECT  *, ROW_NUMBER() OVER (PARTITION BY item1 ORDER BY id) rn FROM itemmaster where groupname like '%PaymentMethod%')r where r.rn=1) p 
where
o.CustomerId = c.Id and
opv.OrderId = o.Id and
o.PaymentMethodSystemName = p.Item1 and
o.OrderStatus not in ('Pending', 'Cancelled') and
o.PaymentStatus = 'Paid' and
opv.CategoryType in ('Confinment', 'StoredValueCard') and
o.CreatedOn >= '2016-08-01' and o.CreatedOn <= '2016-08-31'

在 ItemMaster 裡，我們只需要每個 Group(以 item1 為分群條件)的第一筆資訊，因此以 PARTITION BY 處理該表格

處理後的表格 join 到原表格內，直接取得 ItemMaster 的 item2

收工~

處理前

處理後

耗費時間減少一半...

回首頁