SQLTeam.com | Weblogs | Forums

Update query


#1

I would like to update the table in single run which has 500 millions records without lock table..

I had come up the below query but need to run many times and is slow..

UPDATE TOP(10000) SalesHistory
SET SalePriceIndicator = 1
FROM Sales WITH (READPAST)
WHERE SalePriceIndicator IS NULL
Commit
Go 100

Please let me know if there are any better solution or query to update.

Thanks for your help in advance..


#2

Are you using Standard or Enterprise Esition?

What were you using the READPAST hint for?

What columns are in sales and sales history tables?


#3

Thanks for your response..

Are you using Standard or Enterprise Esition?

I am using the enterprise edition

What were you using the READPAST hint for?

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


#4

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.