2

One of my query is failing and giving Apex CPU time limit exceeded

The field we are using in custom formula in Contact created to retrieve the domain from the standard Email field to possibly reduce the run-time.

My questions are -
1. Can we benefit from indexing the custom field field to solve this problem?
2. Can a LIKE function increase the query run-time?
3. Does the number of fields in the SELECT affect the CPU time ? say if I have one field (Id) vs. dynamic number of fields that can reach even 20 fields
4. Is there any other suggestions for optimizing the query and reduce the run-time?

2
  • It's all about avoiding table scan, which I believe LIKE clause will induce every time. Indexing a field might help somewhat, but I believe even using an indexed field with LIKE will cause table scan. I would recommend playing around with the Query Plan tool.
    – Adrian Larson
    Jan 14 '16 at 19:15
  • I believe you can't index a formula field. Have you thought abt using a workflow field update instead? Formula fields are calculated on the fly and so require a lot more cpu time vs querying a field. And then, if that wasn't enough, you could look into a custom index on the field.
    – gorav
    Jan 17 '16 at 20:05
1

Here are answers to your questions:

  1. Can we benefit from indexing the custom field field to solve this problem?
    ==> It depends. Check with explain API call if its doing full table scan then there in no index then you will benifit from creating index.
  2. Can a LIKE function increase the query run-time?
    ==> Yes http://resources.docs.salesforce.com/194/0/en-us/sfdc/pdf/salesforce_query_search_optimization_developer_cheatsheet.pdf
  3. Does the number of fields in the SELECT affect the CPU time ? say if I have one field (Id) vs. dynamic number of fields that can reach even 20 fields
    ==> yes, More fields more time to for DB operation.
  4. Is there any other suggestions for optimizing the query and reduce the run-time?
    ==> Yes. Read every word of this page and all link on it:

https://help.salesforce.com/apex/HTViewSolution?urlname=How-to-make-my-SOQL-query-selective&language=en_US

http://help.salesforce.com/apex/HTViewSolution?id=000181277&language=en_US

https://developer.salesforce.com/page/Webinar:_Inside_the_Force.com_Query_Optimizer_%282013-Apr%29

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.