This is the problem: I have 22000 records in a table.
The data is as follows: AgentName, ClientID, ContractNo, TransactionType, NetPayable The way the relationships work is that for each Client, there is at least one Agent, and at most 2. In the cases that there are 2 agents per Client, the Contract Numbers will be the same. The report is displayed, broken down by Agents. This is a commission report for the Agents. What the report does is tally up the NetPayables for each agent, broken down by Client. However, if the client is producing less than 5 Contracts, then we apply a formula to the agent's commission, and then check if there is another agent assigned to the contract, and if necessary split the adjustment between the two. So I had an initial query which Grouped By Client, Agent, then Contract (so this is the 3 level nested group output). I then created a Client Array (2 dimensional). So the first Dimension is per Dealer, and the second dimension holds the details. The Details are: 1 Client Name 2 Number of Contracts (just a loop counter which keeps track of contracts per Client) 3 ContractNo (this is actually a list of the ContractNo) 4 AgentName/NetPayable (another list comprised of the AgentName and their NetPayable, mostly a 1 item list, but can be 2 items). Once that output query is finished, I loop through the array that was just created, and split it into 2. Under 5 and Over 5. I basically loop through the array and look at the 2nd position in the 2nd dimension and see if that value is > 5 or <= 5. Based on that I dump the contents in either the Under5 array or the over5 array. Once this is done, I loop through the Under5 array, to create a value list of all the ContractNo's. I then do a query, where the ContractNo's are in this value list. Now we're ready for the output. For the output, we output the last query I just mentioned. The output is by Agent, within the Agent we break it down by ClientID, and then within each ClientID section we list the actual row data. It's during this output section where I apply the formula to the Agent's commission rate. If you see item 4 above, I check if that array position is actually a list whose length is > 1. If that's the case then I know I need to adjust the commission and split it in 2. So in the middle of this output query, I'm looping through the Under5 array to find a match on the Agent Name. When all the clients producing under 5 have been displayed for the current Agent, I have to display the clients producing over 5. I forgot to mention above, that another query we have before we do the output is the Over 5 query. Just like I created a value list of the ContracNo's producing < 5, I do the same for Over5, and run a query specifically on that list. So within our main output query, after the < 5 Clients have been displayed, I loop through the Over5 query output to find the matching Agent Names, and then output the data. Now this section that I just mentioned, is what causes the execution time to jump exponentially. I just tested commenting it out, and the page runs in about 4 seconds. But with this code in, it runs at a minimum of 15 seconds on a good day. On a regular day it just crashes the production server. I tried my best to explain the situation in great detail and hope it makes sense, and hope that some people would be able to help me improve. Thanks again, Ali ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236836 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
