1

I have an Apex Action that queries records of a custom object called Payable__c. This object has a look-up field called InvoiceId__c. The Apex Action needs to sort Payable__c records according to the creation date of invoice records, so something like this:

SELECT Id FROM Payable__c ORDER BY Invoice__r.CreatedDate LIMIT 10

However, invoices can be created in batches, so it's possible for two or more of the 10 records returned by the SOQL above to have the same invoice creation dates, which breaks the sort.

Also, this Apex Action is invoked in a Flow that runs frequently, so I was wondering if there was some way of optimising the sort or even "caching" the ordered records, so that they don't have to be sorted each time the Apex Action is invoked. I came across this very helpful answer, which introduced me to the idea of "indexing". I have a very rough idea of how it works and would like to leverage it in this case, but I'm not sure if I'm doing it correctly.

I modified my query to order by the Invoice__c Id instead:

SELECT Id FROM Applicant__c ORDER BY Invoice__c DESC LIMIT 10

This solves the problem of sorting on duplicate values that I encountered when sorting by CreatedDate, but is it more performant? I figured it would be since Invoice__c is a lookup field and therefore indexed. However, some articles seem to imply that indexed fields must be used in conjunction with LIMIT for the optimiser to kick in, e.g.

So how would adding a sort clause make the query more selective? It wouldn’t. Sort clauses have nothing to do with selectivity. You might also ask, “Wouldn’t adding an ORDER BY clause increase the overhead because Salesforce must complete a sorting task in addition to selecting the appropriate rows?” The answer is “no.” When you have an ORDER BY clause bound by a LIMIT clause–and chances are that you already have one in place to avoid hitting governor limits-the Force.com query optimizer considers using the index because the index, by nature, is a presorted list of values.

I'm not sure what the bit in bold means. Why does Salesforce only consider using an index if the ORDER BY is bound by LIMIT? If we're ordering on an indexed field, wouldn't it always make sense to use the index?

2
  • 3
    You emphasized the wrong segment in that passage. So how would adding a sort clause make the query more selective? It wouldn’t.
    – Adrian Larson
    Mar 7, 2023 at 13:22
  • @AdrianLarson, I emphasised the bit that I was unsure about. I don't want to make my query more selective, I just want to optimise the sorting. I thought I could do this by sorting on an indexed variable, but the passage seems to say that Salesforce only considers using the index if there is a LIMIT clause in the query, but I'm not sure what this means or if I'm misreading it.
    – user51462
    Mar 8, 2023 at 3:05

1 Answer 1

4

First, let's start off by saying a lot of the engineering of databases at the scale of Salesforce, and most commercial CRMs, are typically far beyond the likes of anything other kinds of developers have ever worked with. Handling a billion transactions a day on trillions of records is an engineering feat. At those scales, most of what we developers think would make sense are actually not true for some reason for another.

There's at least a few misconceptions in your question that need to be addressed. First, databases don't return "duplicate" rows in a single query. Each row you receive is unique. If that's not the case, you should file a bug report, though I strongly suspect you're simply misinterpreting the data, or using the wrong terminology. Second, all of the audit fields are Date and Lookup fields, and all Date and Lookup fields are automatically indexed. There's no way to turn off this feature, in fact.

However, one potential cause for confusion is that the audit dates only have a resolution of one second. A record created at 2023-03-07T12:34:56.000 and another at 2023-03-07T12:34:56.999 will both have a timestamp of 2023-03-07T12:34:56.000. Within this one second of granularity, records may be returned out of order with respect to each other. To complicate matters, multiple records created by multiple users within that same second may have chronologically incoherent Id values.

This last fact comes from the fact that each "pod", or "instance", is a cluster of computers. A load balancer directs users to servers in the cluster with the most available resources. Each server grabs some unspecified size block of ID values for use, and when they come close to exhausting their pool, they request another block of ID values from a central, thread-safe ID counter. Within a single database transactions, we can guarantee the ID values will be assigned in ascending order, but multiple users on multiple servers creating records very near each other may result in chronologically confusing order.

That means that even an order operation such as ORDER BY CreatedDate, ID (in either order) may not be sortable to the actual chronological order of the created records. At such small scales, the order is nondeterministic, what a developer would call a "race condition". It's usually not helpful to think that you can order such records on such a small scale, short of intentionally building an automation where you store the epoch time for each record's creation, and then store that data in a number field, and add an index there.

Ordering is something that happens after the rows have been selected. That means that adding any kind of ORDER BY does not inherently add any additional performance for a query. It also means that indexes do not directly benefit an ORDER BY clause. The exception is when there are indexes and a LIMIT. The Query Plan Optimizer can identify records that will likely meet the query more efficiently and stop when enough records are collected. This is what makes an index useful: ORDER BY and LIMIT can turn an otherwise nonselective query into a selective query.

Note that there are multiple levels of cache in Salesforce. The application layer, database layer, and OS layer all have various types of cache. This makes recently used queries return faster, which is why having a local cache is usually unnecessary. You can read more about Platform Cache for developer-accessible cache, but it's probably overkill for your use case.

Ultimately, I don't think I've been able to answer the underlying question, but I hope this answer serves to answer the questions you may have had about the underlying technology. I'd encourage you to read about the X-Y Problem so you can formulate a more direct question in regards to why your code is not working as you expect, which would provide a more concrete answer compared to studying the underlying technical theory of what's going on.

1
  • Thank you for your answer @sfdcfox. I think your third-to-last paragraph answers my question. Just to clarify, by "duplicate values", I don't mean duplicate rows, but rather duplicates in the column we are ordering by, which confounds the sort order (you address this point in your third and fifth paragraphs). My apologies if I wasn't clear in the post.
    – user51462
    Mar 8, 2023 at 3:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .