1

I have a search query in my component that shortly will need to support more than one lookup field where only one would be filled on the certain object. For now the query looks as follows:

 SELECT Name, ... FROM Object WHERE First_Field__c =: value  
                                    [AND... many other conditions]

Where First_Field__c is a lookup to FirstObject__c.

I am wondering what would be more reasonable to do considering efficiency and performance of the query.
I can think of two solutions:

1. Add an OR to the query

 SELECT Name, ... FROM Object WHERE First_Field__c =: value 
                                 OR Second_Field__c =: value

Where Second_Field__c is a lookup to SecondObject__c.

2. Create a formula field Formula__c like ISBLANK(First_Field__c, Second_Field__c ,First_Field__c ) and use it in the query without an OR.

SOSL cannot be leveraged in this scenario as I am using multiple cross-reference fields and conditions. As we know, lookup fields are indexed while formulas are not but ORs also slow down queries. It seems there is no optimal solution, is there?

Improve this question
5
  • the formula field will always be worse as it is not indexed and forces a table scan
    – cropredy
    Apr 8, 2021 at 17:45
  • @cropredy Really, that should have been an answer... 😅
    – sfdcfox
    Apr 8, 2021 at 18:11
  • @cropredy Is it better to leverage an OR considering that I have also multiple other conditions that also cause full table scan as they use not indexed text fields?
    – jm.
    Apr 8, 2021 at 18:11
  • you know you can use the query plan tool in Developer Console against Production Data to gain insight on performance of options
    – cropredy
    Apr 8, 2021 at 18:27
  • @cropredy This would be the first thing I would do if I had access to production data. However Query Plan Tool always returns IsDeleted as not indexed and causes entire query to be not selective, I despair with this tool ;)
    – jm.
    Apr 8, 2021 at 18:30

1 Answer 1

Reset to default
3

You need at least one indexed field in order to avoid expensive table scans, and potentially query timeout problems or non-selective query errors.

You have two real options. First, use your first option. The OR operator will be just fine, as long as you're not filtering for null. The second option is to use the formula field, but request a custom index.

While formulas are normally evaluated in real-time, if your formula meets the criteria for an index, which this probably would, you can get the convenience of a single field filter with the power of an indexed field. This may be overkill, but it is an option.

I would use the OR filter unless you're already near the maximum WHERE size limit (4,000 characters) and you absolutely need the extra few characters.

OR operators do not inherently slow down queries unless one of the two parameters are not indexed.

That is:

OR(LookupField1__c = :value or LookupField2__c = :value)

Will use the index and not cause any problems, but:

OR(LookupField1__c = :value or CheckboxField__c = true)

Will still cause a table scan without other filters.

Improve this answer
2
  • Thank you, I am accepting your answer, however could you please share your thoughts about my previous comment: Is it better to leverage an OR considering that I have also multiple other conditions that also cause full table scan as they use not indexed text fields? What in case of a query like .. (LookupField1__c = :value OR LookupField2__c = :value) AND NotIndexedText__c = 'Value' AND NotIndexedPicklist__c = 'API' that also cause full scan... Or what if we are using terrible LIKE filter?
    – jm.
    Apr 8, 2021 at 18:22
  • 1
    @jm. Given a filter like (A OR B) AND C, as long as A and B are indexed, or C is indexed, the result will be an indexed lookup. A table scan is when every single row in the database must be scanned. In the incident where (A or B) is an indexed search, then C will only be checked where A or B matches, resulting in an efficient search.
    – sfdcfox
    Apr 8, 2021 at 18:59

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.