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
6
Medium Priority
?
272 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
[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
  • Learn & ask questions
6 Comments
 
LVL 21

Accepted Solution

by:
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

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 31

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 31

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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.

604 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