Post reply

Multiple Update Steps in DTS Package

  • November 4, 2008 at 10:18 am

    #195594

    Hello,

    I have a DTS package that has multiple SQL tasks like so:

    UPDATE t1

    SET t1.decription = t2.description

    FROM 1stTable t1, 2ndTable t2

    WHERE t1.code = t2.code

    There are about 9 steps total, but they are all updating the same table. The number of rows in T1 is about 20 million and T2 is a reference table containing several thousand rows at most.

    Each step is taking about 24 hours. Is there any way to speed the overall tasks or package up considerably?

    TIA,

    Bernard

  • November 4, 2008 at 11:06 am

    #893773

    I'd check the execution plans of the update queries. Sounds like there may be some missing indexes.

  • November 4, 2008 at 11:11 am

    #893774

    Lynn,

    I've tried indexing in a test environment (copied the table) and it seemed to make the situation worse--even though SQL Query Analyzer said the opposite.

    Since this is a bulk updated of the entire 16 million record table, I didn't think indexes would help much anyway.

    Any additional thoughts?

    Thanks,

    Bernard

  • November 4, 2008 at 12:26 pm

    #893818

    What do the execution plans for the updates show?

Viewing 4 posts - 1 through 3 (of 3 total)

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