+ Start a Discussion
kpetersonkpeterson 

Too Many Query Rows

I'm getting a "Too many query rows" error on a SOQL For Loop.  It was my understanding that you could use this approach to prevent governor limits from occurring.
 
This is line 106 in the test method:
Code:
insert oAssignments;

 
This is line 40 within the trigger:
Code:
for (OpportunityLineItem oOpportunityLineItem : [SELECT Id, OpportunityId, Project__c, PricebookEntry.Product2Id, PricebookEntry.ProductCode, PricebookEntry.Name FROM OpportunityLineItem WHERE Project__c IN :oProjectsMap.KeySet()])

 
 
Code:
System.Exception: Too many query rows: 501

Trigger.AssignmentInsertPopulateProductFlagsHandler: line 40, column 57

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, AssignmentInsertPopulateProductFlagsHandler: execution of BeforeInsert

caused by: System.Exception: Too many query rows: 501

Trigger.AssignmentInsertPopulateProductFlagsHandler: line 40, column 57

Class.TestAssignmentInsertPopulateProductFlags.testAssignmentInsertPopulateProductFlagsHandler: line 106, column 9

 


Message Edited by kpeterson on 02-19-2008 06:38 AM
SuperfellSuperfell
What's the rest of your trigger code look like?
kpetersonkpeterson
Code:
/*
 Populates the product boolean flags for all assignments under the Project v2.0 recordtypes
*/
trigger AssignmentInsertPopulateProductFlagsHandler on SFDC_Assignment__c (before insert) {

 String sProjectContractRecordTypeId = '012600000009IOw';
 String sProjectAddOnsRecordTypeId = '012600000009ISw';
 Map<Id, SFDC_Projects__c> oProjectsMap = new Map<Id, SFDC_Projects__c>();
 Map<Id, Product2[]> oProjectProductsMap = new Map<Id, Product2[]>();
 SFDC_Assignment__c[] oAssignments = new SFDC_Assignment__c[0];
 
 //get projects the assignments are related to
 for (SFDC_Assignment__c oAssignment : Trigger.New)
 {
  if (oAssignment.Projects__c != null)
   if (!oProjectsMap.containsKey(oAssignment.Projects__c))
    oProjectsMap.put(oAssignment.Projects__c, null);
 }
 
 oProjectsMap = new Map<Id, SFDC_Projects__c>([SELECT Id, RecordTypeId, Opportunity__c FROM SFDC_Projects__c WHERE Id IN :oProjectsMap.KeySet()]);
 
 //get any assignments related to the project system
 for (SFDC_Assignment__c oAssignment : Trigger.New)
 {
  if (oAssignment.Projects__c != null)
  {
   SFDC_Projects__c oProject = oProjectsMap.get(oAssignment.Projects__c);
   if (oProject.RecordTypeId == sProjectContractRecordTypeId ||
    oProject.RecordTypeId == sProjectAddOnsRecordTypeId)
   {
    oAssignments.Add(oAssignment);
   }   
  }  
 }
 
 //if we found some project system assignments
 if (oAssignments.Size() > 0)
 {
  //get the products that belong to the project
  for (OpportunityLineItem oOpportunityLineItem : [SELECT Id, OpportunityId, Project__c, PricebookEntry.Product2Id, PricebookEntry.ProductCode, PricebookEntry.Name FROM OpportunityLineItem WHERE Project__c IN :oProjectsMap.KeySet()])
  {
   System.debug('-- Product: '+oOpportunityLineItem.PricebookEntry.Name);
   if (oProjectProductsMap.get(oOpportunityLineItem.Project__c) == null)
   {
    oProjectProductsMap.put(oOpportunityLineItem.Project__c, new Product2[0]);
   }
   oProjectProductsMap.get(oOpportunityLineItem.Project__c).Add(new Product2(id = oOpportunityLineItem.PricebookEntry.Product2Id, Name = oOpportunityLineItem.PricebookEntry.Name));
  }
  
  System.debug('-- oProjectProductsMap Size: '+oProjectProductsMap.Size());
  
  //loop over the assignments again and lookup their project/products to
  //set the assignments product addon boolean fields
  for (SFDC_Assignment__c oAssignment : oAssignments)
  {
   Product2[] oProducts = oProjectProductsMap.get(oAssignment.Projects__c);
   
   if (oProducts != null)
   {
    System.debug('-- Got this many products for the assignment: '+oProducts.Size());
    
    for (Product2 oProduct : oProducts)
    {
     //ordered by product code ascending     
     if (oProduct.ProductCode == '33070')
      oAssignment.X1_PPC_Increase_Recurring__c = true;
     else if (oProduct.ProductCode == '33080')
      oAssignment.X1_PPC_Increase_Temporary__c = true;
     else if (oProduct.ProductCode == '011010')
      oAssignment.X1_IBE_Contract__c = true;

     ...........

     else if (oProduct.ProductCode == '091901')
      oAssignment.X1_PMS_Interface_1_Way__c = true;
     else if (oProduct.ProductCode == '091902')
      oAssignment.X1_PMS_Interface_1_Way__c = true;
     else if (oProduct.ProductCode == '091903')
      oAssignment.X1_PMS_Interface_2_Way__c = true;
     else if (oProduct.ProductCode == '091904')
      oAssignment.X1_PMS_Interface_2_Way__c = true;
     
    }
   }
  }
 }
}

 
NikiVNikiV

I'm running into a similar problem where I have a query in my trigger that could return more than 1000 records (or the Test Method limit of 500).  Did you ever find a work around on how to chunk up the query results so you don't get more than the limit?  It seems like there should be a way given that it is not inconceivable that there are more than 1000 child records to a parent.  My scenario is Leads linked to a custom Campaign object.  When I query for all the linked Leads and there are more than 1000, things grind to a halt.

 

Thanks for any tips you can provide!

kjpetersonkjpeterson
I did not find a solution.  I believe I just tried to make my code as efficient as possible and had to take the hit if there are too many.
sweetleonsweetleon

I believe I've figured out something: when a query is used repeatedly in a loop, the governor limits apply to the number of rows it returns cumulatively.

In other words, if a query returns 100 rows and is called 10 times, the governor limits think it's returned 100 rows. (Even if the 100 rows returned by each iteration are different.

 

Here's some code that proves it:

 

        List<OpportunityContactRole> tempOCRs = new List<OpportunityContactRole>([select Id from OpportunityContactRole limit 1]);
        Set<Id> realIds = new Set<Id>();

        while (tempOCRs.size() > 0) {
            System.debug('x');
            tempOCRs = new List<OpportunityContactRole>([
                select Id, Opportunity.Id, Contact.Id
                from OpportunityContactRole
                where Contact.Name != 'Attorneys TBD'
                and Opportunity.Id not in :realIds
                limit :(Limits.getLimitQueryRows()-2)
            ]);
            for (OpportunityContactRole ocr : tempOCRs) {realIds.add(ocr.Opportunity.Id);}
        }
 

I don't yet know what to do about this, but figured I'd share it with the community and see if anyone else has ideas.

sweetleonsweetleon

I was wrong. Yet again!

 

It appears that the governor limits on query rows apply cumulatively to all queries in a method.

Annoying, but now I understand what's happenining and will code accordingly.

Abhinav GuptaAbhinav Gupta

The key to get this error fixed is ISOLATION of your test data from the org's data. Try executing your test via System.runAs(<User>). I am sure it will help.

This blog post explains this in more detail : http://www.tgerm.com/2010/05/systemrunas-501-apex-query-rows.html