Long running query on a self joined table

I try to improve the performance of a query which updates a coloumn on each row of a table, by comparing the actual row's values with all other rows in the same table. Here is the query:

update  F set   
    PartOfPairRC = 1
from    RangeChange F
where     Reject=0
and exists( 
            select 1 from RangeChange S
            where   F.StoreID = S.StoreID
            and F.ItemNo = S.ItemNo
            and       F.Reject = S.Reject
            and F.ChangeDateEnd = S.ChangeDate - 1)

The query's performance degrades rapidly as the number of rows in the table incre开发者_如何学Goases. I have 50 millon rows in the table.

Is there a better way to do this? Would SSIS be able to handle such an operation better?

Any help much appreciated, thanks Robert

You can try to create a index on that table:

create index idx_test on RangeChange(StoreID, ItemNo, Reject, ChangeDateEnd) where reject = 0

--when you are not using the SQL enterprise get rid of the where condition in the index and put the reject column as included column in the index --make sure you have a clustered index already on the table (when not you can create the index above as clustered)

-- I would write the query as a join:

update F set
   F.PartOfPairRC = 1
from  RangeChange F
   join RangeChange S
      on F.StoreID = S.StoreID
         and F.ItemNo = S.ItemNo
         and F.Reject = S.Reject
         and F.ChangeDateEnd = S.ChangeDate - 1
where F.Reject=0 and S.Reject = 0