On Feb 15, 9:35 am, wrote:
On Feb 15, 4:22 am, "giorgio rancati"





wrote:
Hi,
The Access subquery performance is bad
You can change the subquery with a Inner Join
try this query
----
SELECT
p.businessdate, p.horizon, p.businessName, p.closeDate,
p.i_pnlValue, p.businessID, p.f_pnlValue, p.[New PnL],
COUNT(*) AS Ranking
FROM
tblCurrFI_PnL_LESS_Inv_PnL_Made_from_Query AS p
INNER JOIN
tblCurrFI_PnL_LESS_Inv_PnL_Made_from_Query as p1
ON p.horizon = p1.horizon AND
p.businessName = p1.businessName AND
p.[New PnL] = p1.[New PnL]
GROUP BY
p.businessdate, p.horizon, p.businessName, p.closeDate,
p.i_pnlValue, p.businessID, p.f_pnlValue, p.[New PnL]
ORDER BY p.horizon,p.businessName,p.[New PnL]
----

bye
--
Giorgio Rancati
[Office Access MVP]

ha scritto nel messaggionews:1171508525.698123.311400@v33g2000cwv .googlegroups.com...

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;- Hide quoted text -

- Show quoted text -

Wow. Thanks for all of the feedback. Just got into work this morning
and am going to give these options a shot.

This is my first post and I am amazed at the quick feedback. thanks.
I'll let you know how it goes- Hide quoted text -

- Show quoted text -


OK. Indexing the table and the code from Giorgio runs the query like
a champ. 10 minutes at most. Now I want to put the ranking into a
table.

I first tried an Update Query, but couldn't execute due to the
Count(*) statement.

Now I am doing a Delete/Append. The delete works easily, but the
Append runs, but returns as a Select query. It never pastes.

I then created a new append query based on the original incase there
was some aggregate function keeping from appending. I did this as a
Make table to and no matter how I set it up, it only returns as a
select. Below is the append query SQL.

Any thoughts


SELECT
qryNewPnLRank.businessdate, qryNewPnLRank.horizon,
qryNewPnLRank.businessName, qryNewPnLRank.closeDate,
qryNewPnLRank.i_pnlValue, qryNewPnLRank.businessID,
qryNewPnLRank.f_pnlValue, qryNewPnLRank.NewPnL, qryNewPnLRank.Ranking

INTO tblRankedNewinventoryPnLImpact_Made_from_Query

FROM qryNewPnLRank