Solved

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

Posted on 2011-09-10
6
255 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access recordset not updateable 8 38
SQL JOIN 6 31
replication - alerts? 4 19
MSSQL Frequency of Years From Days Field 2 13
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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.
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now