+ Start a Discussion
Streepie24Streepie24 

Too many query rows: 516

Hi All,

 

I know this issue is common... but i really am out of ideas of how to fix this error/limit

I tried numerous variants of code to solve this but nothing works...

 

!! PLEASE HELP   !!

 

Run Failures:
  TestClass2.GenericTestMethod3 System.DmlException: Update failed. First exception on row 0 with id a0R20000000XsgSEAS; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, Sluiten_AF_Taken: execution of BeforeUpdate

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

Trigger.Sluiten_AF_Taken: line 9, column 30: []

 

trigger Sluiten_AF_Taken on Achterstallige_factuur__c (before update) { //haal Id van SYSTEM op User sys = [SELECT Id FROM User WHERE Alias =: 'SYSTE']; //Hulplijst List<Achterstallige_factuur__c> afact=new List<Achterstallige_factuur__c>(); List<Task> toUpdate = new List<Task>(); List<Task> t=new List<Task>([SELECT Id, WhatId FROM Task WHERE CreatedById =: sys.Id and status != 'Afgesloten' ]); for(Achterstallige_factuur__c af: Trigger.new){ if(af.status__c == 'Afgehandeld'){ afact.add(af); } } for(Achterstallige_factuur__c aft: afact){ for(Task tk: t){ if(tk.WhatId==aft.Id){ tk.status = 'Afgesloten'; tk.IsReminderSet = false; toUpdate.add(tk); } } } update toUpdate; }

  

 

 

 

Message Edited by Streepie24 on 11-05-2009 01:28 AM
Best Answer chosen by Admin (Salesforce Developers) 
ThomasTTThomasTT
Obviously, query for Task is hitting too many records. However, you just need Tasks whose whatid is trigger target Achterstallige_factuur__c. So, why don't you put all Achterstallige_factuur__c ids on a Set and use it for the query (use where-in)? So you can query only what you want.
I recommend you to put in a Map with the ids as keys a d Tasks as values, so you can easily use them later.
However, this is an fundamental data problem. If 1 Achterstallige_factuur__c can have more than 1000 matching status Tasks, it's over. You need to consider batch apex or @futuer anyway.
ThomasTT

All Answers

ThomasTTThomasTT
Obviously, query for Task is hitting too many records. However, you just need Tasks whose whatid is trigger target Achterstallige_factuur__c. So, why don't you put all Achterstallige_factuur__c ids on a Set and use it for the query (use where-in)? So you can query only what you want.
I recommend you to put in a Map with the ids as keys a d Tasks as values, so you can easily use them later.
However, this is an fundamental data problem. If 1 Achterstallige_factuur__c can have more than 1000 matching status Tasks, it's over. You need to consider batch apex or @futuer anyway.
ThomasTT
This was selected as the best answer
BritishBoyinDCBritishBoyinDC

Specifically, to stop the problem of test scripts exceeding the query limits, I have to do something like this:

 

 

queryRowLimit = Limits.getLimitQueryRows(); if (queryRowLimit == 500) { //We are therefore in test mode: queryRowLimit = 100; //We set to 100 to make sure the page break code is being included but we don't exceed the governors }

Then I add a limit :queryRowLimit to each SOQL statement - you can set the variable when you are in test mode to any value, so long as it prevents the limits from being reached...

 

 

Streepie24Streepie24

OK that is clear,

 

Thanks ThomasTT

 

 

 

 

ThomasTTThomasTT

Thank you for your authorizing my post as a solution.

However, BritishBoyinDC made a good point. The error message says "Too many query rows: 516". In 1 execution, a trigger can handle 1000 x batch size query rows, however, in testmethod, it's 500.

 

You may have other triggers triggered in the same execution and it may already consumed 1000 x batch size - 516 query rows in this case... or, it is because testmethod.

 

So, when you implement testmethod, at least you need to apply my suggestion (not to query non-required Tasks) and use its own test data with small amount of records. Or/And, as BritishBoyinDC suggested, you can always use "LIMIT" to limit query rows only as you are allowed to query.

 

 

integer intLimit = Limits.getLimitQueryRows() - Limits.getQueryRows();

Task[] tskList = [
SELECT
....
LIMIT :intLimit];

if(Limits.getQueryRows() == 0) {

// The query didn't thorw any exceptoin, but it didin't query all Tasks requested.

// do some error handling like showing nicer errer message like "Too many Tasks"

}

 

So, it doesn't throw excepition in any case, so your testmethod will be automatically fine.

ThomasTT

 

 

 

 

Message Edited by ThomasTT on 11-05-2009 10:12 AM