Moar
asked on
Bulk Updating a table from a sum() value in another table
Hi guys,
I'm running into a performance issue in a project where I have to make hourly updates on sales figures.
The situation is as follows:
I have two tables; one with the products and one with the salesdata.
As we do frequent selects on the products based on the number of sold items, I've added a field in the products table with "SoldQuantity".
I have an hourly script doing:
UPDATE PRODUCTS P SET P.SOLDQUANTITY = (SELECT SUM(S.QUANTITY) FROM SALES S WHERE S.PRODUCTID = P.ID)
As the tables both grow, the performance is crashing. I'm sure there's a more efficient way of doing this, but I haven't been able to find it.
Hope some of you can help me along here!
Kind regards,
-Rem
I'm running into a performance issue in a project where I have to make hourly updates on sales figures.
The situation is as follows:
I have two tables; one with the products and one with the salesdata.
As we do frequent selects on the products based on the number of sold items, I've added a field in the products table with "SoldQuantity".
I have an hourly script doing:
UPDATE PRODUCTS P SET P.SOLDQUANTITY = (SELECT SUM(S.QUANTITY) FROM SALES S WHERE S.PRODUCTID = P.ID)
As the tables both grow, the performance is crashing. I'm sure there's a more efficient way of doing this, but I haven't been able to find it.
Hope some of you can help me along here!
Kind regards,
-Rem
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
One way:
Add another datetome field to PRODUCTS table to hold updatedUpTo and I assume you have a datetime field in the sales table.
Next update, just add the records with sales date greater than updatedUpTo and update the updatedUpTo field to the max datetime in the sales records included in the update.
UPDATE PRODUCTS P SET P.SOLDQUANTITY = (SELECT SUM(S.QUANTITY) FROM SALES S WHERE S.PRODUCTID = P.ID And S.dateTimeFld > P.updatedUpTo )
Add another datetome field to PRODUCTS table to hold updatedUpTo and I assume you have a datetime field in the sales table.
Next update, just add the records with sales date greater than updatedUpTo and update the updatedUpTo field to the max datetime in the sales records included in the update.
UPDATE PRODUCTS P SET P.SOLDQUANTITY = (SELECT SUM(S.QUANTITY) FROM SALES S WHERE S.PRODUCTID = P.ID And S.dateTimeFld > P.updatedUpTo )
ASKER
Thanks, that sped it up nicely!
It will even be better if you condition it to only update the one that have not changed ... :)
Good you are satisfied with the answer.
Just a correction for a missing value.
UPDATE PRODUCTS P SET P.SOLDQUANTITY = P.SOLDQUANTITY + (SELECT SUM(S.QUANTITY) FROM SALES S WHERE S.PRODUCTID = P.ID And S.dateTimeFld > P.updatedUpTo )
Just a correction for a missing value.
UPDATE PRODUCTS P SET P.SOLDQUANTITY = P.SOLDQUANTITY + (SELECT SUM(S.QUANTITY) FROM SALES S WHERE S.PRODUCTID = P.ID And S.dateTimeFld > P.updatedUpTo )
PRODUCTS.ID
SALES.PRODUCTID
Open in new window