function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
PekPek 

Too many query rows: 1002

Hello,

I get the following error at runtime when a given contact has  more than 499 related Cases:

Error: Invalid Data. 
Review all error messages below to correct your data.
common.apex.runtime.impl.TriggerExecutionException: Apex trigger TotalsUpdate caused an unexpected exception, contact your administrator: TotalsUpdate: execution of AfterUpdate caused by: System.Exception: Too many query rows: 1002: Trigger.TotalsUpdate: line 44, column 11 

 

Line 44 corresponds to this:

 

conts = [select Id, Last__Date__c, First__Date__c, Vol_Hours__c, Num_of_shift__c, (select of_Hours__c, Activity_Date__c from Cases  order by Activity_Date__c Desc) from Contact where ID in :contIDs ];
 
Is this an issue with the governor limit? 
 
If this is the case, is there a way to avoid the limit?
 
Thanks a lot.
 
Pierre
 
Here is the full trigger: 
trigger TotalsUpdate on Case (after delete, after insert, after update) {
	set<ID> contIDs = new Set<ID>();
			
	if(Trigger.isInsert) {
		for(Case c : System.Trigger.new){
				if (c.of_Hours__c <> NULL)
					{contIDs.add(c.ContactId);}
		}
	
		Contact[] conts = new List<Contact>();
		conts = [select Id, Last__Date__c, First__Date__c, Vol_Hours__c, Num_of_shift__c, (select of_Hours__c, Activity_Date__c from Cases  order by Activity_Date__c Desc) from Contact where ID in :contIDs ];
			
		Case[] ca = new List<Case>(); 
		Map<Id, Case[]> contcase = new Map<Id, Case[]>();
		for (Contact eachCont: conts){
			contcase.put(eachCont.Id, eachCont.Cases);
			 }
		Contact[] updatedContacts = new List<Contact>();
		Case[] cas = new List<Case>();
		for (Contact c : conts ){
				cas=contcase.get(c.Id);
				c.Last__Date__c = cas[0].Activity_Date__c;
				c.First__Date__c = cas[cas.size()-1].Activity_Date__c;
				Double SumOfHours = 0;
				Double NumOfShift = 0;
				for(Case cse : cas) {
					SumOfHours += cse.of_Hours__c;
					NumOfShift = NumOfShift + 1;  }
				c.Vol_Hours__c = SumOfHours;
				c.Num_of_shift__c = NumOfShift;
				updatedContacts.add(c);
			}	
			if (updatedContacts.size()>0) {update updatedContacts;}
	
	}	
	
	if(Trigger.isDelete || Trigger.isUpdate) {
		for(Case c : System.Trigger.old){
				if (c.of_Hours__c <> NULL)
					{contIDs.add(c.ContactId);}
		}
	
		Contact[] conts = new List<Contact>();
		conts = [select Id, Last__Date__c, First__Date__c, Vol_Hours__c, Num_of_shift__c, (select of_Hours__c, Activity_Date__c from Cases  order by Activity_Date__c Desc) from Contact where ID in :contIDs ];
			
		Case[] ca = new List<Case>(); 
		Map<Id, Case[]> contcase = new Map<Id, Case[]>();
		for (Contact eachCont: conts){
			contcase.put(eachCont.Id, eachCont.Cases);
			 }
		Contact[] updatedContacts = new List<Contact>();
		Case[] cas = new List<Case>();
		
		for (Contact c : conts ){
				cas=contcase.get(c.Id);
				Double SumOfHours = 0;
				Double NumOfShift = 0;
				c.Last__Date__c = NULL;
				c.First__Date__c = NULL;
				if(cas.size()>0){
					c.Last__Date__c = cas[0].Activity_Date__c;
					c.First__Date__c = cas[cas.size()-1].Activity_Date__c;							for(Case cse : cas) {
							SumOfHours += cse.of_Hours__c; 
							NumOfShift = NumOfShift + 1;
						}
					}
				
				c.Vol_Hours__c = SumOfHours;
				c.Num_of_shift__c = NumOfShift;				
				updatedContacts.add(c);
			
			}
			
			if (updatedContacts.size()>0) {update updatedContacts;}
	
	}	

}
 
 
   


cloudcodercloudcoder
Yes, the error is related to governor limits. There are a number of ways you can avoid this (See http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_loops_for_SOQL.htm)
 
Looking at your code however, you have some other issues which will cause you problems. For example you are doing multiple SOQL calls within a for loop which is certainly not best practice. Have a look at the following article which should give you some pointers: http://wiki.developerforce.com/index.php/Best_Practice:_Bulkify_Your_Code
 
HTH 


pierrefrazny.ax358pierrefrazny.ax358
I made changes to the trigger. It now accomodates up to 1,000 case records. I thought by using the aggregate functions I would by pass this limit but this did not happen. I still get the following error as soon as there are 1,000 cases records:
 
Error: Invalid Data. 
Review all error messages below to correct your data.
Apex trigger VolunteerUpdate2 caused an unexpected exception, contact your administrator: VolunteerUpdate2: execution of AfterInsert caused by: System.Exception: Too many query rows: 1001: Trigger.VolunteerUpdate2: line 14, column 41
 
Is there any way to by pass this limit?
Thanks a lot.
Pierre
 
 
trigger VolunteerUpdate2 on Case (after delete, after insert, after update) {
    set<ID> contIDs = new Set<ID>();
    Contact[] updateContacts = new Contact[]{};
            
    if(Trigger.isInsert  ) {
        for(Case c : System.Trigger.new){
                if (c.of_Hours__c <> NULL)
                    {contIDs.add(c.ContactId);}
        }
        
        
        Map<ID, Contact> contacts = new Map<ID, Contact>([SELECT ID FROM Contact WHERE Id in: ContIDs ]);
             
        AggregateResult[] allContacts = [select ContactId, COUNT(Id)numShifts, MAX(Activity_Date__c)lastShift, MIN(Activity_Date__c)firstShift From Case Where ContactId in: ContIDs group by ContactId ];
        
        //Loop over the aggregate result set
		for (AggregateResult ar : allContacts) 
		{	
			Contact thisContact = new Contact(Id=String.valueOf(ar.get('ContactId')));
			thisContact.Num_of_shift__c = Integer.valueOf(ar.get('numShifts'));
			thisContact.Last_Date__c = Date.valueOf(ar.get('lastShift'));
			thisContact.First_Date__c = Date.valueOf(ar.get('firstShift'));
			
			//Add this new account to the list of account objects
			updateContacts.add(thisContact);
		}

		//Update the account object.
		update updateContacts;	
        
    }

   if(Trigger.isDelete  ) {
        for(Case c : System.Trigger.old){
                if (c.of_Hours__c <> NULL)
                    {contIDs.add(c.ContactId);}
        }
        
        Map<ID, Contact> contacts = new Map<ID, Contact>([SELECT ID FROM Contact WHERE Id in: ContIDs ]);
        
        AggregateResult[] allContacts = [select ContactId, COUNT(Id)numShifts, MAX(Activity_Date__c)lastShift, MIN(Activity_Date__c)firstShift From Case Where ContactId in: ContIDs group by ContactId ];
        
        //Loop over the aggregate result set
		for (AggregateResult ar : allContacts) 
		{	
			Contact thisContact = new Contact(Id=String.valueOf(ar.get('ContactId')));
			thisContact.Num_of_shift__c = Integer.valueOf(ar.get('numShifts'));
			thisContact.Last_Date__c = Date.valueOf(ar.get('lastShift'));
			thisContact.First_Date__c = Date.valueOf(ar.get('firstShift'));
			
			//Add this new account to the list of account objects
			updateContacts.add(thisContact);
		}

		//Update the account object.
		update updateContacts;	
        
    }

}
 
 
Message Edited by pierrefrazny on  03-04-2010 08:02 PM


bob_buzzardbob_buzzard
It looks like you are hitting a governor limit for the maximum list size, in that you are retrieving the list of cases and then processing them.
 
Take a look at SOQL for loops on page 54 of the Apex Developer's Guide - this looks like exactly the problem they solve.


PekPek
I do not understand how the for loop would help in this case. 
 
Also, would @future annotation help? I have never used it. Do I need to move the trigger logic in a class?
 
Thanks a lot.
 
Pierre


groundwiredgroundwired
Hi Pierre,
 
I am having this same issue with GROUP BY code I am writing.  It was my impression with GROUP BY that only the records that are returned - the aggregate rows - would count against your "query rows" governor limit.  But your experience and mine is that they are counting ALL the rows that are touched as counting against your limit, not the number of rows that you get back.
 
Can someone from Salesforce confirm whether this is a bug, or the intended behavior?  If it is the intended behavior, it would make the new GROUP BY aggregate queries virtually worthless.
 
-Evan  


David81David81
Curious on this as well. Just did some testing with some of the new aggregate functions and it still appears to count every record that was counted as a query row for the limit even though I'm only getting 1 row of data.
 
Oddly enough...

In the debug log for a trigger that has only one query:
 
17:4:23.337|SOQL_EXECUTE_END|[10,30]|Rows:1|Duration:36

Number of query rows: 2 out of 1000

 
 
 
Message Edited by David81 on  03-10-2010 02:08 PM


groundwiredgroundwired
I am sad to say I just found a note in the current documentation
that confirms this - every row you touch counts against your governor
limit, not just the aggregate rows you get back. 
 
Interestingly, this note was added recently - it is not in the same topic of the PDF version of the Apex Reference.
 
Needless to say, this makes the aggregate feature a lot less useful, particularly in triggers.  


PekPek
I agree this is disappointing. 
Here is the workaround I used: I moved the logic of the trigger to a call and used @future notation. 
This increased the governor limit some. 
Hope this helps.
Pierre


gtuerkgtuerk
This is a real bummer.  I just had to deploy my class with zero code coverage so I could use the functionality because in the running of unit tests, the aggregate result does a full table scan.  My aggregate result, when run in Anonymous Apex, only includes 35 rows...  Well, I guess that's why you keep headroom for code coverage


voutchervoutcher
Just hit this problem myself. Here is a related idea to vote up: 


 


https://sites.secure.force.com/ideaexchange/ideaView?id=08730000000Br7TAAS