xenium
asked on
Improving performance of a query that uses a subquery
hi,
I'm looking to improve the performance of a query. The query reads from a single table "Org_Score" (fields: AN, OrgID, Score) AN is an Autonumber field.
Here is the query, also attached in an example MS Access db:
Can anyone suggest any improvements? I'm happy to create an intermediate table if that helps.
Thanks!
Subquery-performance.accdb
I'm looking to improve the performance of a query. The query reads from a single table "Org_Score" (fields: AN, OrgID, Score) AN is an Autonumber field.
Here is the query, also attached in an example MS Access db:
SELECT Org_Score2.OrgID, Org_Score2.Score, 1+(SELECT Count(*) FROM Org_Score WHERE Org_Score.AN<Org_Score2.AN) AS Rank, [Rank] AS OriginalRank, Round(([Score]-[MinScore])/([MaxScore]-[MinScore])*100,3) AS NormalScore, (SELECT min(Score) from Org_Score) AS MinScore, (SELECT max(Score) from Org_Score) AS MaxScore INTO Org_Rank
FROM Org_Score AS Org_Score2
WHERE ((Not (Org_Score2.Score) Is Null))
ORDER BY Org_Score2.Score DESC;
Can anyone suggest any improvements? I'm happy to create an intermediate table if that helps.
Thanks!
Subquery-performance.accdb
ASKER
Thanks for the suggestion. However that didn't make any difference.
Attached an updated file with this example implemented.
See mod_Log to run tests with timer to 100th of second.
Subquery-performance.accdb
Attached an updated file with this example implemented.
See mod_Log to run tests with timer to 100th of second.
Subquery-performance.accdb
ASKER
PS have just tried the same hard-coding those values with no difference, so that aspect is not an issue then.
I assume the issue is just the complex subquery:
1+(SELECT Count(*) FROM Org_Score WHERE Org_Score.AN<Org_Score2.AN ) AS Rank
I assume the issue is just the complex subquery:
1+(SELECT Count(*) FROM Org_Score WHERE Org_Score.AN<Org_Score2.AN
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
aikimark - thanks good call to add an index, yes this definitely helps, I also experienced an 80% improvement
ASKER
Apologies all - i realise this db is broken too (not doing what it's meant to do). Let me tidy up and then try to clarify. Thanks again
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
See where that gets you.
Jim.