Bulk Updating a table from a sum() value in another table
Posted on 2011-09-10
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!