基于行的子查询的性能调优:左外部连接和外部应用，备选方案？

Question

某个查询(在Dynamics 2011数据库上)的性能很差。由于它是一个规范化的数据模型，但需要对此数据( SSRS报告)提供一个扁平的视图，因此我使用了一个SELECT TOP (1)子查询完成了大量(12)左外部联接，例如：

LEFT JOIN Filterednew_rates FRates ON FRates.new_ratesid =
    (SELECT TOP (1)
        FRR.new_ratesid
     FROM Filterednew_rates FRR
     WHERE
        FRR.new_contractid = FContract.contractid
        AND FRR.statuscode <> 803270000 -- NOT Obsolete
     ORDER BY FRR.new_startdate DESC
    )

这对少量的结果行有效(就像3行的10秒)，但是我已经让它在大约100个预期的结果行上运行了45分钟(源数据的数量是相同的，只是WHERE子句不同)。因此，我开始寻找“强制”SQL Server“强制”每行运行子查询的方法(从逻辑上讲，这将是线性的)。

然后我阅读了The的应用运算符的威力并设法将上面的内容更改为

OUTER APPLY (
    SELECT TOP (1)
        FRR.*
     FROM Filterednew_rates FRR
     WHERE
        FRR.new_contractid = FContract.contractid
        AND FRR.statuscode <> 803270000 -- NOT Obsolete
     ORDER BY FRR.new_startdate DESC
) AS FRates

这使得执行时间与结果记录的数量成线性关系( 100行约3:30分钟，3行仍约6秒)。不知何故，这使得Server决定将查询执行计划更改得更好！

在SQL中是否有其他方法可以“平平”一个规范化的数据模型而不诉诸集成/分析服务？

编辑：

感谢您的输入“Aaron”和“BAReese”。我将尝试应用透视/UNPIVOT和窗口函数，并报告查询性能差异。

而根据普遍的请求，查询的更大一部分。我尝试过“匿名”查询，所以实际的查询属性更具有描述性。

OUTER APPLY (
    SELECT TOP (1)
        FCO.*
     FROM Filterednew_contractoption FCO
     WHERE
        FCO.new_contractid = FContract.contractid
        AND FCO.new_included = 1 -- Is Included
        AND FCO.new_optionidname = 'SomeOption1'
) AS FOptionSomeOption1
OUTER APPLY (
    SELECT TOP (1)
        FCO.*
     FROM Filterednew_contractoption FCO
     WHERE
        FCO.new_contractid = FContract.contractid
        AND FCO.new_included = 1 -- Is Included
        AND FCO.new_optionidname = 'SomeOption2'
) AS FOptionSomeOption2
OUTER APPLY (
    SELECT TOP (1)
        FCD.*
     FROM FilteredContractDetail FCD
     JOIN FilteredProduct FProd ON FCD.productid = FProd.productid
     WHERE
         FContract.contractid = FCD.contractid
         AND FCD.new_included = 1 -- Is Included
         AND FProd.productnumber IN ('COLDEL1', 'COLDEL2', 'COLDEL3', 'COLDEL4')
) AS FColDelContractDetail
LEFT JOIN FilteredProduct FColDelProduct ON FColDelContractDetail.productid = FColDelProduct.productid
OUTER APPLY (
    SELECT TOP (1)
        FCO.*
     FROM Filterednew_contractoption FCO
     JOIN Filterednew_contractdetail_new_contractoptions FCD_CO ON FCO.new_contractoptionid = FCD_CO.new_contractoptionid
     WHERE
        FCD_CO.contractdetailid = FColDelContractDetail.contractdetailid
        AND FCO.new_included = 1 -- Is Included
        AND FCO.new_optionidname LIKE 'Input1'
) AS FColDelInput1Option
OUTER APPLY (
    SELECT TOP (1)
        FCO.*
     FROM Filterednew_contractoption FCO
     JOIN Filterednew_contractdetail_new_contractoptions FCD_CO ON FCO.new_contractoptionid = FCD_CO.new_contractoptionid
     WHERE
        FCD_CO.contractdetailid = FColDelContractDetail.contractdetailid
        AND FCO.new_included = 1 -- Is Included
        AND FCO.new_optionidname LIKE 'Input2'
) AS FColDelInput2Option
OUTER APPLY (
    SELECT TOP (1)
        FCO.*
     FROM Filterednew_contractoption FCO
     JOIN Filterednew_contractdetail_new_contractoptions FCD_CO ON FCO.new_contractoptionid = FCD_CO.new_contractoptionid
     WHERE
        FCD_CO.contractdetailid = FColDelContractDetail.contractdetailid
        AND FCO.new_included = 1 -- Is Included
        AND FCO.new_optionidname LIKE 'Input3'
) AS FColDelInput3Option
OUTER APPLY (
    SELECT TOP (1)
        FCP.*
     FROM Filterednew_price FCP
     WHERE FCP.new_contractid = FContract.contractid
     AND FCP.statuscode <> 803270000 -- NOT Obsolete
     ORDER BY FCP.new_validfrom DESC
) AS FPrice
OUTER APPLY (
    SELECT TOP (1)
        FCFR.*
     FROM Filterednew_contractforecastresult FCFR
     WHERE FCFR.new_contractid = FContract.contractid
     ORDER BY FCFR.createdon DESC
) AS FForecastResult

Answer 1

由于您使用的是Server，这将是使用加窗函数提高效率的绝佳机会。

像这样的东西可能会帮助它跑得更快：

LEFT JOIN
    (
    SELECT FRR.new_contractid, ROW_NUMBER() over(partition by FRR.new_contractid
                                            order by FRR.new_startdate DESC) as Last_ID
    FROM Filterednew_rates as FRR
    WHERE FRR.statuscode <> 803270000 -- NOT Obsolete
    ) AS FRates
    ON FRates.new_contractid = FContract.contractid
    and FRates.Last_ID = 1

这应该是允许派生表生成所有收缩的列表，但是给出一个优先级列表。从理论上讲，这在服务器上会更容易，并且不会超出需要的次数。您可以做的另一件事是在查询的顶部添加STATISTICS并设置统计时间(假设您是在中测试这一点)。如果在SSMS中，您将在Messages选项卡上获得一个日志，说明每个表的逻辑/物理读取计数以及查询所花费的时间。