多个相关的子查询正在杀死SQL Server.如何避免这种情况

我有两个表，我需要从中生成报告。我编写了这个查询来解决我在较小的数据库中的任务，但是如果记录量超过 5-6 百万，查询的性能就会变得非常差

insert into Reconcile ([Account], [Beginning balance], [Turnover TB], [Turnover JE], [Diff], [Ending balance], [Ending balance client]) 
select 
    [GL Account Number ], 
    [Functional Beginning Balance], 
    (case when  (select COUNT([DMBTR]) from JE where [SHKZG]='S' and je.[HKONT]=[GL Account Number ]) = 0 
        then 0 
    else (select SUM([DMBTR]) from JE where [SHKZG]='S' and je.[HKONT]=[GL Account Number ]) end 
    + case when (select COUNT([DMBTR]) from JE where [SHKZG]='H' and je.[HKONT]=[GL Account Number ]) = 0 
        then 0 
    else (select SUM([DMBTR]) from JE where [SHKZG]='H' and je.[HKONT]=[GL Account Number ]) end), 
    [Turnover], 
    ((case when (select COUNT([DMBTR]) from JE where [SHKZG]='S' and je.[HKONT]=[GL Account Number ]) = 0 
        then 0 
    else (select SUM([DMBTR]) from JE where [SHKZG]='S' and je.[HKONT]=[GL Account Number ]) end 
    + case when (select COUNT([DMBTR]) from JE where [SHKZG]='H' and je.[HKONT]=[GL Account Number ]) = 0 
        then 0 
    else (select SUM([DMBTR]) from JE where [SHKZG]='H' and je.[HKONT]=[GL Account Number ]) end) - ([Turnover])), 
    ([Functional Beginning Balance] + 
    (case when (select COUNT([DMBTR]) from JE where [SHKZG]='S' and je.[HKONT]=[GL Account Number ]) = 0 
        then 0 
    else (select SUM([DMBTR]) from JE where [SHKZG]='S' and je.[HKONT]=[GL Account Number ]) end 
    + case when (select COUNT([DMBTR]) from JE where [SHKZG]='H' and je.[HKONT]=[GL Account Number ]) = 0 
        then 0 
    else (select SUM([DMBTR]) from JE where [SHKZG]='H' and je.[HKONT]=[GL Account Number ]) end)), 
    [Functional Ending Balance] 
from TB LEFT JOIN JE je 
ON TB.[GL Account Number ]=[HKONT]
group by [GL Account Number ], [Functional Beginning Balance], [Turnover], [Functional Ending Balance], [HKONT]

我知道问题出在多个重复的子查询中，但我对 tsql 很陌生，我不知道如何为数据集中的每条记录运行一次此子查询，然后仅在使用该子查询的其他情况下使用它的返回值

(case when  (select COUNT([DMBTR]) from JE where [SHKZG]='S' and je.[HKONT]=[GL Account Number ]) = 0 
        then 0 
    else (select SUM([DMBTR]) from JE where [SHKZG]='S' and je.[HKONT]=[GL Account Number ]) end 
    + case when (select COUNT([DMBTR]) from JE where [SHKZG]='H' and je.[HKONT]=[GL Account Number ]) = 0 
        then 0 
    else (select SUM([DMBTR]) from JE where [SHKZG]='H' and je.[HKONT]=[GL Account Number ]) end)

如何优化此查询？

如何优化此查询？

好吧，请尝试以下操作：

避免任何子查询。
尽可能将子查询替换为派生表。
有效使用分组。
避免重复代码。

话虽如此，

请尝试以下查询。

INSERT INTO Reconcile ([Account], [Beginning balance], [Turnover TB], [Turnover JE], [Diff], [Ending balance], [Ending balance client]) 
SELECT 
    [GL Account Number ], 
    [Functional Beginning Balance], 
    (CASE WHEN  ISNULL(JE_S.DMBTR_COUNT, 0) = 0 THEN 0 ELSE ISNULL(JE_S.DMBTR_SUM, 0) END 
    + CASE WHEN ISNULL(JE_H.DMBTR_COUNT, 0) = 0 THEN 0 ELSE ISNULL(JE_H.DMBTR_SUM, 0) END), 
    [Turnover], 
    ((CASE WHEN ISNULL(JE_S.DMBTR_COUNT, 0) = 0 THEN 0 ELSE ISNULL(JE_S.DMBTR_SUM, 0) END 
    + CASE WHEN ISNULL(JE_H.DMBTR_COUNT, 0) = 0 THEN 0 ELSE ISNULL(JE_H.DMBTR_SUM, 0) END) 
    - ([Turnover])), 
    ([Functional Beginning Balance] 
    + (CASE WHEN ISNULL(JE_S.DMBTR_COUNT, 0) = 0 THEN 0 ELSE ISNULL(JE_S.DMBTR_SUM, 0) END 
    + CASE WHEN ISNULL(JE_H.DMBTR_COUNT, 0) = 0 THEN 0 ELSE ISNULL(JE_H.DMBTR_SUM, 0) END)), 
    [Functional Ending Balance] 
FROM TB LEFT JOIN JE je 
ON TB.[GL Account Number ]=[HKONT]
LEFT OUTER JOIN (SELECT [HKONT], COUNT([DMBTR]) DMBTR_COUNT, SUM([DMBTR]) DMBTR_SUM  FROM JE WHERE [SHKZG]='S' GROUP BY [HKONT]) JE_S
ON [GL Account Number ] = JE_S.[HKONT]
LEFT OUTER JOIN (SELECT [HKONT], COUNT([DMBTR]) DMBTR_COUNT, SUM([DMBTR]) DMBTR_SUM  FROM JE WHERE [SHKZG]='H' GROUP BY [HKONT]) JE_H
ON [GL Account Number ] = JE_S.[HKONT]
GROUP BY [GL Account Number ], [Functional Beginning Balance], [Turnover], [Functional Ending Balance], [HKONT]

我刚刚尝试做一些快速查找并替换为您的查询。让我知道它是否适合您。

这当然可以进一步优化。不过，我会把它留给你探索。

尝试谷歌或查看有关该主题的一些Microsoft Technet详细信息以进行进一步学习。

以下是一些示例文章：

查询性能调优指南 - Microsot Technet
子查询和派生表之间的区别

我尝试匹配您在每个选择语句中运行的 4 个子查询，然后在 APPLY 语句中使用它们(根据我的经验，这对于子查询非常有效(。

SELECT 
    [GL Account Number ] AS [Account], 
    [Functional Beginning Balance] AS [Beginning balance], 
    (CASE       WHEN q1.[Count] = 0 THEN 0      ELSE q2.[Sum] END +     CASE        WHEN q4.[Count] = 0 THEN 0          ELSE q3.[Sum] END) as [Turnover TB],    [Turnover] AS [Turnover JE], 
    (   (CASE       WHEN q1.[Count] = 0 THEN 0          ELSE q2.[Sum] END +     CASE        WHEN q4.[Count] = 0 THEN 0          ELSE q3.[Sum] END) - ([Turnover])) AS [Diff], 
    ([Functional Beginning Balance] + 
    (   CASE        WHEN q1.[Count] = 0 THEN 0          ELSE q2.[Sum] END +     CASE        WHEN q4.[Count] = 0 THEN 0          ELSE q3.[Sum] END)) AS [Ending balance], 
    [Functional Ending Balance] AS [Ending balance client]
FROM TB LEFT JOIN JE je ON TB.[GL Account Number ]=[HKONT]
    OUTER APPLY (SELECT COUNT([DMBTR]) AS [Count]   FROM JE where [SHKZG]='S' AND je.[HKONT]=[GL Account Number ]) AS q1
    OUTER APPLY (SELECT SUM([DMBTR]) AS [Sum]       FROM JE where [SHKZG]='S' AND je.[HKONT]=[GL Account Number ]) AS q2
    OUTER APPLY (SELECT SUM([DMBTR]) AS [Sum]       FROM JE where [SHKZG]='H' AND je.[HKONT]=[GL Account Number ]) AS q3
    OUTER APPLY (SELECT COUNT([DMBTR]) AS [Count]   FROM JE where [SHKZG]='H' AND je.[HKONT]=[GL Account Number ]) AS q4
GROUP BY [GL Account Number ], [Functional Beginning Balance], [Turnover], [Functional Ending Balance], [HKONT]

您可能需要添加COALESCE或IS NOT NULL或> 0的组合才能完成所需的操作。

此外，为什么要在原始FROM声明中完成LEFT OUTER JOIN以进行TB和JE？每个子查询都在查看左侧外连接右侧的表字段。

获取一次总和，然后加入

declare @JEsum table (SHKZG varchar(20), GLAccountNumber varchar(20), cnt int
                      , primary key (SHKZG, GLAccountNumber));
insert into @JEsum (SHKZG, GLAccountNumber, cnt) 
select [SHKZG], [GL Account Number ], count([DMBTR]) as cnt 
from TB LEFT JOIN JE 
ON TB.[GL Account Number ]=[HKONT] 
where [SHKZG] in ('s', 'h')
group by [SHKZG], [GL Account Number ]

您的查询难以阅读。始终在列中包含表名，以便人们知道它的来源。使用没有别名的 JE 多个点会令人困惑。

相关内容

最新更新

热门标签：