OfficeFrustration - View Single Post

**John W. Vinson** · February 15th, 2007, 05:39 AM posted to microsoft.public.access.queries

On 14 Feb 2007 19:02:05 -0800, wrote:

I have successfully created a ranking subQuery, but the performance is
atrocious. Even after completing the query, scrolling through the
results causes it to freeze.

Here are the pertinent issues and goals

1) Local Data tbl has 490454 records consisting of 502 unique data
points for a main member (977 main members)
2) I created a ranking subquery that assigns 1-502 to each main
member's unique data points
3) It takes hours to complete (honestly it only completed when I did a
subset of half the members)

Here is the full SQL. Any help to improve the code or tips on
performance would be greatly appreciated!

SELECT
p.businessdate, p.horizon, p.businessName, p.closeDate,
p.i_pnlValue, p.businessID, p.f_pnlValue, p.[New PnL],
(SELECT COUNT(*)
FROM
tblCurrFI_PnL_LESS_Inv_PnL_Made_from_Query as p1
WHERE
p.horizon = p1.horizon AND
p.businessName = p1.businessName AND
p.[New PnL] = p1.[New PnL]) AS Ranking

FROM tblCurrFI_PnL_LESS_Inv_PnL_Made_from_Query AS p;

Wow. I'm not surprised it's slow; you're having to make 977 passes
through the subquery, querying half a million records EACH pass. This
CAN'T be done fast.

Perhaps it can be done a bit faster than it is now; do you have
indexes on businessName, horizon and [New PnL]? Might it be possible
to move the "main member" data into one table and the points into a
related table, using a Long Integer as the linking field rather than
the less efficient two-field join?

John W. Vinson [MVP]