Solved

Bulk Updating a table from a sum() value in another table

Posted on 2011-09-10
6
259 Views
Last Modified: 2012-05-12
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
Comment
Question by:Moar
6 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 250 total points
ID: 36517197
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36517205
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
 
LVL 30

Expert Comment

by:hnasr
ID: 36517210
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Closing Comment

by:Moar
ID: 36517225
Thanks, that sped it up nicely!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36517242
It will even be better if you condition it to only update the one that have not changed ... :)
0
 
LVL 30

Expert Comment

by:hnasr
ID: 36517254
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question