Home Forums SQL Server 2012 SQL Server 2012 - T-SQL purging old data and outer joins RE: purging old data and outer joins

  • February 14, 2016 at 4:37 pm

    #1858692

    Sergiy (2/14/2016)

    In this case the best solution would be simply to stop the job.

    Removing 1% of the records as an ultimate goal does not make much sense. Achieving it won't make any difference at all.

    The purging would make more damage (in terms of resource consuming, locking, etc.) than any benefits it can add.

    To this, +1 Billion! Sergiy beat me to it. If you also consider the size of the other tables, it's much less that 1/2% of the data in the combined tables. It's just not worth the fuss.

    I agree... the best fix is to simply stop the purge job, in this case.

    If you absolutely insist on deleting the rows, then learn to use EXCEPT between the Address table and the other tables (starting with the largest of the other tables). You be left with a list of ALL the AddressID's that aren't used in any of the other tables and it'll only cost you one scan per column in the other tables.

    The DELETEs may still take a while if you have DRI in place for all those other tables.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)