是否查询性能改进建议？

Question

我正在尝试优化下面的查询。正在更新临时表。我认为由于子查询的原因，它需要花费很多时间来执行。有没有其他方法可以有效地编写这个查询？

UPDATE CA
SET CA.GP = (ISNULL(BOY.GP, 0) + ISNULL(CA.GP, 0) + ISNULL(NI.GP, 0) + ISNULL(TI.GP, 0) + ISNULL(OI.GP, 0)) - (ISNULL(NDE.GP, 0) + ISNULL(DS.GP, 0) + ISNULL(OD.GP, 0)),
    CA.B7 = (ISNULL(BOY.B7, 0) + ISNULL(CA.B7, 0) + ISNULL(NI.B7, 0) + ISNULL(TI.B7, 0) + ISNULL(OI.B7, 0)) - (ISNULL(NDE.B7, 0) + ISNULL(DS.B7, 0) + ISNULL(OD.B7, 0)),
    CA.TB = (ISNULL(BOY.TB, 0) + ISNULL(CA.TB, 0) + ISNULL(TI.TB, 0) + ISNULL(OI.TB, 0)) - (ISNULL(NDE.TB, 0) + ISNULL(DS.TB, 0) + ISNULL(OD.TB, 0)),
    CA.Other = (ISNULL(BOY.Other, 0) + ISNULL(CA.Other, 0) + ISNULL(NI.Other, 0) + ISNULL(TI.Other, 0) + ISNULL(OI.Other, 0)) - (ISNULL(NDE.Other, 0) + ISNULL(DS.Other, 0) + ISNULL(OD.Other, 0))
FROM #CADetail CA
     JOIN (SELECT GP,
                  B7,
                  TB,
                  Other,
                  TC
           FROM #CADetail
           WHERE CAAccount = N'Balance at beginning of year') BOY ON BOY.TC = CA.TC
     JOIN (SELECT GP,
                  B7,
                  TB,
                  Other,
                  TC
           FROM #CADetail
           WHERE CAAccount = N'CA Contributed') CA ON CA.TC = CA.TC
     JOIN (SELECT GP,
                  B7,
                  TB,
                  Other,
                  TC
           FROM #CADetail
           WHERE CAAccount = N'Net Income/Loss') NI ON NI.TC = CA.TC
     JOIN (SELECT GP,
                  B7,
                  TB,
                  Other,
                  TC
           FROM #CADetail
           WHERE CAAccount = N'T Exempt Income') TI ON TI.TC = CA.TC
     JOIN (SELECT GP,
                  B7,
                  TB,
                  Other,
                  TC
           FROM #CADetail
           WHERE CAAccount = N'Other Increases') OI ON OI.TC = CA.TC
     JOIN (SELECT GP,
                  B7,
                  TB,
                  Other,
                  TC
           FROM #CADetail
           WHERE CAAccount = N'Non-Deductible Expenses') NDE ON NDE.TC = CA.TC
     JOIN (SELECT GP,
                  B7,
                  TB,
                  Other,
                  TC
           FROM #CADetail
           WHERE CAAccount = N'DSs') DS ON DS.TC = CA.TC
     JOIN (SELECT GP,
                  B7,
                  TB,
                  Other,
                  TC
           FROM #CADetail
           WHERE CAAccount = N'Other Decreases') OD ON OD.TC = CA.TC
WHERE CA.CAAccount = N'Balance at end of year';

上面的update语句大约需要15分钟来获取更新。有没有更好的方法来优化这个查询？

Answer 1

您可以使用窗口函数来计算值：

UPDATE CA
    SET CA.GP = TOTAL_GP,
        CA.B7 = TOTAL_B7,
        CA.TB = TOTAL_TB,
        CA.Other = TOTAL_OTHER
FROM (SELECT CA.*,
             SUM(CASE WHEN CAAccount IN (N'T Exempt Income', N'Other Increases', . . .)
                      THEN GP ELSE 0
                 END) OVER (PARTITION BY TC) as total_gp,
             SUM(CASE WHEN CAAccount IN (N'T Exempt Income', N'Other Increases', . . .)
                      THEN B7 ELSE 0
                 END) OVER (PARTITION BY TC) as total_b7,
             SUM(CASE WHEN CAAccount IN (N'T Exempt Income', N'Other Increases', . . .)
                      THEN TB ELSE 0
                 END) OVER (PARTITION BY TC) as total_tb,
             SUM(CASE WHEN CAAccount IN (N'T Exempt Income', N'Other Increases', . . .)
                      THEN other ELSE 0
                 END) OVER (PARTITION BY TC) as total_other    
      FROM #CADetail CA
     ) CA
WHERE CA.CAAccount = N'Balance at end of year';

注意：IN列表需要包括您感兴趣的所有帐户类型。