Apex Code Development You need to sign in to do that
Don't have an account?
tengel Need suggestions on avoiding SOQL governor limit
Hello!
I have a pretty nice flow in place that allows a user to enter some useful competitor information when we lose an Opportunity. One of the steps in the flow allows the user to create a follow-up Opportunity for next year, which is designed to basically clone the existing Opp with all of its products.
To accomplish this, I created a query loop for Opportunity Product where a checkbox called "Flow Processed" is false (thanks @Rajaram! solution here: http://boards.developerforce.com/t5/Visual-Workflow/Deep-Clone-Opportunity-with-Products-via-Visual-Workflow/m-p/642189#M1419). The query retrieves each Opportunity Product one at a time, marks Flow Processed as true, clones it, and re-runs the query until there are no more Flow Processed = false products.
This works perfectly EXCEPT when there are a lot of products. My testing seems to suggest that 4 products is the limit. Anything over that causes this error:
caused by: interaction.sfdc.adapter.rules.SalesforceRuleBrokenException: UPSERT --- UPSERT FAILED --- ERRORS : (CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY) SL_Opportunity: System.LimitException: Too many SOQL queries: 101 --- for SFDC record with ID : null, Salesforce Error ID: 497037844-218727 (-1473334608)
The SL_Opportunity referenced is a trigger developed by a third party that is essential to a system integration in place, so I can't really change that trigger. I need a different workaround.
I know some brilliant mind in this community can offer some suggestions for getting around this. I've been thinking about trying to count the number of Products and then separate the Product create actions into batches or something, but I keep getting confused!
Thanks!
Hi,
Please first check why ID is null and where it is being used in your code. Is it like your For loop depends on this value and loop has query statement?
This error System.LimitException: Too many SOQL queries: 101is due to the fact, you are hitting on governor limit. The governor limit that says we can run total 100 SOQL queries in a context. and you are hitting the limit.
All the triggers fired will be counted in a single context or call. We need to ensure that total number of SOQL fired should be less than 100.
In order to by pass this, you need to change your code in such a way that SOQL fired must be less than 100 or if you need to change the context then you can use @future annotation which will run the code asynchronously.
Also you need to make sure that the SOQL query you are using should not be inside the for loop. There are certain best practices which you would have to follow to avoid this error (to avoid hitting governor limit).
1.http://wiki.developerforce.com/page/Best_Practice:_Avoid_SOQL_Queries_Inside_FOR_Loops
2.http://salesforcedeveloperblog.blogspot.com/2011/05/best-practices-of-triggers.html
3.http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_triggers_bestpract.htm
4.http://wiki.developerforce.com/page/Apex_Code_Best_Practices
If you follow the above practices, the error will stop. Moreover there is no way wherein we in salesforce can increase the governor limit or can stop it so best practices need to be followed.
You have already explained it,,,by your description I think the error is causing because of Query is in loop, use that query out of the loop, and use that record in the query,
You can hit query only 100 times, but you can fetch 10,000 record in a single query, you are using query for each record,
Make your trigger bulky.
Hope this works
If lowering the number of SOQL queries is not an option you could create an Apex plugin to check for governor limits. The plugin would output a boolean to indcate if you were to hit a specified threshold. When the flag is true you would bring the user to a pause screen so that the SOQL governor resets. This will stop the fault from occurring but requiresthe user to click next each time the governor threshold is hit.