INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Complex and thus slow query

Complex and thus slow query

(OP)
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:

CODE -->

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

RE: Complex and thus slow query

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.

RE: Complex and thus slow query

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 winky smile

Duane
Hook'D on Access
MS Access MVP

RE: Complex and thus slow query

(OP)
... 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

RE: Complex and thus slow query

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close