SQL Server – Resolving Slow Performance of Thousands of Update Statements

sql server

I have to update stock prices in Pastel Evolution and need to create the update statements in C#, then execute the in SQL Server.

Each update statement looks like this..

UPDATE t1 
SET t1.fExclPrice = 521.95, t1.fInclPrice = 1 
FROM dbo._evPriceListPrices AS t1 
INNER JOIN dbo.StkItem AS t2 ON t1.iStockID = t2.StockLink 
WHERE t2.Code = '108085R' 
AND t1.iPriceListNameID = 1 AND t1.iWareHouseID = 5;

The problem I'm experiencing is that there are more than 14000 update statements to execute in the batch and it takes forever.

Is there a way I can speed this up?

Here is my Estimated Execution Plan.

Estimated ExecutionPlan

I could not add my Trace result here as it's too large 🙁

Best Answer

Try modifying your query so it isn't overwriting values with the same value. It will reduce logging.

UPDATE t1 
SET t1.fExclPrice = 521.95, t1.fInclPrice = 1 
FROM dbo._evPriceListPrices AS t1 
INNER JOIN dbo.StkItem AS t2 ON t1.iStockID = t2.StockLink 
WHERE t2.Code = '108085R' 
AND t1.iPriceListNameID = 1 AND t1.iWareHouseID = 5
--Dont overwrite records with the same values it generates extra logging
AND 
(
    t1.fExclPrice IS NULL
    OR t1.fExclPrice <> 521.95
    OR t1.fInclPrice IS NULL 
    OR t1.fInclPrice <> 1
)
Related Question