5

Scenario

Let's say I have two custom obejcts - Student and Class. The Class object is connected with object Student through lookup relationship. The Class object also has a formula field (Student_UniqueCode__c):

Student__r.uniqueCode__c.

Now while doing SOQL, which one I should use, the formula field or the relationship to get better performance?

Query 1

Select Student_UniqueCode__c from Student__c

or

Select Student__r.uniqueCode__c from Student__c

Query 2

Select <..> from Student__c where Student_UniqueCode__c like ...

or

Select <..> from Student__c where Student__r.uniqueCode__c like ...
2
  • 1
    Is this just something you're curious about, or are you trying to "optimize"? If it's the latter, remember that premature optimization is generally a bad thing. There are likely better ways to improve overall performance (that will have a larger impact than optimizing queries), but the process starts with measuring your current performance, and defining goals that you want to achieve.
    – Derek F
    Apr 9, 2018 at 16:21
  • 1
    One particular benefit of using a formula is that they're already pre-populated in triggers, so you can get the parent data without an additional query. However, that said, it's rarely ever worth the effort to set up formulas for this purpose unless you're already bumping up against a governor limit, and should be considered a last resort.
    – sfdcfox
    Apr 9, 2018 at 18:45

2 Answers 2

6

You should use the Query Plan tool to figure out performance questions for your own org. When I analyzed a nearly identical structure in my own org, there was no difference in performance.

It may also be worth reading this article by Steve Bobrowski: Force.com Formula Fields, Indexes, and Performance Gotchas. It goes into detail about what your decision making process should be and the performance impacts.

1
  • Student__r.uniqueCode__c is a table join, where as Student_UniqueCode__c is a formula. If we create custom index on Student_UniqueCode__c and its value in in range of Query Threshold, then Student_UniqueCode__c will be better in performance in where clause. Apr 9, 2018 at 16:38
2

Lookup fields are indexed so definitely it will give you performance benefit.

Formula field which is deterministic, custom index can be created.

For your Query 2, if you have millions of the data to be exported then possibly using formula field in WHERE will definitely slower than using direct Lookup field.

2
  • 1
    I think in my scenario, since the formula field is referencing another object, so it is non-deterministic and custom index cannot be created here. So in my scenario, using relationship makes the query perform better. Correct? Apr 9, 2018 at 18:02
  • Yes, you are correct Apr 9, 2018 at 19:09

You must log in to answer this question.

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