sql-server – 执行计划显示昂贵的CONVERT_IMPLICIT操作.我可以通过索引修复此问题还是需要更改表

我有一个非常重要,非常慢的视图,其中包含一些非常丑陋的条件,在where子句中.我也知道连接是varchar(13)上的粗连接和慢连接而不是整数标识字段,但是想要改进下面使用此视图的简单查询：

CREATE VIEW [dbo].[vwReallySlowView]  AS  
AS  
SELECT     
  I.booking_no_v32 AS bkno,I.trans_type_v41 AS trantype,B.Assigned_to_v61 AS Assignbk,B.order_date AS dateo,B.HourBooked AS HBooked,B.MinBooked AS MBooked,B.SecBooked AS SBooked,I.prep_on AS Pon,I.From_locn AS Flocn,I.Trans_to_locn AS TTlocn,(CASE I.prep_on WHEN 'Y' THEN I.PDate ELSE I.FirstDate END) AS PrDate,I.PTimeH AS PrTimeH,I.PTimeM AS PrTimeM,(CASE WHEN I.RetnDate < I.FirstDate THEN I.FirstDate ELSE I.RetnDate END) AS RDatev,I.bit_field_v41 AS bitField,I.FirstDate AS FDatev,I.BookDate AS DBooked,I.TimeBookedH AS TBookH,I.TimeBookedM AS TBookM,I.TimeBookedS AS TBookS,I.del_time_hour AS dth,I.del_time_min AS dtm,I.return_to_locn AS rtlocn,I.return_time_hour AS rth,I.return_time_min AS rtm,(CASE WHEN I.Trans_type_v41 IN (6,7) AND (I.Trans_qty < I.QtyCheckedOut)   
                      THEN 0 WHEN I.Trans_type_v41 IN (6,7) AND (I.Trans_qty >= I.QtyCheckedOut) THEN I.Trans_Qty - I.QtyCheckedOut ELSE I.trans_qty END) AS trqty,7) THEN 0 ELSE I.QtyCheckedOut END) AS MyQtycheckedout,7)   
                      THEN 0 ELSE I.QtyReturned END) AS retqty,I.ID,B.BookingProgressStatus AS bkProg,I.product_code_v42,I.return_to_locn,I.AssignTo,I.AssignType,I.QtyReserved,B.DeprepOn,(CASE  B.DeprepOn       
        WHEN 1 THEN  B.DeprepDateTime     
        ELSE   I.RetnDate  
           END)  AS DeprepDateTime,I.InRack 
FROM         dbo.tblItemtran AS I 

INNER JOIN  -- booking_no = varchar(13)
         dbo.tblbookings AS B ON B.booking_no = I.booking_no_v32  --  string inner-join

INNER JOIN  -- product_code = varchar(13) 
        dbo.tblInvmas AS M ON I.product_code_v42 = M.product_code  --  string inner-join

WHERE     (I.trans_type_v41 NOT IN (2,3,7,18,19,20,21,12,13,22)) AND (I.trans_type_v41 NOT IN (6,7)) AND (I.bit_field_v41 & 4 = 0) OR  
                      (I.trans_type_v41 NOT IN (6,7)) AND (I.bit_field_v41 & 4 = 0) AND (B.BookingProgressStatus = 1) OR  
                      (I.trans_type_v41 IN (6,7)) AND (I.bit_field_v41 & 4 = 0) AND (I.QtyCheckedOut = 0) OR  
                      (I.trans_type_v41 IN (6,7)) AND (I.bit_field_v41 & 4 = 0) AND (I.QtyCheckedOut > 0) AND (I.trans_qty - (I.QtyCheckedOut - I.QtyReturned) > 0)

此视图通常使用如下：

select * from vwReallySlowView
where product_code_v42  = 'LIGHTBULB100W'  -- find "100 watt lightbulb" rows

当我运行它时,我得到这个执行计划项目的成本占批处理总成本的20％到80％,谓词CONVERT_IMPLICIT(….&(4))显示在执行这些按位布尔值时似乎非常慢测试如(I.ibitfield& 4 = 0).

我不是MS SQL专家或DBA类型的专家,因为我大多数时候都是非SQL软件开发人员.但我怀疑这种按位组合是一个坏主意,并且拥有离散的布尔字段会更好.

我可以以某种方式改进我的索引,以更好地处理此视图而不更改架构(已经在数千个位置生产)或者我必须将具有几个布尔值的基础表更改为整数bit_field_v41,以解决此问题问题？

这是我在这个执行计划中扫描的tblItemtran上的聚集索引：

-- goal:  speed up  select * from vwReallySlowView where productcode  = 'X'
CREATE CLUSTERED INDEX [idxtblItemTranProductCodeAndTransType] ON [dbo].[tblItemtran] 
(
    [product_code_v42] ASC,-- varchar(13)
    [trans_type_v41] ASC     -- int
)WITH ( PAD_INDEX  = OFF,STATISTICS_NORECOMPUTE  = OFF,SORT_IN_TEMPDB = OFF,IGNORE_DUP_KEY = OFF,DROP_EXISTING = OFF,ONLINE = OFF,ALLOW_ROW_LOCKS  = ON,ALLOW_PAGE_LOCKS  = ON) 
ON [PRIMARY]

以下是执行计划,其中一个产品导致此CONVERT_IMPLICIT谓词的成本为27％.更新请注意,在这种情况下,我的最差节点现在是内连接上的“哈希匹配”,花费34％我相信这是一个我无法避免的成本,除非我能避免在字符串上进行连接,我可以’目前正在摆脱.上面视图中的INNER JOIN操作都在varchar(13)字段上.

放大右下角：

Entire execution plan as .sqlplan is available on skydrive.此图像只是一个视觉概述.单击here以单独查看图像.

更新了整个执行计划.我似乎无法找到什么product_code值在病理上是坏的,但一种方法是从视图中选择count(*)而不是单个产品.但是,仅在基础表中5％或更少的记录中使用的产品似乎在CONVERT_IMPLICIT操作中显示出更低的成本.如果我要在这里修复SQL,我想我会在视图中使用粗WHERE子句,并计算并存储该巨型where-clause-condition的结果作为“IncludeMeInTheView”位字段,在底层表. Presto,问题解决了,对吗？

解决方法

ItemTran Clustered Index Seek Operator

这个操作符实际上是两个操作合二为一.首先,索引查找操作找到与谓词product_code_v42 =’M10BOLT’匹配的所有行,然后每行具有残差谓词bit_field_v41&应用4 = 0.将bit_field_v41从其基类型(tinyint或smallint)隐式转换为整数.

发生转换是因为bitwise-AND operator(&)要求两个操作数属于同一类型.常量值’4’的隐式类型是整数,而data type precedence rules意味着转换低优先级的bit_field_v41字段值.

通过将谓词写为bit_field_v41&可以很容易地纠正问题(例如它). CONVERT(tinyint,4)= 0 – 意味着常量值具有较低的优先级并被转换(在常量折叠期间)而不是列值.如果bit_field_v41是tinyint,则根本不会发生转换.同样,如果bit_field_v41为smallint,则可以使用CONVERT(smallint,4).也就是说,转换不是性能问题,但在可能的情况下匹配类型并避免隐式转换仍然是一种很好的做法.

此寻求的估计成本的主要部分是基表的大小.虽然聚簇索引键本身相当窄,但每行的大小很大.没有给出表的定义,但只是视图中使用的列加起来有明显的行宽.由于聚簇索引包括所有列,因此聚簇索引键之间的距离是行的宽度,而不是索引键的宽度.在某些列上使用版本后缀表明实际表格中有更多列用于以前的版本.

查看搜索,残差谓词和输出列,可以通过构建等效查询来单独检查该运算符的性能(1<> 2是防止自动参数化的技巧,优化器消除了矛盾并且不会出现在查询计划中)：

SELECT
    it.booking_no_v32,it.QtyCheckedOut,it.QtyReturned,it.Trans_qty,it.trans_type_v41
FROM dbo.tblItemTran AS it
WHERE
    1 <> 2
    AND it.product_code_v42 = 'M10BOLT'
    AND it.bit_field_v41 & CONVERT(tinyint,4) = 0;

具有冷数据高速缓存的此查询的性能是令人感兴趣的,因为预读将受到表(聚簇索引)碎片的影响.此表的群集密钥会引发碎片,因此定期维护(重新组织或重建)此索引非常重要,并使用适当的FILLFACTOR为索引维护窗口之间的新行留出空间.

我使用SQL Data Generator生成的样本数据执行了碎片对预读的影响的测试.使用相同的表行计数,如问题的查询计划中所示,高度分段的聚簇索引导致SELECT * FROM视图在DBCC后15秒DROPCLEANBUFFERS.在相同条件下的相同测试与ItemTrans表上新重建的聚簇索引在3秒内完成.

如果表数据通常完全在缓存中,则碎片问题非常不重要.但是,即使碎片较少,宽表行也可能意味着逻辑和物理读取的数量远远高于预期.您还可以尝试添加和删除显式CONVERT以验证我的期望隐含转换问题在此处不重要,除非是最佳实践违规.

更重要的是离开搜索运算符的估计行数.优化时间估计为165行,但在执行时生成4,226行.我稍后会回到这一点,但是出现差异的主要原因是残差谓词的选择性(涉及按位与AND)对于优化器来说很难预测 – 实际上它是用来猜测的.

过滤运算符

我在这里显示过滤谓词主要是为了说明两个NOT IN列表是如何组合,简化然后扩展的,以及为以下哈希匹配讨论提供参考.可以扩展来自搜索的测试查询以合并其效果并确定Filter运算符对性能的影响：

SELECT
    it.booking_no_v32,it.trans_type_v41,it.QtyCheckedOut
FROM dbo.tblItemTran AS it
WHERE
    it.product_code_v42 = 'M10BOLT'
    AND it.bit_field_v41 & CONVERT(tinyint,4) = 0
    AND
    (
        (
            it.trans_type_v41 NOT IN (2,6,22)
            AND it.trans_type_v41 NOT IN (6,7)
        )
        OR
        (
            it.trans_type_v41 NOT IN (6,7)
        )
        OR 
        (
            it.trans_type_v41 IN (6,7)
            AND it.QtyCheckedOut = 0
        )
        OR 
        (
            it.trans_type_v41 IN (6,7)
            AND it.QtyCheckedOut > 0
            AND it.trans_qty - (it.QtyCheckedOut - it.QtyReturned) > 0
        )
    );

计划中的Compute Scalar运算符定义了以下表达式(计算本身将延迟到稍后运算符需要结果)：

[Expr1016] = (trans_qty - (QtyCheckedOut - QtyReturned))

哈希匹配运算符

对字符数据类型执行连接不是此运算符的高估计成本的原因. SSMS工具提示仅显示Hash Keys Probe条目,但重要的详细信息位于SSMS Properties窗口中.

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!