There’s been a little chatter recently about the script statement limit and specifically around sorting algorithms in Apex.
Instead of burying the solution deep in the annals of the discussion boards, Jon has convinced me to start publishing again. For those that haven’t been reading this blog since the days it was proudly branded "sforce", my last post was on 6/13/2005!
Needless to say that’s been too long and speaking of that, you’re probably saying get on with it already so I will 
It all started with this little post back when Apex was in developer preview at which time we had not yet exposed a sort() method on the collection types (Array/List). We quickly corrected this and for collections of primitives a quick call to sort() will re-order the list in ascending order.
Great for primitives but what about other types? SObjects for example…. perhaps everyone’s favorite standard object – Opportunity?
Before you get caught up or miss the obvious reaction to the following example I will admit it’s fairly contrived. If all you want to do is sort opportunities on amount you should do so within your SOQL statement directly (see ORDER BY). Please bear with me for this example as I will tie this technique into my next post regarding a sorting and grouping solution as sought by this thread.
While it may be easy to fall back to a custom sorting algorithm a combination of the standard List.sort() method and associative arrays (Maps) can provide a significantly reduced statement footprint.
Let’s take a look at some code that demonstrates the approaches:
@IsTest private class sortTest {
/* Test the approach described by the custom sort */ public static testmethod void testCustomSort() {
List<Opportunity> opps = sortTest.getOpps(); Integer before = Limits.getScriptStatements(); sortTest.quicksortOpptys(opps,0,opps.size() - 1); Integer after = Limits.getScriptStatements(); System.debug('SCRIPT STATEMENTS USED BY THE CUSTOM SORT: ' + (after - before)); sortTest.doAsserts(opps); }
/* Test the aproach of using the standard list.sort() method and a map */ public static testmethod void testStandardSort() { List<Opportunity> opps = sortTEST.getOpps(); Integer before = Limits.getScriptStatements(); opps = sortTest.sortStandard(opps); Integer after = Limits.getScriptStatements(); System.debug('SCRIPT STATEMENTS USED BY THE STANDARD SORT: ' + (after - before)); sortTest.doAsserts(opps); }
/* Sort the collection of opportunities using the standard collection sort method. */ private static List<Opportunity> sortStandard(List<Opportunity> opps) { List<Opportunity> resultList = new List<Opportunity>(); /* Create a map of amount to Opportunity collection */ Map<Decimal, List<Opportunity>> oppMap = new Map<Decimal, List<Opportunity>>(); for(Opportunity o:opps) { if(oppMap.get(o.amount) == null) { oppMap.put(o.amount, new List<Opportunity>()); } oppMap.get(o.amount).add(o); } List<Decimal> keys = new List<Decimal>(oppMap.keySet()); /* Leverage the standard, primitive collection sort method */ keys.sort(); for(Decimal key:keys) { resultList.addAll(oppMap.get(key)); } return resultList; }
/* Quicksort method as described on the boards adapted to use an opportunity collection. */ private static void quicksortOpptys(List<opportunity> a, Integer lo0, Integer hi0) { Integer lo = lo0; Integer hi = hi0;
if (lo >= hi) { return; } else if( lo == hi - 1 ) { if (a[lo].amount > a[hi].amount) { Opportunity o = a[lo]; a[lo] = a[hi]; a[hi] = o; } return; }
Opportunity pivot = a[(lo + hi) / 2]; a[(lo + hi) / 2] = a[hi]; a[hi] = pivot;
while( lo < hi ) { while (a[lo].amount <= pivot.amount && lo < hi) { lo++; } while (pivot.amount <= a[hi].amount && lo < hi ) { hi--; } if( lo < hi ){ Opportunity o = a[lo]; a[lo] = a[hi]; a[hi] = o; } }
a[hi0] = a[hi]; a[hi] = pivot; quicksortOpptys(a, lo0, lo-1); quicksortOpptys(a, hi+1, hi0); } /* Get a recent set of opportunities with non-null amounts for use in each test */ private static List<Opportunity> getOpps() {
/* If your org does not have sufficient data, you can create opportunities with random amounts here instead of querying. */ return [select name, amount from opportunity where amount > 0 order by createddate desc limit 25]; } /* Assert the collection is ordered ascending. */ private static void doAsserts(List<Opportunity> opps) {
Decimal assertValue = -1; for(Opportunity o:opps) { System.debug('OPPTY VALUE: ' + o.amount); System.assert(assertValue <= o.amount); assertValue = o.amount; } }}
If you take the code above and save it into your Developer Edition account as a new class you can run these tests to see how they compare with your data. In my account the results were pretty significant:
SCRIPT STATEMENTS USED BY THE CUSTOM SORT :327SCRIPT STATEMENTS USED BY THE STANDARD SORT: 76
Moral to the story: associative arrays and methods on the standard types are great assets to minimize the # of script statements needed to achieve the desired outcome.
It’s good to be back!
