I am using for The READPAST hint tells the SQL Server query engine "If you encounter any rows that are locked, just skip them... I want whatever is not currently being processed by anyone".
What columns are in sales and sales history tables?
I am updating a single table sales and there is no other sales history table. which has 11 columns like saleID,description,region etc..
UPDATE TOP(10000) SalesHistory SET SalePriceIndicator = 1 FROM dbo.Sales [SalesHistory] WITH (READPAST) WHERE [SalesHistory].[SalePriceIndicator] IS NULL Commit Go 100
My guess would be that you're scanning the entire table each time it runs. You might think about a filtered index on SalePriceIndicator where it is NULL. It may take a while to build but might make the entire process faster.
Or you might also do something with the primary key so you're only attempting to update a range of primary keys.
I'd also experiment with the batch size. I've found it often increases linearly and then kind of hockey sticks up or elbows up. I've had success with the range you're in: 1,000 or 5,000 or 10,000 rows.