2

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 :(

  • 1
    Are you doing them one by one (RBAR) or are you doing this 1000 at a time? – Stijn Wynants Aug 3 '18 at 12:55
  • 14000 + at a time :( – Orion Aug 3 '18 at 12:56
  • can you make smaller batches? – Stijn Wynants Aug 3 '18 at 12:57
  • I'm considering smaller batches, maybe 1000 at a time. Just trying to find out if there is a way to speed this process up by doing them all at once. – Orion Aug 3 '18 at 12:58
  • Try doing smaller batches & check the waits on the query when they are executing. Are you hitting the right indexes etc. Can you give me the plan of this query? – Stijn Wynants Aug 3 '18 at 12:59
1

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
)
| improve this answer | |
1

insert your new prices into a temporary table

update the temp table from dbo.StkItem so that it has StockLink

the update dbo._evPriceListPrices from the temp table using a join on Stocklink

experiment with batch sizes 1000 might be faster or 100000 it depends on your server and the workload.

| improve this answer | |
  • This could have been an option if the tables were in the same database. Unfortunately we are updating prices from 1 application to another, hence 1 database to another. – Orion Aug 4 '18 at 7:41
1

I've opted to process the SQL statements in batches. Each batch has a file size of up to 500kb. This is the most I could process as the server memory is at 98%, which impacts the number of statements I can run at a time.

After doing them in batches, I found that the updates complete in 1 hour. This is a dramatic drop in time from previous methods of updating. I was using the Pastel Evolution SDK before committing raw SQL.

This still was not fast enough for me.

I then decided to add a checkbox to allow the user to select which stock items they would like to update. This will fetch and updated selected stock items only, instead of updating the entire stock table.

At the end, it comes down to CPU Memory available. If you have enough memory, then you can process larger batches. Otherwise it would be better to update records that are marked for updating only.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.