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
Greg HGreg H 

System.Exception: Too many query rows

I may be overlooking something here and am hoping someone can point me in the correct direction.  I am building a class which will eventually be called from an sControl.  I basically want to summarize opportunity data in the class and return that summarized data to my sControl where I can control the display/format of it.
 
I am just getting started with my class and am already encountering an exception with the number of query rows.  My Class is below (and I know that I do not have the summary organization done yet):
Code:
global class getOppSummary2 {
 webService static Opportunity[] getOpps(String regionVal, Date startDate, Date endDate, Date lastNinetyDays) {
  Map<Id, User> allUsers = new Map<Id, User>();
  for (User[] usrArray : [SELECT Id FROM User WHERE Reporting_Region__c = :regionVal AND IsActive = true]) {
   for (Integer a = 0; a < usrArray.size(); a++) {
    allUsers.put(usrArray[a].id,usrArray[a]);
   }
  }
  Opportunity[] allOpps = new Opportunity[]{};
  for (Opportunity oppArray : [SELECT AccountId,Account.Name,Amount,CloseDate,CreatedById,CreatedBy.Username,First_Appointment_Date__c,Id,MFTC_DUNS_Number__c,Name,OwnerId,Owner.FirstName,Owner.LastName,RecordTypeId,Revenue_Type__c,StageName FROM Opportunity WHERE OwnerId in :allUsers.keySet() AND ((IsClosed=false AND CloseDate < :startDate AND StageName<>'Targeted Account (No Forecasted Sale)') OR (IsClosed=true AND (CloseDate >= :lastNinetyDays AND CloseDate <= :startDate) AND (StageName='Closed Won' OR StageName='Closed Won/Contract')) OR (IsClosed<>true AND (StageName='Targeted Account (No Forecasted Sale)' OR StageName='Fallback Opportunity')) OR (IsClosed<>true AND (CloseDate >= :startDate AND CloseDate <= :endDate) AND (StageName='First Time Visit Completed' OR StageName='Proposal/Pricing Presented' OR StageName='Verbal Agreement' OR StageName='First Order')))]) {
   allOpps.add(oppArray);
  }
  return allOpps;
 }
 static testMethod void getOppSummaryTest() {
  getOpps('West',Date.newInstance(2008,03,12),Date.newInstance(2008,06,12),Date.newInstance(2007,12,31));
 }
}

My question is why am I encountering this exception?  Am I not looping through the map correctly?  I have over 4,000 users and way more opportunities to churn through which is why I want to do this in a class.
 
Thanks for any assistance,
-greg
sparkysparky
Have you read the section in the apex documentation on governor limits?

Check to see if the total rows selected by your two queries there go over the limits.  If so, you'll have to figure out a way to reduce how many rows you query for.
Ron HessRon Hess
arrays can hold 1000 members.

you appear to be exceeding this limit

to test your code, add "Limit 1000 " to the user query and then see if you hit any more limits when you get to the opp query

i suspect that listing 1000 user ID's in your where clause ( userid in :allUser.keyset() ) is streching the size of a SOQL query...
VarunCVarunC
Any info, on how to handle the processing if I have over 500 records returned from a Query and I need to SUM them up and store the value, but since there is a LIMIT on Max Quer Rows = 500 I COULD NOT read more than 500 records but then How can I Implement my processing :( ... I cannot process on 500 records if I have 600 records .. :( ... any idea or help ... plz ...
SuperfellSuperfell
Why don't you just use an RollUp Summary field to do this?
VarunCVarunC

Firstly, I'm working on a Managed Released package code :(. I cannot DELETE or CHANGE field type.

 

Secondly, for ROLLUP Summary fields i think we need to make Master Detail relationships between objects, which I do not have between my objects its simple lookup relationships.

 

:(
SuperfellSuperfell
You're not going to be able to do it by recalculating the new total, at some point, which you may already be past, there's too many rows to do this, instead you should workout the delta caused by the set of rows in the trigger and apply that change. (this is how RSF's work).
VarunCVarunC
SimonF wrote:
You're not going to be able to do it by recalculating the new total, at some point, which you may already be past, there's too many rows to do this, instead you should workout the delta caused by the set of rows in the trigger and apply that change. (this is how RSF's work).

 

Sorry I didn't catch your comment :(. Can you please elaborate more on this please.

 

FYI, here is my condition of issue for which I'm looking to find solutions:

ok .. here is my concern and issue :(

I have Obj-A. Obj-A have trigger on After Insert and Update.

In Trigger for insert and update of Obj-A record, I need to fetch All records from Obj-A Where Contact__c = Obj-A.Contact__c and I Also need to fetch All records from Obj-A where Lead__c = Obj-A.Lead__c. So ultimately Im fetching around 600 records from SAME object in two Queries in 2 triggers.

So would not the solution of integrating the triggers and then Fetching the Results in this query:

Select Id From obj-A Where Lead__c = Obj-A.Lead__c OR Contact__c = obj.Contact__c] will throw me SAME error of Governor Limit being reacched  with 600+ records retruning. 

I'm really strugling to figure out, how can I avoid the Goernor limit, If I need to fetch 500+ records and then do some processing based on those Fields returned in the records and then Update SINGLE records with the Calculations I did on the set of 600+ records :(.

I'm strugling to find ANY solution. I CANNOT just put in LIMIT 500 in query because that is INCORRECT dataset, I need to process ALL records that match my criteria, and I ALSO do not want running into Governor Limits.

Is there any reliable solution or Example of Beaking my 600+ records into BATCH of 500 records and then I do SAME processing on 500 record's Batch, WITHOUT hitting Governor Limit.


 


SuperfellSuperfell
If you're trying to sum child rows, there's no need to re-read all the child rows just because you've added a new child, the new sum will be the old sum plus the change from the new row.


VarunCVarunC
here is my trigger code: 
        for (Billing__c b : trigger.new) {
            if (b.Matter__c != null) {
                Billing__c[] bi = [Select Id, Total_Amount__c From Billing__c Where Matter__c =: b.Matter__c];
                Account ac = new Account(Balance__c = 0);
                for (Integer i=0;i<bi.size();i++)
                    if (bi[i].Total_Amount__c != null)
                        ac.Balance__c += bi[i].Total_Amount__c;
                Matter__c m = new Matter__c(Id = b.Matter__c);
                m.Actual_Billings__c = ac.Balance__c;
                update m;
            }
        }
 
Do you think I can concise this to REMOVE the query row limit exceed of 500 :) ... Actually i didn't get the meaning of your last comment, how can I make sure that my next row is sum of all old values ?
Message Edited by vchaddha on  03-10-2009 09:16 AM


SuperfellSuperfell
You can start by not doing the query inside the loop, see any of the posts on this forum about making triggers bulk safe.


VarunCVarunC
yes did that already .... doing that MAKE my query return more than 630 records :(


SuperfellSuperfell
there's no 630 row limit. and again if you're summing child data, there's no need to re-read all the existing rows, just apply the delta.


VarunCVarunC
Here is my error :( ...
 
BillingCalculations: execution of AfterUpdate
caused by: System.Exception: Too many query rows: 630
 
AND Please tell me what is DELTA you did mentioned this in 2 post back also in this thread, I'm NOT sure how to do that. I do SUMMING up some column value and Updating Tthat SUMMED up value in 3rd object's field.
 
Though FYI I didn't tried moving Query before FOR loop, BUT that will FORCE me to READ ALL data in the object without the Contact criteria, don't you think that WILL surely hit me with returning "Too many query rows" error, since that table itself conmtains around 17-1800 records ?


SuperfellSuperfell
if the trigger adds a new row with value 10, all you need to do is add 10 to the existing total, there's no need to read the exsiting hundreds of rows to sum them, because you already know what they add up to, just apply the change to the total.


SuperfellSuperfell
You can also collect up all the ids for your where clause first, then use that set of ids in the query, no need to query everything.


VarunCVarunC
Ok I will try this and will try t concise me code too, and will Let you know my findings .. THANKS a lot for all this :) ...


VarunCVarunC
SimonF wrote: 
You can also collect up all the ids for your where clause first, then use that set of ids in the query, no need to query everything. 
 
Can you tell me this thing too? 
 
If I'm doing it correctly for Updating a Contact I'm running this code:
           for (Billing__c b : trg_b) {
                ......                
                Contact c = new Contact (Id = c.Matter__c);
                c.Pending_Billing__c = xyzvariable;
                con_list.add(c);
            }

            update con_list;
 
In this I'm directly initializing the Variable I thought I Make a step ahead in SAVING one SELECT query to Fetch the Contact record for that Id and then updating it. Or is It Same as this ???
 
Contact c = [Select Id From Contact Where ID =: b.Contact__c];


VarunCVarunC
Hi ... I've sorted things out a bit ... but I still have been running into this Error:
 
Too many SOQL queries: 101
I'm now trying to fetch things in a query like 
 
contact[] contacts = [Select Id, Name, (Select Id, Name From Billings__r) From Contact Where ID IN : contactids];
 
Can you tell me if this is incoreect procedure to call for child records and update parent record's field value based on the values of child records fetched in above query ? Is this NOT Part of BEST PRACTICES for Not Hitting Governor Limits ? Please do tell me .. :(