我下面的SQL查询运行得很慢.我看看执行计划,它声称对Files.OrderId的排序是最高的成本操作(53％).为什么会发生这种情况,如果我没有在OrderId的任何地方订购？我最好打赌在File.OrderId上创建索引吗？
Execution plan如果有人有兴趣
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.isprimary,c.orderid,c.createdate,au.firstname,au.lastname
)
解决方法
sql Server有三种算法可以选择何时需要连接两个表.嵌套循环加入,哈希加入和排序合并加入.它选择哪一个基于成本估算.在这种情况下,它认为,根据可用的信息,Sort-Merge-Join是正确的选择.
在sql Server执行计划中,Sort-Merge将分割为两个运算符,即Sort和Merge-Join,因为排序操作可能不是必需的,例如,如果数据已经排序.
关于联系的信息,请查看我的加入系列：http://sqlity.net/en/1146/a-join-a-day-introduction/
关于排序合并 – 加入的文章在这里：http://sqlity.net/en/1480/a-join-a-day-the-sort-merge-join/
为了使您的查询更快,我首先会查看索引.您在查询中有一堆聚簇索引扫描.如果您可以用寻找来替换其中的一些,那么您最有可能会更好.还要检查sql Server生成的估计是否符合实际执行计划中的实际行数.如果他们很远,sql Server往往会做出不好的选择.因此,提供更好的统计信息可以帮助您查询性能.
