1

I have a use case where I am exposing a custom Rest service that would accept phone# as an argument and compare with the five phone# fields on Account and would return the single record matching either of the five phone#. In case, if multiple records are found, the earliest created record should be returned. My only concern/problem is when this goes to production, it won't work/there will be a performance issue as there are 15 million Account records. I can probably index the fields, but will that really help when record count is so high? I would really appreciate any suggestions/help to optimize this SOQL.

SOQL:

[SELECT Id 
FROM Account 
WHERE RecordType=’Person Account’ 
AND (Parent_Phone_Number__c= :phoneNo OR Student_Phone_Number__c= :phoneNo OR 
Alternate_Number_1__c= :phoneNo OR Alternate_Number_2__c= :phoneNo OR 
Alternate_Number_3__c= :phoneNo)
ORDER BY CreatedDate DESC 
LIMIT 1];

PS: I already asked the Biz team if we can have only one phone# field while comparison. But they are adamant with five fields.

3

This is the perfect use case for SOSL, instead. You can do:

FIND :phoneNumber 
IN PHONE FIELDS
RETURNING Account(
  Id 
  WHERE RecordType.Name='Person Account'
  ORDER BY CreatedDate
  LIMIT 1
)

Note: If any of those custom fields are formula fields, this approach will miss those records, as formula fields are not search indexed. You need to actually copy the values down to the record being searched for performance reasons.

This statement would still be true with SOQL, by the way; you need to make the query selective, which means not using formula fields. As long as all the fields filtered are indexed, then your original query should not be a problem, even at 15 million records of data volume.

6
  • what if they have a text formula which contains all the fields value and we can use IN condition(to compensate ORs), will it work?
    – Ysr Shk
    Aug 15 '20 at 13:53
  • 1
    @YsrShk No, that won't help, as there could be multiple phone values. Also, you have to submit a support ticket to index formulas, or they behave as a full table scan, which exacerbates the situation.
    – sfdcfox
    Aug 15 '20 at 14:29
  • oh kk thanks for the information!
    – Ysr Shk
    Aug 15 '20 at 15:58
  • @sfdcfox Thanks much for the insight. Just wondering how it would make a difference using SOQL vs SOSL in this use case..
    – Ali
    Aug 16 '20 at 5:15
  • @Ali The general difference is that SOQL has to specify all the fields to search/filter, but SOSL can automatically search "all" phone fields, even if new fields are added, or old fields are removed. SOSL cannot find values that are not indexed, though, but will be faster than SOQL if formulas are involved.
    – sfdcfox
    Aug 16 '20 at 5:25

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.