4

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 ...
  • 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 '18 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 '18 at 18:45
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.

  • 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. – Pranay Jaiswal Apr 9 '18 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.

  • 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? – Sudipta Deb Apr 9 '18 at 18:02
  • Yes, you are correct – Santanu Boral Apr 9 '18 at 19:09

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.