sql - 大表 UNION 的性能问题

Question

我有七个大表，随时可以存储 100 到 100 万行。我将它们称为 LargeTable1、LargeTable2、LargeTable3、LargeTable4...LargeTable7。这些表大多是静态的:没有更新也没有新的插入。它们每两周或每月仅更改一次，当它们被截断并在每个寄存器中插入新的一批寄存器时。

所有这些表都有三个共同的字段:Headquarter、Country 和File。 Headquarter 和 Country 是格式为“000”的数字，尽管在其中两个表中，由于其他一些系统要求，它们被解析为 int .

我有另一个小得多的表，名为 Headquarters，其中包含每个总部的信息。该表的条目很少。实际上，最多 1000 个。

现在，我需要创建一个存储过程，返回出现在大表中但在 Headquarters 表中不存在或已被删除的所有那些总部(此表在逻辑上被删除:它有一个 DeletionDate 字段来检查这个)。

这是我试过的查询:

CREATE PROCEDURE deletedHeadquarters
AS
BEGIN
    DECLARE @headquartersFiles TABLE
    (
        hq int,
        countryFile varchar(MAX)
    );

    SET NOCOUNT ON

    INSERT INTO @headquartersFiles
    SELECT headquarter, CONCAT(country, ' (', file, ')')
    FROM
    (
        SELECT DISTINCT CONVERT(int, headquarter) as headquarter,
                        CONVERT(int, country) as country,
                        file
        FROM            LargeTable1     
        UNION
        SELECT DISTINCT headquarter,
                        country,
                        file
        FROM            LargeTable2
        UNION
        SELECT DISTINCT headquarter,
                        country,
                        file
        FROM            LargeTable3
        UNION
        SELECT DISTINCT headquarter,
                        country,
                        file
        FROM            LargeTable4
        UNION
        SELECT DISTINCT headquarter,
                        country,
                        file
        FROM            LargeTable5
        UNION
        SELECT DISTINCT headquarter,
                        country,
                        file
        FROM            LargeTable6
        UNION
        SELECT DISTINCT headquarter,
                        country,
                        file
        FROM            LargeTable7
    ) TC

    SELECT  RIGHT('000' + CAST(st.headquarter AS VARCHAR(3)), 3) as headquarter,
            MAX(s.deletionDate) as deletionDate,
            STUFF
            (
                (SELECT DISTINCT ', ' + st2.countryFile
                FROM @headquartersFiles st2
                WHERE st2.headquarter = st.headquarter
                FOR XML PATH('')),
                1,
                1,
                ''
            ) countryFile
    FROM    @headquartersFiles as st
    LEFT JOIN headquarters s ON CONVERT(int, s.headquarter) = st.headquarter
    WHERE   s.headquarter IS NULL
       OR   s.deletionDate IS NOT NULL
    GROUP BY st.headquarter

END

这个 sp 的性能对我们的应用程序来说不够好。目前大约需要 50 秒才能完成，每个表的总行数如下(只是为了让您了解大小):

• 大表 1:1516666 行
• 大表 2:645740 行
• 大表 3:1950121 行
• 大表 4:779336 行
• 大表 5:1100999 行
• LargeTable6:16499 行
• LargeTable7:24454 行

我可以做些什么来提高性能？我尝试执行以下操作，但没有太大区别:

• 批量插入本地表，排除我已经插入的那些总部，然后为那些重复的更新countryFile字段
• 为该 UNION 查询创建 View
• 为总部领域的 LargeTables 创建索引

我也考虑过在 LargeTables 更改后将这些缺失的总部插入永久表中，但是 Headquarters 表可以更频繁地更改，我不想这样做不得不改变它的模块来保持这些东西的整洁和更新。但如果这是最好的选择，我会选择它。

谢谢

Answer 1

采用这个过滤器

LEFT JOIN headquarters s ON CONVERT(int, s.headquarter) = st.headquarter
WHERE   s.headquarter IS NULL
   OR   s.deletionDate IS NOT NULL

并将其添加到联合中的每个单独查询并插入到@headquartersFiles

看起来这会产生更多的过滤器，但它实际上会加快处理速度，因为您在开始作为联合处理之前进行过滤。

同时取出你所有的 DISTINCT，它可能不会加快速度，但它看起来很愚蠢，因为你正在做一个 UNION 而不是 UNION all。