Apex Code Development You need to sign in to do that
Don't have an account?
Eager-2-Learn Too many query rows...
Hello Once Again All,
I am really struggling with trying to understand how to get around these exhausting limits! I have over 16,000 records as of today that are in production/sandbox. I need to iterate through them all and this number is going to grow. How can I manipulate the code below to get around the limits. I just am not getting these work around requirements yet. I will but I am just a little slow on catch the ball sometimes. I appreciate your support.
This code is all around another post that I submitted on how to produce a report that shows Opportunities that have no related Opportunity Partner Records. The work around was to add a custom checkbox field on the opportunity object and use apex to check it no parters exist and uncheck it if one or more does exists. The code below a small portion of this. If I am this thing working I would like to post the enter class code in here for others because I saw this question by others.
opps = [ select id, MissingPartner__c, (select id from OpportunityPartnersFrom)
from Opportunity ];
}
for (Opportunity o : opps) {
if ( o.OpportunityPartnersFrom.size() > 0 ) {
o.MissingPartner__c = false; //Opportunity has partner record(s)
} else {
o.MissingPartner__c = true; //Opportunity is missing partner record(s)
}
}
The batch for loop that you see there takes a list of objects, not 1 object. Eager, in your example, the variable "o" refers to a list, so to set the field you want, you will have to iterate through that list. Essentially, you will have two for loops. The outside one is to handle the query, the inside one to handle the list results from the query.
But all of this is immaterial. Even if you do the for loop as the other poster suggested, you still have the limit of 10000 records in one query. And you will still have to address this process by splitting it up in some way, either through batches, or through a Trigger, or something else.
Jeremy
All Answers
one thing i know is write an asyncronous class with a future method so that it will iterate in a batch of 200 records.
I'd say you have a couple of options:
(1) Use a SOQL for loop - that will iterate the records in batches of 200. An example straight from the Apex Developer's Guide is:
// The sObject list format executes the for loop once per returned batch // of records i = 0; Integer j; for (account[] tmp : [select id from account where name = 'yyy']) { j = tmp.size(); i++; }(2) Use Batch Apex
Yeah, 16000 records is more than you can do in one pass (it's over 10000), unless you write a batch Apex class to handle it. This isn't that hard, but it may be that you can do what you want in another way.
For one thing, a Trigger can update your data in real time, so that you would never have to do one of these big batch queries. The Trigger would only update the Opportunity that's been changed, not all 16000 of them. Then you would only have to catch up the data that's out there already, in a one-time batch something like what you're proposing.
Here's an idea for doing the batch as you want to do it:
Split up your query so you can do it in separate manual batches. E.g.: [select id,... from Opportunity where CloseDate < 2009-01-01] then move forward in time so you get them all done. This is only practical if this is a one-time update, and not something that runs all the time.
Let me know how it goes!
Jeremy
Your thinking is right on and my query going into production will have a where clause. The work around is always jumping through hoops with Apex. I still would like to see a concrete example that works with a large number of records. Code should be dynamic and once in place it should work with data as it grows.
The prior gentlement posted the code below as a solution but I do not see what the i or j variables are even doing.
SFDC needs to raise the limits because 10k records is nothing in todays world. This is truly not acceptable in my book but my book only has a half a page. :smileyvery-happy:
// The sObject list format executes the for loop once per returned batch
// of records
i = 0;
Integer j;
for (account[] tmp : [select id from account where name = 'yyy']) {
j = tmp.size();
i++;
}
The only way to run one job on more than 10000 records is to write a Batch Apex class. The Apex Developers Guide has some examples on this. I have some batches that run on schedules to do complex logic overnight, for example.
I agree that 10000 records is not a lot of data. While they may raise their limits in the future, it's also worthwhile getting in the habit of coding within governor limits. In your case, for example, the only reason you need such a large query is that you have to bulk catch-up your existing data, and once you've done that, you should never have to do it again. Then, a Trigger only needs to run on changed records, and not everything in your org.
As much as I chafe against the limits sometimes, there do need to be some kind of boundaries, just to keep us frugal with resources. Treat all resources as scarce, and you'll have plenty.
Jeremy
Thanks for your continued help. I will just have to do the range solution for now. However, I noticed that you spoke on trigger several times. I do not think that I can do a trigger because on the Opportunity page, the related partners records do not seem to be accessible to the developer. I would have to be able to have a trigger on that related list when the opportunity record is saved, correct? I am not sure how to get to that object so that I can write a trigger? The related table is 'OpportunityPartnersFrom' and I would have to be able to know when a record is added and deleted wouldn't I?
This wasn't posted as a solution - its an example from the Apex Developer's Guide regarding SOQL for loops. The interesting bit is:
This means that each iteration through the loop pulls back 200 records at a time, removing the need to extract all of your records in a single query.
I appreciate the support but spoon feeding and playing 'airplane is coming' isn't working for me. I am looking for an example that makes complete sense. This is all pieced together and incomplete.
Does anyone have an example that shows how I will update?
I am fishing in dark deep waters here and just need a little more. I am not asking for the code to be written but being new, I do need a complete example.
This isn't working: It produces an error on o.MissingPartner__c = false;
for (opportunity[] o : [ select id, MissingPartner__c, (select id from OpportunityPartnersFrom)
from Opportunity ]) {
o.MissingPartner__c = false;
The batch for loop that you see there takes a list of objects, not 1 object. Eager, in your example, the variable "o" refers to a list, so to set the field you want, you will have to iterate through that list. Essentially, you will have two for loops. The outside one is to handle the query, the inside one to handle the list results from the query.
But all of this is immaterial. Even if you do the for loop as the other poster suggested, you still have the limit of 10000 records in one query. And you will still have to address this process by splitting it up in some way, either through batches, or through a Trigger, or something else.
Jeremy