2

I have an Apex Controller that is sporadically bumping up against the SOQL Governor limit when executing some functionality.

The functionality does some queries and updates some values which executes some triggers, which do more queries. Basically a bunch of Apex classes are running in the call-chain. I'm trying to find out which pieces are piling up the most queries and see how it can be optimized.

I'm trying to use the Analysis perspective in Developer console to see where I'm at limit-wise.

enter image description here

This shows me the total number of queries compared to the limit. Is there a way to break it down so it can show me the number of queries executed by each class in the call-chain?

3

Open Debug > Change Log Levels. Set the debug level of profiling, apex as fine. Check below: (Also better to set expiration to 11.30pm)

enter image description here

Now, when you get log, open it and open Debug > View log panels. and select Execution log and execution overview. you will see as below:

enter image description here

Here you can see count of each query and time taken and other things. If you select it you can see from which line numbers its coming. Select the SOQL above and remove filter. Then in lines above that soql you will see which class it has entered.

Now I removed filter:

enter image description here

Now you can see that query is in line122 and in line 78, you can see className.MethodName(Parameters)

2

Use Profiling. By going in to the Developer Console, you can go to Debug > Change Log Levels, then modify the debug logging to include "Profiling" set to "FINEST". From there, run your execution, and afterwards, you'll get a section in your debug logs that show you which queries were executed, and how many times.

For example, here's an example from my dev org:

09:38:20.332 (1332704580)|CUMULATIVE_PROFILING|SOQL operations|
Trigger.myNS.myTrigger: line 12, column 1: [
            SELECT AccountId Id, COUNT(Id) total FROM Opportunity
            WHERE AccountId = :accountSum.keySet() GROUP BY AccountId]: executed 5 times in 13 ms

This shows that a query was executed 5 times, from a particular trigger (it could also be a class, such as Class.myNS.myClass). From there, you can determine both how many times it executed and how long it took in "database time", which affects the length of the transaction. This will give you an idea on where to focus your optimization efforts.

  • Profiling set to finest runs into truncated logs many times in my exp. Info is just enough. – salesforce-sas Jul 24 '19 at 17:09
  • 1
    @salesforcesas I usually turn the other flags to NONE when I do that, it ends up being a respectably small log. Of course, YMMV, but you might try that out sometime. – sfdcfox Jul 24 '19 at 17:53
  • +1 -- I never knew this. Thanks! – cropredy Jul 25 '19 at 1:44
-1

That depends on which objects you are updating, and how you have written triggers. If you think you can bypass the execution of trigger after a dml, then please do bypass. This avoids trigger recursion. Also make sure process builders are not executing multiple times and workflows as well. To avoid multiple execution of process builders and workflows, have a check that does update only if the particular field value is changed or empty before. These kind of checks avoid multiple executions.

  • Those are the type of improvements I am looking to make. This is code that was not originally written by me, so I am unsure which spots can be bypassed without looking into it further. What I was hoping is there would be a way to see number of queries per class, or method or something more granular to the call chain so I can try to identify spots for improvement like is mentioned. – Developer Guy Jul 24 '19 at 15:27
  • Well what can I think right now is, have a static integer as a counter in each class, and increment it whenever a query is made. This way you can have the count. – Manjunatha V P Jul 24 '19 at 15:32
-1

Have a static integer as a counter in each class, and increment it whenever a query is made. This way you can have the count.

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.