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

ASKER CERTIFIED SOLUTION

JestersGrind

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

Try it using the query below, but make sure there are indexes on:
PRODUCTS.ID
SALES.PRODUCTID

UPDATE  P
SET     SOLDQUANTITY = SoldQuantity
FROM    PRODUCTS p
        INNER JOIN (SELECT  PRODUCTID,
                            SUM(QUANTITY) SoldQuantity
                    FROM    SALES
                    GROUP BY PRODUCTID
                   ) S ON P.ID = S.PRODUCTID
WHERE   SOLDQUANTITY <> SoldQuantity

Open in new window

Hamed Nasr

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 )

Moar

ASKER

Thanks, that sped it up nicely!

Anthony Perkins

It will even be better if you condition it to only update the one that have not changed ... :)

Hamed Nasr

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 )