• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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
0
Moar
Asked:
Moar
1 Solution
 
JestersGrindCommented:
A join might be more efficient than a subquery like this:

UPDATE P SET P.SOLDQUANTITY = SUM(S.QUANTITY)
FROM PRODUCTS P INNER JOIN SALES S ON P.ID = S.PRODUCTID

Greg

0
 
Anthony PerkinsCommented:
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

0
 
hnasrCommented:
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 )
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
MoarAuthor Commented:
Thanks, that sped it up nicely!
0
 
Anthony PerkinsCommented:
It will even be better if you condition it to only update the one that have not changed ... :)
0
 
hnasrCommented:
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 )
 
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now