Link to home
Start Free TrialLog in
Avatar of xenium
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:

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;

Open in new window


Can anyone suggest any improvements? I'm happy to create an intermediate table if that helps.

Thanks!
Subquery-performance.accdb
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Build a seperate query to return the min and max scores.   Then nest that (use it as a "table") in your final query.

See where that gets you.

Jim.
Avatar of xenium
xenium

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
Avatar of xenium

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
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of xenium

ASKER

aikimark - thanks good call to add an index, yes this definitely helps, I also experienced an 80% improvement
Avatar of xenium

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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial