2

To lock records during the life of a transaction we use FOR UPDATE at the end of the SOQL statement. All good. One limitation is that you cannot use ORDER BY in conjunction with FOR UPDATE.

If I wanted to combine the effects of FOR UPDATE and ORDER BY on a result set, I could do a bubble sort on the recordset and use that.

My question is can I do this:

sObject[] lockedResultset = [select blah from blah where blah = 'blah' for update];
sObject[] orderedResultset = [select blah from blah where blah = 'blah' order by something];

and use the orderedResultset, whilst being confident that the records are locked (by virtue of the first SOQL query...)?

The pros of using the bubble sort is that we do only one query not two, and it might be fractionally quicker. Any thoughts?

Thanks

2
  • 2
    Both approaches will work. If you are sorting using standard SObject sorting, the sort processing is behind a Salesforce Apex API and therefore is fast. If you cannot use this, because you want to sort by something other than name, then you want to minimise the Apex processing you do. You might find it more efficient to wrap the SObjects in instances of an Apex class that implements Comparable and use list sorting. In essence, reduce the amount of loops and the loop complexity at the expense of more Salesforce API calls (the latter is always faster). Do two queries if you are limits safe to do – Phil W Nov 17 '20 at 10:55
  • thanks @PhilW - appreciate it – edralph Nov 17 '20 at 15:50
4

Given the governor limits involved, you should not generally query twice for the same rows. This reduces the effective maximum from 50,000 rows to 25,000 rows, which doesn't sound too bad, but remember that the limits include all sub-transactions, so it's important not to waste them frivolously.

However, you should not use a bubble sort. It has only slightly better performance than say, a random sort algorithm. That is to say, if you have 200 records in a result, the bubble sort needs up to 40,000 iterations to sort all the records, which can easily take up over a second. By the time you get to 2,000 rows, you'll have 4,000,000 iterations, which will definitely be a CPU timeout.

Instead, implement a wrapper class with the Comparable interface, sort them, and then extract the values.

public class Wrapper implements Comparable {
  public Integer compareTo(Object other) {
    return record.field.compareTo((Wrapper)other).field);   
  }
  sObject record;
  public Wrapper(sObject record) {
    this.record = record;
  }
}

Then, for the the implementation:

sObject[] records = [SELECT field from sobject for update];
Wrapper[] temp = new Wrapper[0];
for(sObject record: records) {
  temp.add(new Wrapper(record));
}
temp.sort();
records.clear();
for(Wrapper item: temp) {
  records.add(item.record);
}

This seems like a lot more work, but will save CPU time over a bubble sort by a large margin.

1
  • Super useful thanks. The query will only ever return fewer than 10 rows, but they need to be locked and I need them sorted :). In the short term I think doing two queries is the way to go, and then I'll refactor with the wrapper class approach. – edralph Nov 17 '20 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.