Post reply

UNION ALL performance issues - one table is NULL

  • July 17, 2015 at 2:04 pm

    #150919

    We have a table structure with current data in one DB / table and archived data to be moved to another DB / table combo. Currently there is a view which selects union all on both tables and we have found queries to run extremely slow. The archive table is not populated with the archive data yet and will be in early August. Currently the union all in the view is commented out until we populate the archive table after which we will need to go back to the original setup.

    This is the basic query of the view

    SELECT * FROM CurrDB.dbo.Table

    UNION ALL

    SELECT * FROM ArchiveDB.dbo.Table -- This table is currently truncated

    Why is a UNION ALL so slow with table #2 unpopulated? Once we get data into the archive table, will things speed up?

    I have searched high and low and have found no solid answer as to why a UNION ALL to a truncated table is so slow. As stated before, the table will be populated in August and Management wants to keep the UNION ALL operator in place for when the data appears

    Thanks in advance

  • July 17, 2015 at 2:25 pm

    #1813433

    There's not enough information here to give you a definitive answer.

    Could you post:

    1. The DDL for both tables

    2. The query plan produced when you do a SELECT * from <current table>

    3. The query plan produced when you query the view

    Let us know if you have questions about how to do this.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply