Complex and thus slow query

[georgesOne]

Dear All,

I have a query which produces the results I want, but it takes forever (ca. 25 secs on my pretty fast machine). Each line is repeated 15 times for data of different years:

SELECT qryActNENK.PID, 
Sum([CRev2015]/1.29) AS [WS-Rev-2015], (x15)
Sum([CRev2015]/1.29*CProb) AS [WS-Rev-RA-2015], (x15)
IIf(Sum([CRev2015])=0,0,Sum([CRev2015]/1.29*CProb)/Sum([CRev2015]/1.29)) AS [WS-RAdj-2015], (x15)
[WS-Rev-2015]/[WS-Lay-2015]/[WS-DV-2015]/[WS-P-2015]*1000000 AS [WS-WS-2015], (x15)
IIf(IsNull(Sum(WS2015)),Sum(Lay)/Count(Lay),Sum(Lay*WS2015)/Sum(WS2015)) AS [WS-Lay-2015], (x15)
IIf(IsNull(Sum(WS2015)),Sum(DV)/Count(DV),Sum(DV*WS2015)/Sum(WS2015)) AS [WS-DV-2015], (x15)
IIf(IsNull(Sum(WS2015)),Sum([P$L])/1.29/Count([P$L]),Sum([P$L]/1.29*WS2015)/Sum(WS2015)) AS [WS-P-2015] (x15)
FROM qryActNENK
WHERE NOT ISNULL(qryActNENK.PID)
GROUP BY qryActNENK.PID;

I understand that it is a lot of data to crunch, but I wonder if there is a way to make it faster?

Any suggestion is welcome, goergesOne

 

[lameid]

If you could somehow group by year and do the other aggregates, you should get the correct results. I'm assuming WS2015 is a calculated field in qryActNENK and you could get to the underlying data table and do the same.

You might consider using a pivot table to display your data if the standard reports are not getting you what you want... Probably dump or link the data in Excel and use an Excel Pivot Table. Someone recently showed my an Access Pivot and it was slow.

 

[dhookom]

I wonder if your data was normalized if it might be more efficient. Any time I see data stored in column names, it causes me to shudder ;-)

Duane
Hook'D on Access
MS Access MVP

 

[georgesOne]

... Agreed...
This is the quick 1:1 transformation of an excel sheet that is about going out of control (to demonstrate that Access may be better suited).
So normalization will be a next step, when people agree to transfer things.
Until then I use this as a make-table query, using the table for fast access and which will be occasionally updated.
Thank you, georgesOne

 

[lameid]

It may be worth the effort to write the queries to normalize it and save them for when it is approved and then turn around and write the demo off the normalized tables. That is the only true gauge of performance is to do it and compare apples to apples. Ultimately you are closer to the situation and know whether it is worthwhile to do now.