Authored by Zishan Razzaq

Wednesday, July 23, 2008

Too many SOQL queries: 21 Error in Force.com

Hi and Welcome:
Basically this error defines that your code went over the governing level of salesforce. Basically, this happens when you are uploading data through an web service api or data loader.

Just remember you will have to keep your batch size low due to the trigger governor limits (soql statements). A batch of 20 is just fine, or less.

So an example:
Scenario:
  1. Upload through a 3rd party tool to salesforce from sql server into the product and pricebookentry table.
  2. This is to update the existing products into salesforce. (NOT AN INSERT BUT AN UPDATE)
  3. The trigger will update the account making the IsActive(Price) Active on the current Pricebook which is Active not Standard Pricebook.
Bad Trigger:

trigger updateitafter on Product2 (after update) {

List toUpdate = new List();
for(Product2 p:Trigger.new){

Product2 existingProduct= Trigger.oldMap.get(p.Id);
//The SOQL statement below will give you an error
//because of the for loop..
for (PriceBookEntry li:[Select UnitPrice, Product2Id,
Pricebook2Id, Id From PricebookEntry
where Product2Id=:existingProduct.Id
and IsActive=false and UnitPrice!=0.00])
{
li.IsActive=true;
toUpdate.add(li);
}

update toUpdate;
}
}

The right way to do the trigger:
trigger updateitafter on Product2 (after update) {
List allids = new List();

List toUpdate = new List();
for(Product2 p:Trigger.new){

Product2 existingProduct= Trigger.oldMap.get(p.Id);
allids.add(existingProduct);
}

for(PriceBookEntry li:[Select UnitPrice,
Product2Id, Pricebook2Id, Id
From PricebookEntry where
Product2Id In :allids
and IsActive=false
and UnitPrice!=0.00])
{
li.IsActive=true;
toUpdate.add(li);
}
update toUpdate;
}

The Change:

  1. In apex or any database-centric Programming, a query should not be executed inside the loop.
  2. So getting all the ProductId's first and placing it into an array(list) then using that list placing it back into the query just by an IN statement makes it easy and within limits of salesforce.
  3. This would do two things:
    1. Execute a single query and loop through its results.
    2. Also, efficient coding and clean.
Hope this was helpful.
Thanks
View my Other Blogs:
VisualForce Made Easy

Salesforce Data Migration Made Easy