You need to sign in to do that
Don't have an account?
TehNrd
Too many script statements: 200001, Enter for an Algorithmic Challenge!
Warning, this thread has a lot of data to take in but if you like a good challenge continue on.....
200,000 script statements!?! What the heck are you doing?
Business Problem:
Our sales team sends out e-mails to massive company wide e-mail distributions lists asking, "Does anyone know of a company in Industry X using one of our products for application Y. Due to sharing rules running reports doesn't return all of our results and our sales team has reporting phobia even if this was an option. :smileywink:.
Solution:
What we are trying to do is create a custom VF page that is like a sales wins portal / advanced search interface. There are a few pick lists, Industry, Application, etc. A user selects values then hits submit and Apex runs a dynamic SOQL search on opportunities to find all of these results.
Technical Problem:
When displaying the data the results should not show individual opps but group these by accounts. It should also display the list so that the largest accounts are at the top of the list. If SOQL had SUM and GROUP BY options everything would be great but alas this is not an option. So what I have done when querying the opps is order them by AccountId. As the loop cycles through these opps it sums up the opp amounts and when it detects an AccountID change creates Account container that can be displayed on the page.
The next step is to add the "Account" to a List but this list must be in order from largest to smallest. So what I have done is create an algorithm that grabs two objects and compares the values to each. If it is less that the first but greater than the second it takes the index of the second.
With all this manual grouping and sorting I sometimes hit the script statement limits.
The Challenge:
Obviously my algorithms are not very efficient. I am not a classically trained programmer (learned must of it on my own) so I don't really know all the slick algorithm tricks a seasoned developer may know. So finally, is there a better/more efficient way of doing this to reduce my script statements or am I just out of luck?
The Code:
Thanks a ton!
-Jason
Admins/Mods: I debated on what board to post this on (Apex/VF) as it is Apex but I am working with it for the sole purpose of formatting the data to be displayed on a VF page. Feel free to move it you deem necessary.
Message Edited by TehNrd on 09-19-2008 04:32 PM
200,000 script statements!?! What the heck are you doing?
Business Problem:
Our sales team sends out e-mails to massive company wide e-mail distributions lists asking, "Does anyone know of a company in Industry X using one of our products for application Y. Due to sharing rules running reports doesn't return all of our results and our sales team has reporting phobia even if this was an option. :smileywink:.
Solution:
What we are trying to do is create a custom VF page that is like a sales wins portal / advanced search interface. There are a few pick lists, Industry, Application, etc. A user selects values then hits submit and Apex runs a dynamic SOQL search on opportunities to find all of these results.
Technical Problem:
When displaying the data the results should not show individual opps but group these by accounts. It should also display the list so that the largest accounts are at the top of the list. If SOQL had SUM and GROUP BY options everything would be great but alas this is not an option. So what I have done when querying the opps is order them by AccountId. As the loop cycles through these opps it sums up the opp amounts and when it detects an AccountID change creates Account container that can be displayed on the page.
The next step is to add the "Account" to a List but this list must be in order from largest to smallest. So what I have done is create an algorithm that grabs two objects and compares the values to each. If it is less that the first but greater than the second it takes the index of the second.
With all this manual grouping and sorting I sometimes hit the script statement limits.
The Challenge:
Obviously my algorithms are not very efficient. I am not a classically trained programmer (learned must of it on my own) so I don't really know all the slick algorithm tricks a seasoned developer may know. So finally, is there a better/more efficient way of doing this to reduce my script statements or am I just out of luck?
The Code:
Code:
List<cAccount> resultsAcct = new List<cAccount>(); Opportunity holder; Decimal totalAmount = 0; Integer count = 0; for(Opportunity opp : Database.query(queryString)){ boolean added = false; //------There was a change in accounts------- //holder.Account.Id is the account Id of the previous opp in this loop so if they don't match account grouping has changed if(holder != null && opp.Account.Id != holder.Account.Id){ cAccount newAcct = new cAccount(); newAcct.accountName = holder.Account.Name; newAcct.accountID = holder.AccountId; newAcct.info.amount = totalAmount; newAcct.info.OwnerID = holder.Account.OwnerId; newAcct.info.Theater__c = holder.Account.Theater__c; //If there is only one entry in our set we only have one comparison to make if(resultsAcct.Size() == 1){ if(newAcct.info.amount > resultsAcct[0].info.amount){ added = true; resultsAcct.add(0,newAcct); } //Here is the meat and potatoes //Basically compares the new value to indexes 0-1, then 1-2 then 2-3, and then insert accordingly }else if(resultsAcct.Size() > 1){ for(Integer i = 0; i < resultsAcct.Size()-1; i++){ cAccount acctCompareHigh = resultsAcct[i]; cAccount acctCompareLow = resultsAcct[i+1]; if(newAcct.info.amount >= acctCompareHigh.info.amount){ added = true; resultsAcct.add(0,newAcct); break; }else if(newAcct.info.amount < acctCompareHigh.info.amount && newAcct.info.amount >= acctCompareLow.info.amount){ added = true; resultsAcct.add(i+1,newAcct); break; } } if(added == false){ resultsAcct.add(newAcct); } //This is entered if the size = 0 }else{ resultsAcct.add(newAcct); } totalAmount = 0; } //------------------------------------------------- if(opp.Amount == null){ opp.Amount = 0; } totalAmount += opp.Amount; holder = opp; } //cAccount class / container public class cAccount{ public Opportunity info {get; set;} public String accountName {get; set;} public ID accountId {get; set;} public cAccount(){ info = new Opportunity(); } }
Thanks a ton!
-Jason
Admins/Mods: I debated on what board to post this on (Apex/VF) as it is Apex but I am working with it for the sole purpose of formatting the data to be displayed on a VF page. Feel free to move it you deem necessary.
Message Edited by TehNrd on 09-19-2008 04:32 PM
Message Edited by dchasman on 09-19-2008 09:42 PM
Message Edited by dchasman on 09-19-2008 10:07 PM
http://community.salesforce.com/sforce/board/message?board.id=apex&thread.id=260
if you could pick one of his superior sorting tricks, you should come in under the statement limit.
You still have to adapt your code, but this is a good start.
I like the idea of using an external javascript libraries to make the pages truly rich but I do have some concerns with this approach. One of the reasons that makes Visualforce so appealing is that you take away the concerns about browser compatibility. No longer do I have to worry about whether the code will work on one browser but not the other. I leave that up to you guys. I know things are getting better in this area as more browsers start to adhere to web standards but even the thought of developing for each browser or the fact that something may stop working when a new browser is release is somewhere I would rather not go.
Also, in all honestly, I do not yet have the skill set to create pages that use these nifty javascript libraries. I have played a little with ext.js and scriptaculous but I'm just not there yet. But then again a little over a year ago I knew nothing about Apex or Visualforce so I'm sure I could pick it up if I could squeeze in the time to learn it.
So in regard to my original problem I have reworked the code that may provide some solutions. What it comes down to is that I was performing a really inefficient sort every time I added a cAccount to the List. Now as long as the List size is less than 1000 just add the cAccount to this list and then sort it once at the end. Rarely does this size even reach 1000 so this works most of the time. When this list does hit 1000 and there are still more entries to add i will sort this once when size hits 1000. Then for any additional cAccounts to add I will need to come up with an efficient algorithm that adds values to a list that is already sorted. Here is what I am thinking:
If the new value is lower than the lowest don't do anything, else, split the list in half and find the middle value. If the value I need to add is greater than the middle value split the top half, repeat and keep splitting until I find the index where this should fit. If my math is correct the most I would ever have to do this is 10 times on a List of 1000 elements.
Also, in the link that Ron posted I have added some more efficient sorting algorithms.
Thanks,
Jason
-Jason
Group, Sum and Order data in Apex
Share and enjoy!This is what I have:
SELECT Name, Theater__c, OwnerId, (SELECT Name, Amount FROM Opportunities where Application__c = 'Application Server LB' AND Account.Industry = 'Aerospace') FROM Account where Id IN (SELECT AccountId from Opportunity where Application__c = 'Application Server LB' AND Account.Industry = 'Aerospace')
If I try this:
SELECT Name, Theater__c, OwnerId, (SELECT Name, Amount FROM Opportunities where Application__c = 'Application Server LB' AND Account.Industry = 'Aerospace') FROM Account where Id IN (SELECT AccountId from Opportunity)
It errors out with too many soql rows as I assume it is selecting the AccountId from every Opportunity in the system. Any I doing this right or am I missing something.
Thanks,
Jason
This query says "give me all accounts in the Aerospace industry that have at least one opportunity with an application__c value of 'Application Server LB' and for each account include the opportunities with an application__c value of 'Application Server LB'.
Depending on the number of unique accounts you could still hit the SOQL rows limit. You can further constrict the data set with additional criteria on the Semi-join or add additional criteria to the base query. Ultimately you may need to just add a limit though you'll probably want to constrain since you really need the whole result set to make this meaningful.
The important point about the limits here is that it's not the return size of the Semi-Join sub-query that's the problem it's that it does not constrict the base query enough to get you under the limit. In other words, it's easy to misconstrue what is actually impacting the soql rows limit since there is only one criterion and it's not an Anti-Join.
Hopefully this helps.
I have tweaked your code so that a user can dynamically show and hide the related Opportunites. This is very slick as initially the list is nice and compact. Then a user can drill down to view additional details for a given Account. The problem I have run into is that I have changed the Opportunity Name to a command link but this action method is never being executed. Ideally a user would then be able to click the opp name and then be displayed more detailed info for this specific opportunity.
I'm really hoping this is an easy fix as this will be super cool but I have spent a decent amount of time on it and can't figure out why the action is not executing.
Thanks,
Jason
[edit] code has been updated and now works
Here is the code:
Controller:
Page:
Message Edited by TehNrd on 10-07-2008 11:20 AM
EDIT: Figured out a way to only re-render the opps row. I wrapped the entire contents of this column in a outputPanel and then re-render this panel. CommandLinks now work and there is no performance hit for re-rendering the entire table. Code is updated.
Message Edited by TehNrd on 10-07-2008 11:18 AM