题 为什么我的执行计划中会出现这种情况?


我有下面的SQL查询运行速度非常慢。我看了一下执行计划,它声称Files.OrderId上的排序是成本最高的操作(53%)。如果我没有在任何地方订购OrderId,为什么会发生这种情况呢?我最好在File.OrderId上创建索引吗?

执行计划 如果有人有兴趣

with custOrders as
(
    SELECT c.firstName + ' ' + c.lastname as Customer, c.PartnerId , c.CustomerId,o.OrderId,o.CreateDate, c.IsPrimary
    FROM Customers c
    LEFT JOIN CustomerRelationships as cr
        ON c.CustomerId = cr.PrimaryCustomerId
    INNER JOIN Orders as o
       ON c.customerid = o.customerid 
           OR (cr.secondarycustomerid IS NOT NULL AND o.customerid = cr.secondarycustomerid)
    where c.createdate >= @FromDate + ' 00:00' 
       AND c.createdate <= @ToDate + ' 23:59' 
),
 temp as
(
SELECT Row_number() 
         OVER ( 
           ORDER BY c.createdate DESC)                    AS 'row_number', 
       c.customerid as customerId, 
       c.partnerid as partnerId, 
       c.Customer, 
       c.orderid as OrderId, 
       c.createdate as CreateDate, 
       Count(f.orderid)                                   AS FileCount, 
       dbo.Getparentcustomerid(c.isprimary, c.customerid) AS ParentCustomerId, 
       au.firstname + ' ' + au.lastname                   AS Admin, 
       '' as blank, 
       0  as zero
FROM   custOrders c 
       INNER JOIN files f 
               ON c.orderid = f.orderid 
       INNER JOIN admincustomers ac 
               ON c.customerid = ac.customerid 
       INNER JOIN adminusers au 
               ON ac.adminuserid = au.id 
       INNER JOIN filestatuses s 
               ON f.statusid = s.statusid 
WHERE  ac.adminuserid IS NOT NULL 
       AND f.statusid NOT IN ( 5, 6 ) 
GROUP  BY c.customerid, 
          c.partnerid, 
          c.Customer, 
          c.isprimary, 
          c.orderid, 
          c.createdate, 
          au.firstname, 
          au.lastname 
)

15
2018-01-08 16:25


起源


验证源XML时出错 - Kermit
查看 本文,“如何避免SORT操作”部分。 - Darth Continent
是的,我看到了。它真的有所作为吗?你仍然可以看到XML ... - Abe Miessler
不 OVER 意味着什么? - Kermit
@njk是的,但不是OrderId - Sebastian Meine


答案:


SQL Server在需要连接两个表时有三种算法可供选择。 Nested-Loops-Join,Hash-Join和Sort-Merge-Join。它选择哪一个基于成本估算。在这种情况下,它认为,基于它可用的信息,Sort-Merge-Join是正确的选择。

在SQL Server执行计划中,Sort-Merge被拆分为两个运算符,Sort和Merge-Join,因为排序操作可能不是必需的,例如,如果数据已经排序。

有关联接的mor信息,请查看我的联接系列: http://sqlity.net/en/1146/a-join-a-day-introduction/ 关于Sort-Merg-Join的文章在这里: http://sqlity.net/en/1480/a-join-a-day-the-sort-merge-join/


为了使您的查询更快,我首先看看索引。您在查询中有一堆聚簇索引扫描。如果你可以用寻求替换它们中的一些,那么你很可能会更好。还要检查SQL Server生成的估计值是否与实际执行计划中的实际行计数相匹配。如果它们距离很远,SQL Server通常做出错误的选择。因此提供更好的统计信息也可以帮助您查询性能。


11
2018-01-08 16:41



+1表示实际行数和统计数据 - Chris Smith


SQL Server正在执行排序,以启用该排序运算符右侧的数据集与该排序运算符中的记录之间的合并连接 Orders 表。合并连接本身是一种非常有效的方式来连接数据集中的所有记录,但它要求要连接的每个数据集都根据连接键和相同的顺序进行排序。

自从 PK_Orders 钥匙已经订购了 OrderID,SQL Server决定通过对连接的另一端(排序右侧的其他内容)进行排序来利用它,以便两个数据集可以在计划中的该点合并在一起。合并连接的常见替代方法是散列连接,但这对您没有帮助,因为您将使用昂贵的散列连接运算符而不是排序和合并。在这种情况下,查询优化器已确定排序和合并更有效。

计划中昂贵步骤的根本原因是需要将订单表中的所有记录组合到数据集中。有没有办法限制来自的记录 files  表?索引 files.statusid 如果不在5,6中的记录小于总表大小的10%,则可能会有所帮助。

QO认为大部分记录最终会被过滤掉。尝试将尽可能多的过滤条件反馈到记录源,以便在计划的中间处理更少的记录。

编辑: 我忘了提到,制定一个我们可以看到的执行计划是非常有帮助的。有什么方法可以得到一个 实际 执行计划结果,以查看通过这些运营商的实际记录数量?有时估计的记录数量可能有点偏差。

编辑: 深入研究第二个到最后一个过滤器运算符的谓词字段,总结如下:

c.CustomerId=o.CustomerId
OR o.CustomerId=cr.SecondaryCustomerId AND cr.SecondaryCustomerId IS NOT NULL

看起来SQL Server正在生成所有可能的匹配记录之间的交叉连接 Orders 和 Customers 直到查询中的这一点(第二个到最后一个过滤器运算符右侧的计划)然后查看具有该条件的每个记录以查看它是否确实匹配。请注意进入过滤器的线路是如何变胖的,而且出来的线路真的很薄?那是因为在该运算符之后估计的行数从21k变为4。忘掉我之前说过的话,这可能是计划中的主要问题。即使这些列上有索引,SQL Server也无法使用它们,因为连接条件太复杂。它导致计划将所有记录合并在一起,而不是只寻求您需要的记录,因为它不能立即使用完整的连接谓词。

我的第一个想法是改写CTE custOrders 作为两个数据集的联合:一个使用 CustomerId 和一个使用 SecondaryCustomerId 加入。这将复制CTE其余部分的工作,但如果能够正确使用索引,则可能是一个巨大的胜利。


2
2018-01-08 16:49



好的,我更新了实际的计划 - Abe Miessler
仍然看起来像最初的估计计划。您是否编辑了问题或在XML Playground上更改了文档? - Chris Smith


我认为此连接正在发生排序:

FROM   custOrders c 
       INNER JOIN files f 
               ON c.orderid = f.orderid 

我会在包含列orderid和statusid的文件上创建索引,因为查询也使用了statusid列。

您可能还需要考虑以下更改:

  1. 您不需要“ac.adminuserid IS NOT NULL”,因为adminusers和admincustomers之间的内部联接涵盖了这一点
  2. 将测试“f.statusid NOT IN(5,6)”改为正条件(例如In),因为负条件处理起来更昂贵。

1
2018-01-08 16:40



[files]已经有了覆盖索引 - Sebastian Meine
是的,它已经在进行非聚集索引寻找 Files。有没有办法进一步减少来自的记录数 Files? - Chris Smith
从性能角度来看,非聚集索引搜索很好。减少记录计数的另一个变化是消除上面建议的针对statusid的NOT IN检查。 - Jeff Siver


我知道这个问题已经很老了,但是我遇到了同样的问题并且意识到我的桌子突然放慢了一个完全不同的原因。症状相同,更新以前闪电般快速的视图很慢。 “排序”给出了40%的成本。 这个解决方案可能对某人有用,而且很简单。 加入表格时,请确保您加入“喜欢”之类的基础。 我在ID上加入了两张桌子。但是在一个表中,我的ID被设置为int,而另一个表被设置为nvarchar。我更正了这个问题,将它们定义为相同类型,并且视图恢复闪电般的速度。

希望这可以帮助其他人避免花费一周时间试图找出SQL的错误,当它真的是一个PEBKAC时刻。

(键盘和椅子之间存在问题)


0
2017-12-30 15:10