+ 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