Cross Tab query takes long time to execute

[Meh]

Hello

I have the below cross tab query which takes 4-5 minutes to execute,


TRANSFORM Sum([1].[8 oz Case Equivalents]) AS [SumOf8 oz Case Equivalents]
SELECT [1].[HFM Country], [1].[ENTITY DETAIL], [1].[Product Detail], [1].[Container Detail]
FROM 1
GROUP BY [1].[HFM Country], [1].[ENTITY DETAIL], [1].[Product Detail], [1].[Container Detail]
PIVOT [1].Period;


The table [1] is a query output which concatenates 2 columns from the Volumes table as below, Volumes table only has 50,000 records.

SELECT Volumes.[Month Map] & "" & "-" & "" & Year.YearMap AS Period, Volumes.[HFM Country], Volumes.[ENTITY DETAIL], Volumes.[Product Detail], Volumes.[Container Detail], Volumes.[8 oz Case Equivalents]
FROM Volumes INNER JOIN [Year] ON Volumes.[Year Display]=Year.YearDisplay
GROUP BY Volumes.[Month Map], Year.YearMap, Volumes.[HFM Country], Volumes.[ENTITY DETAIL], Volumes.[Product Detail], Volumes.[Container Detail], Volumes.[8 oz Case Equivalents];


Please help me out as to why it takes so long to execute,

Thanks in advance.

[NTC]

how long does the table1 query take - all by itself?

[Meh]

30 seconds

[NTC]

well that's not quick either - is it. but probably ok. lets assume you are not running on minimal RAM...although a test on a more powerful PC is something you can consider.....

what I would suggest then is to make a query to write the Q1 results into a new temp table.

next, resource your crosstab so the record source is this new temp table.

this is an experiment, but it won't take much time - - I am thinking that the xtab pulling from a table should be much quicker; because it is not waiting on the Q1...

will be interested to what you find....