次の方法で共有

SQL Server性能问题案例解析 （3）

  • [アーティクル]

今天的博客是SQL Server 性能问题解析系列的最后一个案例。这个案例背景是用户反映以下查询语句执行时间过长，执行时间为10秒以上（已经排除了blocking原因），希望将执行时间压缩到2秒以内。

declare @Company as varchar(10)

set @Company = 'a'

declare @SystemVersion as varchar(30)

set @SystemVersion = 'Total Ticketing Solution v2.1.3'

declare @LocationID_PSS as varchar (10)

declare @UserCode_PSS as varchar (10)

declare @ProductId_PSS as varchar (10)

declare @TrxDateFr as datetime

declare @TrxDateTo as datetime

set @LocationID_PSS = null

set @UserCode_PSS = null

set @ProductId_PSS = null

set @TrxDateFr = '2006-5-17'

set @TrxDateTo = '2006-5-18'

SELECT

@Company as company,

@SystemVersion as version,

@TrxDateFr AS TrxDateFrom, @TrxDateTo AS TrxDateTo,

l.LocationID,

l.LocationDesc,

TH.ClientPCName,

ProductDesc = (P.ProductDesc1 + ' ' + P.ProductDesc2),

TD.PkgHDDesc ,

TD.PassTypecode,

TD.SellingPrice,

TD.TktAmt,

TD.DiscountDesc,

TPD.PayTypeCode,

TPD.PaymodeCode,

TP.Status,

COUNT(TD.TicketNo) AS TotalTktQty,

TotalAmt = sum (TD.TktAmt)

FROM

TransactionHd TH ,

TransactionDetail TD ,

TransactionPaymentDetail TPD ,

Product P , Location l  ,TransactionPayment TP ,TimeSlot TS 

WHERE

TH.TrxHdID = TD.TrxHdID AND TD.TrxDetailID = TP.TrxDetailID AND

TP.TrxPaymentDetailID=TPD.TrxPaymentDetailID AND

P.ProductID =TS.ProductID AND

TD.TimeSlotID=TS.TimeSlotID AND

L.LocationID = Th.LocationID AND ((L.LocationID = @LocationID_PSS)OR (@LocationID_PSS IS NULL))

AND ((TPD.LastUpdateBy <=@UserCode_PSS)OR> (@UserCode_PSS IS NULL)) AND

TP.Status IN ('SOLD' , 'REFUND', 'VOID') AND ((P.ProductID = @ProductId_PSS)
OR (@ProductID_PSS IS NULL)) AND ( ( ((TH.LastUpdateBy =@UserCode_PSS)OR (@UserCode_PSS IS NULL)) AND (TH.LastUpdateDate BETWEEN
@TrxDateFr AND dateadd( d, 0 ,@TrxDateTo))) 

OR

(((TP.CancelBy=@UserCode_PSS)OR (@UserCode_PSS IS NULL)) AND (TP.CancelDateTime BETWEEN
@TrxDateFr AND dateadd( d, 0 ,@TrxDateTo)) ) )

GROUP BY L.LocationID,

L.LocationDesc,Th.ClientPCName,TH.Lastupdateby,TP.CancelBy , TD.IsVoucher, TD.TktTypeCode,

P.ProductDesc1,P.ProductDesc2,TD.PkgHDDesc,TD.PassTypeCode,TD.SellingPrice , TD.TktAmt, TD.IsGroup, TD.PaxQty,

DiscountDesc, DiscountPerCent,DiscountValue ,TPD.PayTypeCode, TPD.PaymodeCode, TP.Status

 

首先执行set statistics profile on, set statistics io on和set statistics time on 来获取语句的执行计划和相关执行信息，我们会看到统计信息基本准确，排除了统计信息过时的问题。

接下来我们来看下IO和CPU的相关信息， 我们可以看到'TransactionDetail' 所消耗的逻辑读最多，达到了32895， 第二名是TransactionPayment，达到了4721. 而TransactionPaymentDetail的读取次数最多，达到了4616次。

Table 'TransactionPaymentDetail'. Scan count 4616, logical reads 14787, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table
'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table
'TransactionHd'. Scan count 1, logical reads 4678, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table
'TransactionPayment'. Scan count 1, logical reads 4721, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TransactionDetail'. Scan count 1, logical reads 32895, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TimeSlot'. Scan count 1, logical reads 666, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Product'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Location'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

TransactionPaymentDetail在index seek之后和TransactionDetail进行了nested loop, 对于sql server来说，nested如果大量执行，是非常消耗cpu的，也会增加执行时间。

实际的执行计划显示，TransactionDetail和TransactoinPayment两张表占用了54%的性能消耗。

 
 
  
   

两张表都采用了cluster index scan而不是seek。 这是两张表关联的方式 - TD.TrxDetailID = TP.TrxDetailID。检查发现连接的字段的类型相同，这一点上没有问题。

将鼠标悬浮在TransactoinPayment上，发现上面的Predicate有很多or的操作，这很可能导致了sql生成过于复杂的执行计划，增长了执行时间。

在语句的尾部，发现了or的语句:

OR

(((TP.CancelBy <=@UserCode_PSS)OR> (@UserCode_PSS IS NULL)) AND (TP.CancelDateTime BETWEEN
@TrxDateFr AND dateadd( d, 0 ,@TrxDateTo)) ) ) 

在t-sql语句中，or和union是可以互换的。于是进行如下改写，即将最后的or的两个条件拆成两个select语句然后进行union操作， 之后语句能在2秒内就完成了。 需要注意的是，对于or或union，并没有确定的孰优孰劣，使用时要进行测试才能确定。

declare @Company as varchar (10)

set @Company = 'a'

declare @SystemVersion as varchar(30)

set @SystemVersion = 'Total Ticketing Solution v2.1.3'

declare @LocationID_PSS as varchar (10)

declare @UserCode_PSS as varchar (10)

declare @ProductId_PSS as varchar (10)

declare @TrxDateFr as datetime

declare @TrxDateTo as datetime

set @LocationID_PSS = null

set @UserCode_PSS = null

set @ProductId_PSS = null

set @TrxDateFr = '2006-5-17'

set @TrxDateTo = '2006-5-18'

SELECT

@Company as company,

@SystemVersion as version,

@TrxDateFr AS TrxDateFrom, 
@TrxDateTo AS TrxDateTo,

l.LocationID,

l.LocationDesc,

TH.ClientPCName,

ProductDesc = (P.ProductDesc1 + ' ' + P.ProductDesc2),

TD.PkgHDDesc ,

TD.PassTypecode,

TD.SellingPrice,

TD.TktAmt,

TD.DiscountDesc,

TPD.PayTypeCode,

TPD.PaymodeCode,

TP.Status, COUNT(TD.TicketNo) AS TotalTktQty,

TotalAmt = sum (TD.TktAmt)

FROM

TransactionHd TH ,

TransactionDetail TD ,

TransactionPaymentDetail TPD ,

Product P , Location l ,TransactionPayment TP ,

TimeSlot TS 

WHERE

TH.TrxHdID = TD.TrxHdID AND

TD.TrxDetailID = TP.TrxDetailID AND

TP.TrxPaymentDetailID=TPD.TrxPaymentDetailID AND

P.ProductID =TS.ProductID AND

TD.TimeSlotID=TS.TimeSlotID AND

L.LocationID = Th.LocationID AND

((L.LocationID = @LocationID_PSS)OR (@LocationID_PSS IS NULL)) AND

((TPD.LastUpdateBy =@UserCode_PSS)OR (@UserCode_PSS IS NULL)) AND

TP.Status IN ('SOLD' , 'REFUND', 'VOID') AND

((P.ProductID = @ProductId_PSS ) OR (@ProductID_PSS IS NULL)) AND

( ( ((TH.LastUpdateBy =@UserCode_PSS)OR (@UserCode_PSS IS NULL)) AND (TH.LastUpdateDate BETWEEN @TrxDateFr AND 
dateadd( d, 0 ,@TrxDateTo))) )

GROUP BY L.LocationID,

L.LocationDesc,Th.ClientPCName,TH.Lastupdateby, TP.CancelBy , TD.IsVoucher, TD.TktTypeCode,

P.ProductDesc1,P.ProductDesc2,TD.PkgHDDesc, TD.PassTypeCode,TD.SellingPrice , TD.TktAmt, TD.IsGroup, TD.PaxQty,

DiscountDesc, DiscountPerCent, DiscountValue ,TPD.PayTypeCode, TPD.PaymodeCode, TP.Status

Union

SELECT

@Company as company,

@SystemVersion as version,

@TrxDateFr AS TrxDateFrom, 
@TrxDateTo AS TrxDateTo,

l.LocationID,

l.LocationDesc,

TH.ClientPCName,

ProductDesc = (P.ProductDesc1 + ' ' + P.ProductDesc2),

TD.PkgHDDesc ,

TD.PassTypecode,

TD.SellingPrice,

TD.TktAmt,

TD.DiscountDesc,

TPD.PayTypeCode,

TPD.PaymodeCode,

TP.Status, COUNT(TD.TicketNo) AS TotalTktQty,

TotalAmt = sum (TD.TktAmt)

FROM

TransactionHd TH ,

TransactionDetail TD ,

TransactionPaymentDetail TPD ,

Product P ,

Location l ,TransactionPayment TP ,

TimeSlot TS 

WHERE

TH.TrxHdID = TD.TrxHdID AND

TD.TrxDetailID = TP.TrxDetailID AND

TP.TrxPaymentDetailID=TPD.TrxPaymentDetailID AND

P.ProductID =TS.ProductID AND

TD.TimeSlotID=TS.TimeSlotID AND

L.LocationID = Th.LocationID AND

((L.LocationID = @LocationID_PSS)OR (@LocationID_PSS IS NULL)) AND

((TPD.LastUpdateBy =@UserCode_PSS)OR (@UserCode_PSS IS NULL)) AND

TP.Status IN ('SOLD' , 'REFUND', 'VOID') AND

((P.ProductID = @ProductId_PSS ) OR (@ProductID_PSS IS NULL)) AND

( (((TP.CancelBy =@UserCode_PSS)OR (@UserCode_PSS IS NULL)) AND (TP.CancelDateTime BETWEEN @TrxDateFr AND 
dateadd( d, 0 ,@TrxDateTo)) ))

GROUP BY L.LocationID,

L.LocationDesc,Th.ClientPCName,TH.Lastupdateby, TP.CancelBy , TD.IsVoucher, TD.TktTypeCode,

P.ProductDesc1,P.ProductDesc2,TD.PkgHDDesc, TD.PassTypeCode,TD.SellingPrice , TD.TktAmt, TD.IsGroup, TD.PaxQty,

DiscountDesc, DiscountPerCent,
DiscountValue ,TPD.PayTypeCode, TPD.PaymodeCode, TP.Status

 

另外这个语句也可以用另一种思路去调优，那就是仔细分析下语句的逻辑，看是否可以简化，我们可以发现这个语句中以下这部分代码非常繁琐。

 

((L.LocationID = @LocationID_PSS)OR (@LocationID_PSS IS NULL)) AND

((TPD.LastUpdateBy <=@UserCode_PSS)OR> (@UserCode_PSS IS NULL)) AND

TP.Status IN ('SOLD' ,'REFUND', 'VOID') AND ((P.ProductID = @ProductId_PSS) OR (@ProductID_PSS IS NULL)) AND

 

进一步查看会发现 @LocationID_PSS，@UserCode_PSS和 @ProductID_PSS这些参数的值就是NULL，然而当他们是NULL值的时候，以上条件其实是多余的，我们完全可以移除它们，经过测试，移除后执行计划简化了很多，执行速度更是大大提高。因此我们可以在这个语句前加一个IF…ELSE的条件，通过IF来判断这些参数是否为NULL值，如果是，则执行移除这些条件的语句，如果不是，则再运行上面调优后使用UNION的语句。这样使得这个语句的运行速度达到了最优化。

这就是今天的分享，更多SQL Server技术文章请持续关注本博客的更新。

Comments

  • Anonymous
    August 28, 2015
    这个问题简化了说就是这个 DECLARE @i INT =***; SELECT * FROM TEST WHERE (@i IS NULL OR id=@i); 造成的全表扫描 SqlServer应该学习mysql中对于类似语义的解析， 在mysql中，他就把这个优化了，类似于动态地实现了对于@i的识别

  • Anonymous
    January 03, 2016
    I think that can be good and so helpful http://lolebazkoni.ir/