If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Ranking performance is slow
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; |
Thread Tools | |
Display Modes | |
|
|