Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

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

Posted on 2011-09-10
Medium Priority
272 Views
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
Question by:Moar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 21

Accepted Solution

JestersGrind earned 1000 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

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
``````
0

LVL 31

Expert Comment

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

Author Closing Comment

ID: 36517225
Thanks, that sped it up nicely!
0

LVL 75

Expert Comment

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

LVL 31

Expert Comment

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

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Acâ€¦
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses
Course of the Month9 days, left to enroll