Performance issues with UNION of large tables
<p>I have seven large tables, that can be storing between 100 to 1 million rows at any time. I'll call them <code>LargeTable1</code>, <code>LargeTable2</code>, <code>LargeTable3</code>, <code>LargeTable4</code>...<code>LargeTable7</code>. These tables are mostly static: there are no updates nor new inserts. They change only once every two weeks or once a month, when they are truncated and a new batch of registers are inserted in each.</p>

<p>All these tables have three fields in common: <code>Headquarter</code>, <code>Country</code> and <code>File</code>. <code>Headquarter</code> and <code>Country</code> are numbers in the format '000', though in two of these tables they are parsed as <code>int</code> due to some other system necessities. </p>

<p>I have another, much smaller table called <code>Headquarters</code> with the information of each headquarter. This table has very few entries. At most 1000, actually.</p>

<p>Now, I need to create a stored procedure that returns all those headquarters that appear in the large tables but are either absent in the <code>Headquarters</code> table or have been deleted (this table is deleted logically: it has a <code>DeletionDate</code> field to check this).</p>

<p>This is the query I've tried:</p>

<pre><code>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
</code></pre>

<p>This sp's performance isn't good enough for our application. It currently takes around 50 seconds to complete, with the following total rows for each table (just to give you an idea about the sizes): </p>

<ul>
<li>LargeTable1: 1516666 rows</li>
<li>LargeTable2: 645740 rows</li>
<li>LargeTable3: 1950121 rows</li>
<li>LargeTable4: 779336 rows</li>
<li>LargeTable5: 1100999 rows</li>
<li>LargeTable6: 16499 rows</li>
<li>LargeTable7: 24454 rows</li>
</ul>

<p>What can I do to improve performance? I've tried to do the following, with no much difference:</p>

<ul>
<li><strong>Inserting into the local table by batches</strong>, excluding those headquarters I've already inserted and then updating the countryFile field for those that are repeated</li>
<li><strong>Creating a view for that UNION query</strong></li>
<li><strong>Creating indexes for the LargeTables for the headquarter field</strong></li>
</ul>

<p>I've also thought about inserting these missing headquarters in a permanent table after the <code>LargeTables</code> change, but the <code>Headquarters</code> table can change more often, and I would like not having to change its module to keep these things tidy and updated. But if it's the best possible alternative, I'd go for it.</p>

<p>Thanks</p>