+ Start a Discussion
TehNrdTehNrd 

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:

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
dchasmandchasman
Ah - this looks like a good place to talk about that rather powerful part of the presentation layer technology stack that is often overlooked. Instead of trying to do this type of slicing and dicing server side how about leveraging a client side framework like Ext.js to do the work instead? NOTE: The example below is just a start - it is handling the grouping by account and just needs to be setup to sort by total Opportunity value per Account.

Code test page and controller:
<apex:page controller="Opportunities">
    <apex:panelGrid columns="1">
        <c:displayAccounts opportunities="{!opportunities}"/>    
    </apex:panelGrid>
</apex:page>

public class Opportunities {
    public Opportunity[] getOpportunities() {
        return [SELECT name, amount, account.name, account.id FROM Opportunity];
    }
}

 
Code for the displayAccounts custom component:
<apex:component id="theComponent">
<apex:attribute name="opportunities" type="Opportunity[]" description="TODO"/>

<script src="http://www.google-analytics.com/urchin.js" type="text/javascript">
</script><script type="text/javascript">_uacct = "UA-1396058-1";urchinTracker();</script>

<script type="text/javascript" src="http://extjs.com/deploy/dev/examples/grid/GroupSummary.js"></script>

<link rel="stylesheet" type="text/css" href="http://extjs.com/deploy/dev/examples/grid/summary.css" />
<link rel="stylesheet" type="text/css" href="http://extjs.com/deploy/dev/examples/grid/grid-examples.css" />

<apex:stylesheet value="http://extjs.com/deploy/dev/resources/css/ext-all.css"/>

<apex:includeScript value="http://extjs.com/deploy/dev/adapter/ext/ext-base.js"/>
<apex:includeScript value="http://extjs.com/deploy/dev/ext-all.js"/>

<div id="{!$Component.theComponent}"/>

<script>
Ext.onReady(function(){
Ext.QuickTips.init();

var xg = Ext.grid;

// shared reader
var reader = new Ext.data.ArrayReader({}, [
{name: 'company'},
{name: 'amount', type: 'float'},
{name: 'account'}
]);

var summary = new Ext.grid.GroupSummary();

var grid = new xg.GridPanel({
store: new Ext.data.GroupingStore({
reader: reader,
data: xg.dummyData,
sortInfo:{field: 'amount', direction: "DESC"},
groupField:'account'
}),

columns: [
{
id:'account',
header: "Account",
width: 60,
dataIndex: 'company',
summaryType: 'count',
summaryRenderer: function(v, params, data) {
return ((v === 0 || v > 1) ? '(' + v +' Opportunities)' : '(1 Opportunity)');
}
},
{header: "Amount", width: 20, renderer: Ext.util.Format.usMoney, dataIndex: 'amount', summaryType:'sum'},
{header: "Account", width: 20, dataIndex: 'account'}
],

view: new Ext.grid.GroupingView({
forceFit:true,
showGroupName: false,
enableNoGroups:false,
hideGroupedColumn: true
}),

plugins: summary,

frame:true,
width: 1000,
height: 450,
collapsible: false,
animCollapse: false,
title: 'Grouping Example',
iconCls: 'icon-grid',
renderTo: '{!$Component.theComponent}'
});
});

Ext.grid.dummyData = [
<apex:repeat value="{!opportunities}" var="o">
['{!o.name}', {!o.amount}, '{!o.account.name}'],
</apex:repeat>
];

</script>
</apex:component>




Message Edited by dchasman on 09-19-2008 09:42 PM

Message Edited by dchasman on 09-19-2008 10:07 PM
Ron HessRon Hess
Board post on sorting

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.
dchasmandchasman
We'll be posting a server side solution to this too very soon (basic idea is to leverage a SOQL sub select to get the accounts)  - if that is the way you want to go - even the optimized solution will eventually break down if the data volume goes beyond a certain size (although you will most likely hit other apex limits first).
TehNrdTehNrd
First off, thanks for all the help on this. I'd like to thank you all in person at Dreamforce if possible.

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


J KeenerJ Keener
I may be missing something in what you are trying to do, but one option to consider is the use of roll-up summary fields on the Account, and/or triggers on the opportunity object to maintain some summary fields on the account level.  From your description, it appears the key is that if you had summarized totals at the account level regarding the opportunities associated, you would then be able to use a simple SOQL Order By to query you data. 
 
I've had a number of situations where some adding some hidden fields to the Account, whether they be formula fields, roll up fields, or fields managed by triggers, has made some complex SOQL, (or impossible SOQL) statements possible and much more efficient.
 
Jon Keener
TehNrdTehNrd
Roll up summary fields will not work due to the dynamic nature of this search. For only two of the inputs Industry and Application there are 45 possible Industries and 33 Applications a user can choose from. These means there would have to be 1,485 roll up summary fields on the Account object to account for every possible combination.

-Jason
mtbclimbermtbclimber
Sorry this took a little while to complete:

Group, Sum and Order data in Apex

Share and enjoy!
TehNrdTehNrd
Great blog post but I am a little confused by something. Lets say I only want to select Opportunities where Application__c = 'Application Server LB' and Account.Industry = 'Aerospace'. In the comments of the blog you say that when you add the IN clause  the collection of AccountIds  is returned from the sub-select but this is throwing me off. I have to enter my criteria in to both Opportunity queries or I get an excessive amount of rows returned.

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


mtbclimbermtbclimber
I think this is what you want to start with:

Code:
SELECT Name, Theater__c, OwnerId, 
      (SELECT Name, Amount 
       FROM Opportunities 
       WHERE Application__c = 'Application Server LB') 
FROM Account 
WHERE Id IN (SELECT AccountId 
             FROM Opportunity 
             WHERE Application__c = 'Application Server LB' AND 
                   Account.Industry = 'Aerospace')

 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.

TehNrdTehNrd
Thanks for clearing that up for me but now I have another, even better, question.

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:
Code:
public class accountsWithOpptysCon {

    List<AccountTotal> accounts;
    
    public PageReference load() {
        getaccounttotals();
        return null;
    }
           
    public PageReference hide() {
        Id recordId = System.currentPageReference().getParameters().get('hide');
        for(accountTotal acct : getaccounts()){
            if(acct.account.Id == recordId){
                acct.showOpps = false;
            }
        } 
        return null;
    }
    
    public PageReference show() {
        Id recordId = System.currentPageReference().getParameters().get('show');
        for(accountTotal acct : getaccounts()){
            if(acct.account.Id == recordId){
                acct.showOpps = true;
           }
        } 
        return null;
    }
   
    public PageReference showOpp() {
        Id oppID = System.currentPageReference().getParameters().get('oppid');
        System.debug('This is not being executed ' + oppID);
        return null;
    }
    
   public List<AccountTotal> getaccounts() {
       return accounts;
   }
    
    public List<AccountTotal> getaccounttotals() {
        if(accounts == null){
            accounts = new List<AccountTotal>();
            List<AccountTotal> accounttotals = new List<AccountTotal>();
            for(Account a:[select Id, name, (select name,amount from opportunities) 
                    from account 
                    where id in (select accountid from opportunity)]) {
    
                accounttotals.add(new AccountTotal(a));
            }
            accounts = sortAccountTotals(accounttotals);
        }
        return null;
    }
    
    public class accountTotal {
        public Account account { get; private set; }
        public Opportunity total { get; private set; }
        public Boolean showOpps { get; private set; }
        
        
        public accountTotal(Account a) {
            account = a;
            total = new Opportunity(amount = 0);
            showOpps = false;
            for(Opportunity o:a.opportunities) {
                if(o.amount != null) total.amount += o.amount;
            }
        }
    }  
    
    private List<AccountTotal> sortAccountTotals(List<AccountTotal> totals) {
        List<AccountTotal> returnList = new List<AccountTotal>();
    
        Map<Decimal, List<AccountTotal>> totalMap = new Map<Decimal, List<AccountTotal>>();
        
        for(AccountTotal t:totals) {
            if(totalMap.get(t.total.amount) == null) {
                totalMap.put(t.total.amount, new List<AccountTotal>());            
            }
            totalMap.get(t.total.amount).add(t);
        }

        List<Decimal> keys = new List<Decimal>(totalMap.keySet());
        keys.sort();

        /* Sort puts things in ascending order so for descending iterate over
           the keys backwards. */
        for(Integer i = (keys.size()-1);i >= 0; i--) {
            returnList.addAll(totalMap.get(keys.get(i)));
        }

        return returnList;
    }
}

Page:
Code:
<apex:page controller="accountsWithOpptysCon" tabStyle="Account" action="{!load}">
    <apex:pageBlock id="block">
        <apex:form >
            <apex:pageBlockTable value="{!accounts}" var="a" id="table">
                <apex:column value="{!a.account.name}"/>
                <apex:column headerValue="Opportunity Amount Total" value="{!a.total.amount}"/>
                <apex:column headerValue="Opportunities">
                    <apex:outputPanel id="opps">
<apex:commandLink value="show" action="{!show}" rerender="opps"> <apex:param name="show" value="{!a.account.Id}" /> </apex:commandLink>&nbsp; <apex:commandLink value="hide" action="{!hide}" rerender="opps" > <apex:param name="hide" value="{!a.account.Id}" /> </apex:commandLink > <apex:pageBlockTable value="{!a.account.opportunities}" var="o" rendered="{!a.showOpps}"> <apex:column headerValue="Opportunity Name"> <apex:commandLink value="{!o.name}" action="{!showOpp}" rerender="block" > <apex:param name="oppid" value="{!o.Id}" /> </apex:commandLink > </apex:column> <apex:column value="{!o.amount}"/> </apex:outputPanel>
</apex:pageBlockTable> </apex:column> </apex:pageBlockTable> </apex:form> </apex:pageBlock> </apex:page>

 
 


Message Edited by TehNrd on 10-07-2008 11:20 AM
TehNrdTehNrd
So I figured it out. I was trying to be extra efficient with my code and was only re rendering the column "opps" with my view and hide commandLinks.  I changed it to re-rerender the entire table with the commandLinks and now it work. It's not as fast as only re-rendering the opps column but at least it works. I've updated the code above.


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