Suggestions to better optimize percentile query?

[templeowls]

I have a table of complaints. There's a category field and dollar amount field. I created a query to group by category and then calculate the 10th percentile dollar value for each category by using a subquery. Below is the SQL. It works but runs very slow when opening. There's only 1,000 total records so I'm thinking my SQL just isn't designed well.



Any suggestions on how to better optimize it?

SELECT tblComplaints.ComplaintType, Max(tblComplaints.[DollarAmount]) AS 10thPercentileFROM tblComplaints
WHERE (((tblComplaints.[DollarAmount]) In (SELECT TOP 10 PERCENT [DollarAmount]          FROM tblComplaints AS sub          WHERE sub.ComplaintType= tblComplaints.ComplaintType         ORDER BY [DollarAmount] ASC     )))
GROUP BY tblComplaints.ComplaintType;

[Minty]

Have you tried saving the sub query as a separate saved query and joining on it?

My understanding is that Access doesn't optimise sub-queries very well if at all.

[templeowls]

Very dumb question, but how would I do that? Not sure given the where statement in the subquery

[Micron]

I agree. IIRC that sub query will run once for every record in the main query, and it is made worse when the sub query contains aggregate functions.

[Minty]

Unusually, I can't quite decipher the overall purpose of the query from reading its SQL (possibly because it is late afternoon on a Friday here!)

I think you would need to use

SELECT TOP 10 PERCENT [DollarAmount], RiskGroup FROM tblComplaints ORDER BY [DollarAmount] ASC

As your saved query but I'm not 100% certain.

[templeowls]

Unusually, I can't quite decipher the overall purpose of the query from reading its SQL (possibly because it is late afternoon on a Friday here!)

I think you would need to use

SELECT TOP 10 PERCENT [DollarAmount], RiskGroup FROM tblComplaints ORDER BY [DollarAmount] ASC

As your saved query but I'm not 100% certain.

Yea this was what I tried :/. It doesn't work because it's filtering to the top 10 percent across the entire table. Not top 10 percent specific to each ComplaintType (ignore 'riskgroup'; that was an error on my part in my OP).

I almost need to create a subquery for each complainttype and then join them all, but there's 60-70 different complainttypes :/

[CJ_London]

You could try indexing dollar amount or include the pk in your sub query and join on that instead of the dollar amount.

[CJ_London]

What I meant was

SELECT ComplaintType, Max([DollarAmount]) AS 10thPercentile
FROM tblComplaints
WHERE PK In (SELECT TOP 10 PERCENT PK FROM tblComplaints AS sub          
                           WHERE sub.ComplaintType= tblComplaints.ComplaintType
                           ORDER BY [DollarAmount] ASC)

GROUP BY ComplaintType;