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; |
#3
|
|||
|
|||
Ranking performance is slow
|
#4
|
|||
|
|||
Ranking performance is slow
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 messaggio oups.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; |
#5
|
|||
|
|||
Ranking performance is slow
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 |
#6
|
|||
|
|||
Ranking performance is slow
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 |
#7
|
|||
|
|||
Ranking performance is slow
On Feb 15, 5:36 pm, wrote:
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- Hide quoted text - - Show quoted text - Anyone had this happen before?? |
#8
|
|||
|
|||
Ranking performance is slow
Are you executing the query or simply switching to the datasheet view?
If you click the run button (Red exclamation) or select Query: Run the query should execute and create the table. If you switch to datasheet view, you will see what WOULD be created if you ran (executed) the query. In other words, a select query view of the existing data in the existing table. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. wrote in message oups.com... 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 |
#9
|
|||
|
|||
Ranking performance is slow
On Feb 16, 11:27 am, "John Spencer" wrote:
Are you executing the query or simply switching to the datasheet view? If you click the run button (Red exclamation) or select Query: Run the query should execute and create the table. If you switch to datasheet view, you will see what WOULD be created if you ran (executed) the query. In other words, a select query view of the existing data in the existing table. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . wrote in message oups.com... 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- Hide quoted text - - Show quoted text - I was executing. I looked up another thread and they said that the action queries can get corrupted if they are switched often. I deleted it and recreated it and it is working fine. Thanks everyone for their help. |
Thread Tools | |
Display Modes | |
|
|