function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
LaurenP6777LaurenP6777 

SQL inside of For loop

Hi Everyone, 

 

I have gotten an error on the following trigger saying that I am going over the query limit. I keep reading that it is because I have my "SQL query" within my "FOR" loop.

Can someone please show me how to rework this code so I won't hit my limit?

 

I have seen other examples but can't apply it to my own. If I can see this trigger reworked- i think it will really help me understand. Thanks!

 

All this trigger does is update the name of the "Parent opportunity" when any of the "Related Opportunity" child records are changed.

 

 

trigger countopptyonchildchange on Opportunity (after insert, after update, after undelete)
{
list<Opportunity> ParentOpptys = new list<opportunity>();
list<id> relatedIDs = new list<id>();

for(Opportunity Ropp : trigger.new)
{
if(Ropp.Parent_Opportunity__c != null)
{
relatedIDs.add(Ropp.Parent_Opportunity__c);
}
}

for(Opportunity Popp: [select Id, name from Opportunity where Id in : relatedIDs])
{
Popp.name=popp.name;
ParentOpptys.add(Popp);
}

update ParentOpptys;
}

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

As the other poster mentioned, this code isn't the problem. A "query inside loop" will always look something like this:

 

for( a b: x ) {
  c d = [select ... from e where ... = b.id];
  // do something with d
}

Sometimes the logic is far removed, especially if you're using utility classes, etc.

 

The reason why your for loop is getting flagged is only because it is the 101st query, not because it is in itself the problem. In your debug logs, make sure that "DB" is set to finest, and check the cumulative usage area:

 

11:18:25.551|CUMULATIVE_PROFILING_BEGIN
11:18:25.551|CUMULATIVE_PROFILING|SOQL operations|
Class.X.<init>: line 25, column 1: [select ... from opportunity where id = :oppid]: executed 5 times in 14 ms Class.X.<init>: line 20, column 1: [select ... from pricebook2 where isstandard = true]: executed 1 time in 6 ms

This is an example from a debug log. Here, it shows that a single query was executed 5 times in a single transaction. This means that that query is a candiate for optimization. There are total of six queries for this run.

 

To debug, follow these steps:

 

In Salesforce, in the upper-right corner, click on "Your Name" > Developer Console.

In the new window that appears, click on "Workspace: Default", and choose "Change Log Levels".

Set "DB" to "finest", and click "Done."

Keeping this window open, go back to your main window, and replicate the error by performing the action you are trying to do (e.g. update an opportunity).

Once the error surfaces, go back to the Developer Console, click on the line item in the top frame that has a "Status" that shows an error.

Click on "Open Raw Log" to open the log. Scroll all the way to the bottom, then scroll up, looking for CUMULATIVE_PROFILING_BEGIN.

Observe the queries being called, and how many times they are executed.

 

Then, go fix the query with the largest count; it is most likely the culprit.

All Answers

DrBix65DrBix65

From my view, it looks flne and should only execute two queries.  I'd guess the problem is elsewhere in your code, and not in this trigger.  Perhaps something is causing the trigger to fire many times.

 

As a side note, that query is not actually PART of the for loop.  It will only get exectued once.

sfdcfoxsfdcfox

As the other poster mentioned, this code isn't the problem. A "query inside loop" will always look something like this:

 

for( a b: x ) {
  c d = [select ... from e where ... = b.id];
  // do something with d
}

Sometimes the logic is far removed, especially if you're using utility classes, etc.

 

The reason why your for loop is getting flagged is only because it is the 101st query, not because it is in itself the problem. In your debug logs, make sure that "DB" is set to finest, and check the cumulative usage area:

 

11:18:25.551|CUMULATIVE_PROFILING_BEGIN
11:18:25.551|CUMULATIVE_PROFILING|SOQL operations|
Class.X.<init>: line 25, column 1: [select ... from opportunity where id = :oppid]: executed 5 times in 14 ms Class.X.<init>: line 20, column 1: [select ... from pricebook2 where isstandard = true]: executed 1 time in 6 ms

This is an example from a debug log. Here, it shows that a single query was executed 5 times in a single transaction. This means that that query is a candiate for optimization. There are total of six queries for this run.

 

To debug, follow these steps:

 

In Salesforce, in the upper-right corner, click on "Your Name" > Developer Console.

In the new window that appears, click on "Workspace: Default", and choose "Change Log Levels".

Set "DB" to "finest", and click "Done."

Keeping this window open, go back to your main window, and replicate the error by performing the action you are trying to do (e.g. update an opportunity).

Once the error surfaces, go back to the Developer Console, click on the line item in the top frame that has a "Status" that shows an error.

Click on "Open Raw Log" to open the log. Scroll all the way to the bottom, then scroll up, looking for CUMULATIVE_PROFILING_BEGIN.

Observe the queries being called, and how many times they are executed.

 

Then, go fix the query with the largest count; it is most likely the culprit.

This was selected as the best answer