查询性能改进建议

Question

我试图优化以下查询。它更新临时表。我认为，由于subquess的执行时间需要很多时间。是否有其他方法可以有效地编写此查询？

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';

上面的更新声明需要大约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列表需要包括您感兴趣的所有帐户类型。