2

I have 2 different formula fields, one with huge data and the other one is a new one and therefore has fewer records.

I have 2 different SOQL queries using these 2 fields and the performance for the SOQL with formula field with huge data is taking only 2 seconds but the SOQL query with the latter formula field with fewer records is taking 8 seconds making the site look very slow.
1. Can anyone throw light on this behaviour with formula fields?
2. Can you suggest if there is a way to increase the performance on formula fields ruling out going to SF to index it?

2
  • Are both these formula fields on the same object?
    – Nick C
    Nov 24, 2016 at 0:10
  • Yes. Both returning the same type(text) as well.
    – SF Learner
    Nov 24, 2016 at 0:23

1 Answer 1

2

Assuming the formula fields are both on the same object, it's impossible for one to have 'fewer records' than the other.

A formula field is calculated at run time. This means when you view a record, the formula field is calculated and displayed at that point in time. The 'value' isn't stored anywhere, it's only displayed when a record is viewed or accessed.

That sounds simple and innocent enough, but it has a huge bearing on the performance of your SOQL queries. Because a formula is calculated at run time, if a SOQL query uses a formula field as its WHERE clause, it has to retrieve all records then individually check each record to see if it matches (i.e. it's a full table scan). This is because the 'value' is only available when the record is 'viewed' by your code.

It's hard to provide specific advice on how to improve the performance, because it all depends on your data and your data model.

Your starting place should be analysing your SOQL query using the Query Plan in the Developer Console. For more information on this: Query Plan Tool (How To & FAQ)

As per that doc, your goal is to make your query selective. If you existing formulas would result in a small number of records out of a huge table, perhaps you can move the formula to a workflow rule that updates a field. You can then get that field indexed, which will improve your SOQL performance dramatically.

4
  • Thanks @Nick for the quick and detailed response. Just curious to know what you mentioned about a formula field not having fewer data even if its created afresh. When is the formula actually calculated? I am sure it would appear on every record I see afterwards, but will it have any impact on how its calculated when put in a 'Where' condition of SOQL? I read somewhere that if the where clause field is NOT a formula field, then there would be a performance boost.
    – SF Learner
    Nov 24, 2016 at 0:30
  • So, are you suggesting that a workflow update for ex. a Text field and copy the values from that formula field which i could use in the where clause, but this approach might have the overhead of forcing an update?
    – SF Learner
    Nov 24, 2016 at 0:30
  • @ShaliniMendu I've updated my answer. Hope it's a bit clearer on when a formula is calculated. That workflow idea is a possible solution, but it depends on your problem. If you want to understand this, I'd highly recommend using the Query Plan tool, as referenced in my answer. If you're having trouble with it, there are plenty of other questions on here that go into some details, but if they're no help, I'd suggest closing this question and asking a new question with specific details on your problem.
    – Nick C
    Nov 24, 2016 at 0:45
  • I used Query plan tool to gauge performance and found that the fast running query was indeed using index on the formula field. Thanks for your help. I am closing this question.
    – SF Learner
    Nov 24, 2016 at 1:19

You must log in to answer this question.

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