Link to home
Start Free TrialLog in
Avatar of David Gerler
David GerlerFlag for United States of America

asked on

Update a summary table with values from detail records

I have a transaction table with rows for each item in every transaction (detail) including item id (sku), qty (Amount2) and cost (Amount1).
I run a SQL job to update a daily summary table with the count (numItems) and $ value (Amount) of each sku sold.

The problem I have is the summary table is getting the exact same value for numItems and Amount in every row.

My original update query was:
update PA_Product_Mix_Summary set numItems = a.num, amount = a.amount 
from (Select d.yyyymmdd, d.store, dt.Typeid as ItemType, d.sku as Item, sum(amount2) as num, sum(amount1) as Amount
from detail d full outer join @Excluded e on d.id = e.id full outer join distinctType dt on d.type = dt.type
	left outer join dbo.PA_Product_Mix_Summary pms 
		on d.store = pms.store and d.yyyymmdd = pms.yyyymmdd and d.type = dt.Type and d.sku = pms.item
where /*d.store = @STORE and */
	d.yyyymmdd = @YYYYMMDD
	and class = 'sale'
	and subclass = 'item'
	and d.[type] in ('sku','dept')
	and e.id is null
	and pms.item is not null
group by d.store, d.yyyymmdd, dt.typeid, d.sku) a 

Open in new window

This query was running 2.5 Minutes. Way too long!
I believe I was essentially updating every row in the summary table (more than 11M) with the last row of the sub query.

After searching I found this solution https://www.experts-exchange.com/questions/28963260/Update-table-field-based-on-results-from-a-query.html

I updated my query to:
update PA_Product_Mix_Summary set numItems = sum(amount2), amount = sum(amount1) 
from detail d full outer join @Excluded e on d.yyyymmdd = @YYYYMMDD and d.id = e.id full outer join distinctType dt on d.type = dt.type
	left outer join dbo.PA_Product_Mix_Summary pms 
		on d.store = pms.store and d.yyyymmdd = pms.yyyymmdd and d.type = dt.Type and d.sku = pms.item
where class = 'sale'
	and subclass = 'item'
	and d.[type] in ('sku','dept')
	and e.id is null
	and pms.item is not null
group by d.store, d.yyyymmdd, dt.typeid, d.sku

Open in new window


I'm getting an error: Incorrect syntax near the keyword 'group'.

I'm hoping the run time will be sub-second after I figure our the syntax error.
Also, I'm currently using MS SQL 2005, but will be moving to 2014. I'd like syntax that will work with both.

Any help is appreciated
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Please explain why do you need FULL OUTER JOIN operations?
Hope that you know what that means.
Can you provide PA_Product_Mix_Summary structure?
Avatar of David Gerler

ASKER

The full outer join is used to exclude certain kinds of transaction that will not have any items on them.

Create Query for PA_Product_Mix_Summary attached
create-PA_Product_Mix_Summary.sql
PA_full-job.txt
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I had to add "      and PA_Product_Mix_Summary.Item = a.Item" at the end

It is producing the results I wanted now.
Statistics are:
SQL Server parse and compile time:
   CPU time = 375 ms, elapsed time = 376 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(0 row(s) affected)

(5 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 10 ms.

SQL Server Execution Times:
   CPU time = 173 ms,  elapsed time = 24 ms.

(6 row(s) affected)
I had to add "      and PA_Product_Mix_Summary.Item = a.Item" at the end
Oh, I forgot about that field. Luckily that you could figured it out.
So if the performance is also acceptable then I think you have no issues now, right?