CUMULATIVE_LIMIT_USAGE & CUMULATIVE_LIMIT_USAGE_END - Too many SOQL queries

Question

We are getting 'Too many SOQL queries' on execution of Triggers on Account. We are using lot of Managed packages.I am trying to debug a single test method to start with. I am looking at the debug logs, this is my approach: 1. Find out the first statement which says 'Number of SOQL queries: 52 out of 100 ******* CLOSE TO LIMIT , for example 2. Try to focus on the flow of the code between 'CODE_UNIT_STARTED' & 'CODE_UNIT_FINISHED' around the first statement found in step 1. I could not find anything wrong with the queries so far, but is this the right approach? I guess there is a limit usage log for the managed packages, which is giving the SOQL query usage for them, but can it so happen that because of the excessive queries fired in managed packages, the error is reported in the limit usage log of unmanaged classes?

Answer 1

I find that updates in for loops is a dead giveaway as the number at the conclusion of the previous code is low and then it hits the limit during one code execution of a class or trigger.

Recursion - either directly or indirectly (Trigger updates a related object which fires a different trigger and updates another related object which fires a trigger and updates the original object... and so on and so forth) can add up quickly by the time you get to five levels deep. You only need 20 soql queries to get caught out that way.

In our org we have found that the biggest problem we inherited off the original dev team was that triggers fire and run 2 or 3 queries but due to business logic don't actually do anything. The solution we used was to 'hide' the queries inside an if statement and check the business logic first. The better solution is to move all logic to helper classes and only call the helper when the business rules (as far as can be determined without soql) need it.

Answer 2

Queries running in the managed package do not affect the SQOL limits in the default namespace where your classes run.

In which namespace are you seeing the queries getting too high? If it's managed, there are sometimes things you can do, like pre-populate some fields on Account prior to insert - sometimes if managed code does not see any value in a certain field, it runs a query to get data for that field. You may need to contact the managed package vendor to see how you could reduce queries on Account. If you see duplicated queries, then it's possible that either your or managed code is not bulkified, which would need to be fixed.

If it's in your namespace, check for recursion. A trigger could be re-updating the Account within the same transaction, and rerunning the triggers. Search the log for duplicated queries. That could also be a sign that some queries are inside FOR or WHILE loops, which they shouldn't be.

Additionally, consider caching some query results in the class's static member, and accessing them in there from other classes, instead of rerunning the same query in the same context.

Answer 3

My approach to this problem would be to look for some reoccurring SOQL in the logs on the assumption that the issue would be caused by the same SOQL being executed in a for loop. I would do this by searching the logs for SOQL_EXECUTE_BEGIN. If you see the same query happening a lot then that could indicate a problem.