Solved Speeding Up a Query (1 Viewer)

Weekleyba

Registered User.
Local time
Yesterday, 23:32
Joined
Oct 10, 2013
Messages
447
I have a query (BALANCE_Q 2) that's based on another query (BALANCE_Q1).
BALANCE_Q 1 runs in a split second.
BALANCE_Q 2 runs in about 8 seconds and the result is shown below. Note only 59 records.

1626616735128.png


1626616840618.png


There's no doubt that it's because of my expression for Balance, RunTotal, and Counter, are what is slowing it down. If I delete those three, the query runs in a split second.

Code:
Balance: CCur([MajorM&IFundTotal]+[BEMARFund]-DSum("[TotalPoolRequest]","[BALANCE_Q 1]","[CustomNumberForOrdering] >= " & [CustomNumberForOrdering]))
RunTotal: CCur(DSum("[TotalPoolRequest]","[BALANCE_Q 1]","[CustomNumberForOrdering] >= " & [CustomNumberForOrdering]))
Counter: DCount("ProjectID","[BALANCE_Q 1]","[CustomNumberForOrdering] >= " & [CustomNumberForOrdering])

The query generates what I want, but is there another way to accomplish this, that would speed the query up?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:32
Joined
Feb 19, 2002
Messages
33,213
It is the domain functions that are causing the slowness. Each domain function runs a SEPARATE query so you are running three queries for each row in q1. I'm pretty sure that the solution is a join rather than dLookup()'s but I'ld have to see everything to know for sure.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:32
Joined
Feb 19, 2013
Messages
13,363
perhaps use a non standard join. would need to see some data and the required outcome from that data to understand the full requirement - but the FROM part would look something like

FROM [BALANCE_Q 1] A INNER JOIN [BALANCE_Q 1] B ON A.[CustomNumberForOrdering]<B.[CustomNumberForOrdering]

note this can only be done in the sql window, the query window can't display a non standard join. But you can create the query in the query window with a standard join, then move to the sql window to change it
 

Weekleyba

Registered User.
Local time
Yesterday, 23:32
Joined
Oct 10, 2013
Messages
447
Attached is the whole database. It's a bit of mess right now but you can see the two queries I mentioned above.
I'm not understanding the non-standard join you are mentioning. I openly admit, I'm not an expert as well.
On the main form, the "All Projects" button opens a continuous form base on the BALANCE_Q 2 query.
Thanks for any and all help.
 

Attachments

  • MIRAC 1 GPA 2021-07-08 - Copy.zip
    577.1 KB · Views: 115

CJ_London

Super Moderator
Staff member
Local time
Today, 05:32
Joined
Feb 19, 2013
Messages
13,363
copy and paste this sql - seems to me it produces the same result

Code:
SELECT A.CustomNumberForOrdering, CCur([a].[MajorM&IFundTotal]+[a].[BEMARFund]-Sum([B].[TotalPoolRequest])) AS Balance, CCur(Sum([b].[TotalPoolRequest])) AS RunTotal, Count(B.ProjectID) AS [Counter], [a].[MajorM&IFundTotal]+[a].[BEMARFund] AS ALLFUNDS, A.ProjectTitle, A.CostEstimate, A.TotalPoolRequest, A.GrandTotal, A.Location_Description, A.LSWt, A.CodeWt, A.BacklogWt, A.RegAccredWt, A.ProgramWt, A.BEMAR, A.[MajorM&I], A.SmallClinic, A.NotFunded, A.Disqualified, A.NotFunded_Reason, A.ProjectID, A.FYID, A.FY, A.[IHS - Location]
FROM [BALANCE_Q 1] AS A INNER JOIN [BALANCE_Q 1] AS B ON A.CustomNumberForOrdering<B.CustomNumberForOrdering
GROUP BY A.CustomNumberForOrdering, [a].[MajorM&IFundTotal]+[a].[BEMARFund], A.ProjectTitle, A.CostEstimate, A.TotalPoolRequest, A.GrandTotal, A.Location_Description, A.LSWt, A.CodeWt, A.BacklogWt, A.RegAccredWt, A.ProgramWt, A.BEMAR, A.[MajorM&I], A.SmallClinic, A.NotFunded, A.Disqualified, A.NotFunded_Reason, A.ProjectID, A.FYID, A.FY, A.[IHS - Location]
HAVING (((A.FYID)=[Forms]![Main_F]![cboFY]))
ORDER BY Count(B.ProjectID);

you have a lot of redundant fields, data is displayed in a different order so you will need to verify
 

Weekleyba

Registered User.
Local time
Yesterday, 23:32
Joined
Oct 10, 2013
Messages
447
CJ, I copied and pasted into a new query, but it gives the following error.
What I'm I doing wrong?
1626625578366.png
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:32
Joined
Feb 19, 2013
Messages
13,363
it's your app so you should know - a form needs to be open for the query to run. I didn't investigate as to why. You'll need to change the form popup and modal properties to no to be able to test it
 

Weekleyba

Registered User.
Local time
Yesterday, 23:32
Joined
Oct 10, 2013
Messages
447
Oh my... that's embarrassing. I know exactly why. Thank you!
That change speeds it way up!
Queries are still the most puzzling to me. They seem so easy but there are more things ways to develop them then you can count.
I need to learn more!! I certainly do not understand this non-standard join and why it speeds the query up so much.
Thanks again CJ.
 

June7

AWF VIP
Local time
Yesterday, 20:32
Joined
Mar 9, 2014
Messages
3,669
Non-standard join because using < instead of =.
Query is faster because not use domain aggregate function.
Non-standard join can still perform slower than standard join.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:32
Joined
Feb 19, 2002
Messages
33,213
Non-standard join can still perform slower than standard join.
But significantly better than hundreds of unnecessary queries. Add the q1 to the grid a second time. Draw a join line between the custom number fields. Switch the query to SQL view and change the "=" in the join to "<=". Then use SQL functions instead of domain functions to do the summing.

Of course the fact that the second query isn't actually doing any aggregation is suspicious.
 

Weekleyba

Registered User.
Local time
Yesterday, 23:32
Joined
Oct 10, 2013
Messages
447
As I look at this query more closely it's not working right.
1626657089826.png

There should be 59 projects but the query shows 58.
The highest scoring project, which happens to be ProjectID 1, is missing. ProjectTitle "Replace Generators & ATSs" for $750,000.
But notice the RunTotal is counting it. ??
Below is the old query that is correct. (but runs slow)
What is the issue with this?
1626657210124.png
 
Last edited:

Weekleyba

Registered User.
Local time
Yesterday, 23:32
Joined
Oct 10, 2013
Messages
447
copy and paste this sql - seems to me it produces the same result

Code:
SELECT A.CustomNumberForOrdering, CCur([a].[MajorM&IFundTotal]+[a].[BEMARFund]-Sum([B].[TotalPoolRequest])) AS Balance, CCur(Sum([b].[TotalPoolRequest])) AS RunTotal, Count(B.ProjectID) AS [Counter], [a].[MajorM&IFundTotal]+[a].[BEMARFund] AS ALLFUNDS, A.ProjectTitle, A.CostEstimate, A.TotalPoolRequest, A.GrandTotal, A.Location_Description, A.LSWt, A.CodeWt, A.BacklogWt, A.RegAccredWt, A.ProgramWt, A.BEMAR, A.[MajorM&I], A.SmallClinic, A.NotFunded, A.Disqualified, A.NotFunded_Reason, A.ProjectID, A.FYID, A.FY, A.[IHS - Location]
FROM [BALANCE_Q 1] AS A INNER JOIN [BALANCE_Q 1] AS B ON A.CustomNumberForOrdering<B.CustomNumberForOrdering
GROUP BY A.CustomNumberForOrdering, [a].[MajorM&IFundTotal]+[a].[BEMARFund], A.ProjectTitle, A.CostEstimate, A.TotalPoolRequest, A.GrandTotal, A.Location_Description, A.LSWt, A.CodeWt, A.BacklogWt, A.RegAccredWt, A.ProgramWt, A.BEMAR, A.[MajorM&I], A.SmallClinic, A.NotFunded, A.Disqualified, A.NotFunded_Reason, A.ProjectID, A.FYID, A.FY, A.[IHS - Location]
HAVING (((A.FYID)=[Forms]![Main_F]![cboFY]))
ORDER BY Count(B.ProjectID);

you have a lot of redundant fields, data is displayed in a different order so you will need to verify
Ok, I found it. Need to add a "=". (...A.CustomNumberForOrdering<=B.CustomNumberForOrdering...)

Code:
SELECT [A].CustomNumberForOrdering, CCur([A].[MajorM&IFundTotal]+[A].[BEMARFund]-Sum([B].[TotalPoolRequest])) AS Balance, CCur(Sum([B].[TotalPoolRequest])) AS RunTotal, Count([B].[ProjectID]) AS [Counter], [A].[MajorM&IFundTotal]+[A].[BEMARFund] AS ALLFUNDS, A.ProjectTitle, A.CostEstimate, A.TotalPoolRequest, A.GrandTotal, A.Location_Description, A.LSWt, A.CodeWt, A.BacklogWt, A.RegAccredWt, A.ProgramWt, A.BEMAR, A.[MajorM&I], A.SmallClinic, A.NotFunded, A.Disqualified, A.NotFunded_Reason, A.ProjectID, A.FYID, A.FY, A.[IHS - Location]
FROM [BALANCE_Q 1] AS A INNER JOIN [BALANCE_Q 1] AS B ON A.CustomNumberForOrdering<=B.CustomNumberForOrdering
GROUP BY A.CustomNumberForOrdering, [a].[MajorM&IFundTotal]+[a].[BEMARFund], A.ProjectTitle, A.CostEstimate, A.TotalPoolRequest, A.GrandTotal, A.Location_Description, A.LSWt, A.CodeWt, A.BacklogWt, A.RegAccredWt, A.ProgramWt, A.BEMAR, A.[MajorM&I], A.SmallClinic, A.NotFunded, A.Disqualified, A.NotFunded_Reason, A.ProjectID, A.FYID, A.FY, A.[IHS - Location]
HAVING (((A.FYID)=[Forms]![Main_F]![cboFY]))
ORDER BY Count(B.ProjectID);
 

Weekleyba

Registered User.
Local time
Yesterday, 23:32
Joined
Oct 10, 2013
Messages
447
Oh…so much faster!
I’ve been a little scared to dig into using the SQL view for queries, so this really helped me get introduced to it.
Not as scary as it first appeared.
Thanks to you guys.
 

Weekleyba

Registered User.
Local time
Yesterday, 23:32
Joined
Oct 10, 2013
Messages
447
And indexing is just done in the table, per field, by setting the indexing field?
With the exception of auto numbers, do you normally choose to index with Yes (duplicates ok)?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:32
Joined
Feb 19, 2013
Messages
13,363
The index type is based on the requirements of your app. Auto numbers are used for the pk to uniquely identify a record, so a) you don’t want duplicates and b) it must be populated.

a customer name can’t be unique - you could have two John Doe’s, if not now, then perhaps in the future, so it can have duplicates and is probably required
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:32
Joined
Feb 19, 2002
Messages
33,213
If your BE is Jet/ACE be aware that Access automatically creates a hidden index for each FK.
 

Users who are viewing this thread

Top Bottom