为什么我的执行计划中会出现排序? [英] Why is there a sort showing up in my execution plan?

问题描述

我下面的 sql 查询运行得很慢.我查看了执行计划，它声称对 Files.OrderId 进行排序是成本最高的操作 (53%).如果我不在任何地方按 OrderId 订购，为什么会发生这种情况?在 File.OrderId 上创建索引是我最好的选择吗?

I have the sql query below that is running very slowly. I took a look at the execution plan and it is claiming that a sort on Files.OrderId is the highest cost operation (53%). Why would this be happening if I am not ordering by OrderId anywhere? Is my best bet to create an index on 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 
)

推荐答案

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

SQL Server has three algorithms to choose from when it needs to join two tables. The Nested-Loops-Join, the Hash-Join and the Sort-Merge-Join. Which one it selects it bases on cost estimates. In this case it figured, that based on the information it had available a Sort-Merge-Join was the right choice.

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

In SQL Server execution plans a Sort-Merge is splitt into two operators, the Sort and the Merge-Join, because the sort operation might not be necessary, for example if the data is sorted already.

有关连接的更多信息，请在此处查看我的连接系列: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/

For mor information about joins check out my join series here: http://sqlity.net/en/1146/a-join-a-day-introduction/ The article about the Sort-Merg-Join is here: http://sqlity.net/en/1480/a-join-a-day-the-sort-merge-join/

为了使您的查询更快，我首先会查看索引.您在查询中有一堆聚集索引扫描.如果你可以用搜索替换其中的一些，你很可能会更好.还要检查 SQL Server 产生的估计值是否与实际执行计划中的实际行数相匹配.如果它们相距甚远，SQL Server 往往会做出错误的选择.因此，提供更好的统计信息也可以帮助您提高查询性能.

To make your query faster, I first would look at indexes. You have a bunch of clustered index scans in the query. If you can replace a few of them with seeks you will be most likely better of. Also check if the estimates that SQL Server produces match the actual row counts in an actual execution plan. If they are far off, SQL Server often makes bad choices. So providing better statistics can help you query performance too.

这篇关于为什么我的执行计划中会出现排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！