在使用计数(*)和内连接时查询缓慢

Question

我的查询大约需要5秒钟才能运行。它返回了将近5000个记录。

SELECT 
    c.CompanyID, 
    c.CompanyName, 
    psr.PartnerSRFName, 
    psr.PartnerSRLName, 
    be.MonthlyFee, 
    (
        SELECT Count(*) 
        FROM Units
        INNER JOIN Properties ON Units.PropertyID = Properties.PropertyID
        WHERE Properties.CompanyID = c.CompanyID
    ) TotalUnits
FROM Company c
LEFT JOIN Partner_SalesRep_CompanyMap psrcm ON c.CompanyID = psrcm.CompanyID
LEFT JOIN Partner_SalesReps psr ON psrcm.Partner_SalesRepsID = psr.AssignedID
LEFT JOIN Billing_Exemption be ON be.CompanyID = c.CompanyID
WHERE c.LeadSourceCompanyID = 1 AND Active = 1

如果我删除了Select Count(*) ...，则运行非常快，但是我需要此数据。有没有办法改进此查询？

谢谢

Answer 1

Gordon Linoff推荐的索引是必不可少的。

您可以做的另一件事是将计算从内线查询移动到连接的子查询。这可能允许RDBMS优化查询，因为现在明确表示无需重复每个记录的计算（CompanyID的不同值实际上会有所不同）：

SELECT 
    c.CompanyID, 
    c.CompanyName, 
    psr.PartnerSRFName, 
    psr.PartnerSRLName, 
    be.MonthlyFee, 
    COALESCE(x.TotalUnits, 0) TotalUnits
FROM Company c
LEFT JOIN Partner_SalesRep_CompanyMap psrcm ON c.CompanyID = psrcm.CompanyID
LEFT JOIN Partner_SalesReps psr ON psrcm.Partner_SalesRepsID = psr.AssignedID
LEFT JOIN Billing_Exemption be ON be.CompanyID = c.CompanyID
LEFT JOIN (
    SELECT Properties.CompanyID, COUNT(*) TotalUnits
    FROM Units
    INNER JOIN Properties ON Units.PropertyID = Properties.PropertyID
    GROUP BY Properties.CompanyID
) x ON x.CompanyID = c.CompanyID
WHERE c.LeadSourceCompanyID = 1 AND Active = 1

另一个选项是将子查询与外部查询合并（通过在外部查询中添加更多连接），在整个外部查询上打开聚合，并在表Units或Properties中使用唯一的列进行计数。我不是这个人的忠实拥护者，因为通常，您越早汇总的效率越早。

Answer 2

为此子查询：

(Select Count(*)
 From Units u join
      Properties p
      on u.PropertyID = p.PropertyID
 Where p.CompanyID = c.CompanyID
) as TotalUnits

您需要properties(companyid, propertyid)和units(propertyid)。