Query boggs down with large datasets 1

[SanAntonioSpurFan]

I have a append query that runs great when there is 500 records but boggs down when there are more than 1000 records. Here's what the query does.

Fields:
Gen Code, UPC12, Brand, Description, Sales, ACV. There are about 40 fields for sales. They are different time frames.

The query does a couple of things. It look at UPC12 for duplicates and if it finds a dup, it sums sales for the dup records and takes the max for ACV. It also looks at Gen Code and takes the hight value to pick the correct Brand and Description.

Here the SQL the query produced....Sorry for the long code but most is duplicate info for each of the 40 sales fields.

Any way to speed up the query? Do I need all this for each field?


INSERT INTO RawData2 ( Dataset, [UPC Code], [Original Brand], Description, G1Dol52, G1Dol24, G1Dol12, G1Dol4, G1DolChg52, G1DolChg24, G1DolChg12, G1DolChg4, G1Un52, G1Un24, G1Un12, G1Un4, G1UnChg52, G1UnChg24, G1UnChg12, G1UnChg4, G1ACV52, G1ACV24, G1ACV12, G1ACV4, G2Dol52, G2Dol24, G2Dol12, G2Dol4, G2DolChg52, G2DolChg24, G2DolChg12, G2DolChg4, G2Un52, G2Un24, G2Un12, G2Un4, G2UnChg52, G2UnChg24, G2UnChg12, G2UnChg4, G2ACV52, G2ACV24, G2ACV12, G2ACV4 )
SELECT a.Dataset, a.[UPC Code 12], a.[Original Brand], a.Description, (select sum(G1Dol52)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1Dol52, (select sum(G1Dol24)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1Dol24, (select sum(G1Dol12)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1Dol12, (select sum(G1Dol4)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1Dol4, (select sum(G1DolChg52)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1DolChg52, (select sum(G1DolChg24)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1DolChg24, (select sum(G1DolChg12)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1DolChg12, (select sum(G1DolChg4)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1DolChg4, (select sum(G1Un52)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1Un52, (select sum(G1Un24)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1Un24, (select sum(G1Un12)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1Un12, (select sum(G1Un4)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1Un4, (select sum(G1UnChg52)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1UnChg52, (select sum(G1UnChg24)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1UnChg24, (select sum(G1UnChg12)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1UnChg12, (select sum(G1UnChg4)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1UnChg4, (select max(G1ACV52)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1ACV52, (select max(G1ACV24)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1ACV24, (select max(G1ACV12)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1ACV12, (select max(G1ACV4)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G1ACV4, (select sum(G2Dol52)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2Dol52, (select sum(G2Dol24)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2Dol24, (select sum(G2Dol12)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2Dol12, (select sum(G2Dol4)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2Dol4, (select sum(G2DolChg52)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2DolChg52, (select sum(G2DolChg24)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2DolChg24, (select sum(G2DolChg12)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2DolChg12, (select sum(G2DolChg4)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2DolChg4, (select sum(G2Un52)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2Un52, (select sum(G2Un24)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2Un24, (select sum(G2Un12)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2Un12, (select sum(G2Un4)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2Un4, (select sum(G2UnChg52)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2UnChg52, (select sum(G2UnChg24)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2UnChg24, (select sum(G2UnChg12)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2UnChg12, (select sum(G2UnChg4)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2UnChg4, (select max(G2ACV52)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2ACV52, (select max(G2ACV24)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2ACV24, (select max(G2ACV12)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2ACV12, (select max(G2ACV4)from RawData where [UPC Code 12] = a.[UPC Code 12]) AS G2ACV4
FROM RawData AS a
WHERE (((a.[Generation Code]) In (select max([Generation Code]) from RawData where [UPC Code 12] = a.[UPC Code 12])));

 

[Golom]

Bogs down? ... I'm suprised that it runs at all!! You have 40 subqueries in the select part of your statement. Can you do this with a grouped query something like
[blue][tt]
INSERT INTO ... etc. ...

SELECT Dataset, [UPC Code 12], [Original Brand], Description,
sum(G1Dol52) AS G1Dol52,
sum(G1Dol24) AS G1Dol24,
sum(G1Dol12) AS G1Dol12,
.... etc. ...

FROM RawData AS a

WHERE [Generation Code] =
(Select MAX([Generation Code])
from RawData
where [UPC Code 12] = a.[UPC Code 12])

GROUP BY Dataset, [UPC Code 12], [Original Brand], Description
[/tt][/blue]

 

[SanAntonioSpurFan]

Golom,

Thanks for the trip. Question, I killed the sub queries and everything works except the Sum's and Max's.

For example, The Dataset I ran had 155 records. There were only 3 duplicates based on UPC Code 12. The results returned 152 records and picked the correct values based off Gen Code for Brand and Description. That parts works. However, the query did not sum the sales for each time frame for the 3 duplicate upc's. It only picked the sales for the highest Gen Code. Any clue? Here's the new SQL I have. Thanks for your HELP!

INSERT INTO RawData2 ( Dataset, [UPC Code], [Original Brand], Description, G1Dol52, G1Dol24, G1Dol12, G1Dol4, G1DolChg52, G1DolChg24, G1DolChg12, G1DolChg4, G1Un52, G1Un24, G1Un12, G1Un4, G1UnChg52, G1UnChg24, G1UnChg12, G1UnChg4, G1ACV52, G1ACV24, G1ACV12, G1ACV4, G2Dol52, G2Dol24, G2Dol12, G2Dol4, G2DolChg52, G2DolChg24, G2DolChg12, G2DolChg4, G2Un52, G2Un24, G2Un12, G2Un4, G2UnChg52, G2UnChg24, G2UnChg12, G2UnChg4, G2ACV52, G2ACV24, G2ACV12, G2ACV4 )

SELECT a.Dataset, a.[UPC Code 12], a.[Original Brand], a.Description, Sum(a.G1Dol52) AS G1Dol52, Sum(a.G1Dol24) AS G1Dol24, Sum(a.G1Dol12) AS G1Dol12, Sum(a.G1Dol4) AS G1Dol4, Sum(a.G1DolChg52) AS G1DolChg52, Sum(a.G1DolChg24) AS G1DolChg24, Sum(a.G1DolChg12) AS G1DolChg12, Sum(a.G1DolChg4) AS G1DolChg4, Sum(a.G1Un52) AS G1Un52, Sum(a.G1Un24) AS G1Un24, Sum(a.G1Un12) AS G1Un12, Sum(a.G1Un4) AS G1Un4, Sum(a.G1UnChg52) AS G1UnChg52, Sum(a.G1UnChg24) AS G1UnChg24, Sum(a.G1UnChg12) AS G1UnChg12, Sum(a.G1UnChg4) AS G1UnChg4, Max(a.G1ACV52) AS G1ACV52, Max(a.G1ACV24) AS G1ACV24, Max(a.G1ACV12) AS G1ACV12, Max(a.G1ACV4) AS G1ACV4, Sum(a.G2Dol52) AS G2Dol52, Sum(a.G2Dol24) AS G2Dol24, Sum(a.G2Dol12) AS G2Dol12, Sum(a.G2Dol4) AS G2Dol4, Sum(a.G2DolChg52) AS G2DolChg52, Sum(a.G2DolChg24) AS G2DolChg24, Sum(a.G2DolChg12) AS G2DolChg12, Sum(a.G2DolChg4) AS G2DolChg4, Sum(a.G2Un52) AS G2Un52, Sum(a.G2Un24) AS G2Un24, Sum(a.G2Un12) AS G2Un12, Sum(a.G2Un4) AS G2Un4, Sum(a.G2UnChg52) AS G2UnChg52, Sum(a.G2UnChg24) AS G2UnChg24, Sum(a.G2UnChg12) AS G2UnChg12, Sum(a.G2UnChg4) AS G2UnChg4, Max(a.G2ACV52) AS G2ACV52, Max(a.G2ACV24) AS G2ACV24, Max(a.G2ACV12) AS G2ACV12, Max(a.G2ACV4) AS G2ACV4

FROM RawData AS a

WHERE (((a.[Generation Code])=(Select MAX( [Generation Code]) from RawData where [UPC Code 12] = a.[UPC Code 12])))

GROUP BY a.Dataset, a.[UPC Code 12], a.[Original Brand], a.Description;

 

[Golom]

That's what your SQL is telling it to do. Your WHERE clause says
[blue][tt]
WHERE [Generation Code] =
(Select MAX( [Generation Code]) from RawData
where [UPC Code 12] = a.[UPC Code 12])
[/tt][/blue]
So it's only picking up that one record (i.e. the MAX([Generation Code])). If you want all of them then remove the restriction.

 

[SanAntonioSpurFan]

Golom,

I want the query to pick the correct Brand and Description if there is a duplicate UPC based off the MAX Gen Code. It currently does that. For any duplicate records, based on UPC Code 12, I want the query to SUM all the sales fields for the duplicates. For Example:

UPC Code 12,GEN Code,Brand,Desc,Sales
001,01,BrandA,DescA,100
001,02,BrandB,DescB,150

I want the query to produce the following:
001,02"Really Don't Need Gen Code,BrandB,DescB,250

Does that make sense?

Do I need to change something in the WHERE Clause?

Thanks for your HELP!

 

[Golom]

Try something like
[blue][tt]
SELECT B.Dataset, a.[UPC Code 12], B.[Original Brand], B.Description,
.... Other Fields ....

FROM RawData As a INNER JOIN RawData As B ON a.[UPC Code 12] = B.[UPC Code 12]

WHERE B.[Generation Code] =
(Select MAX( [Generation Code]) from RawData
where [UPC Code 12] = a.[UPC Code 12])

GROUP BY B.Dataset, a.[UPC Code 12], B.[Original Brand], B.Description
[/tt][/blue]

 

[SanAntonioSpurFan]

That did it! It works perfect!

There a reason your at the top of the list! Thanks sooooo much for the help!