You need to sign in to do that
Don't have an account?

SOQL inside for loop of execute method of a batch class
The below batch class will be processing a little over 2.5 million records. The START method will be sending in the whole 2.5 million records to the execute method and the execute method does the processing on each of the 2.5 million records inside a for loop.
Also the for loop has a SOQL inside which I believe cannot be avoided to get the right numbers.
Is this the right way of doing this or are there any other better ways. Please help!
Also when the running the below batch class I get the First Error: Too many query rows error.
I request if someone could please help me with this, as I am dealing with this for some time, with no idea on how to fix this.
Also the for loop has a SOQL inside which I believe cannot be avoided to get the right numbers.
Is this the right way of doing this or are there any other better ways. Please help!
Also when the running the below batch class I get the First Error: Too many query rows error.
global class MDUSquadRawDataBatchTest implements Database.Batchable<sObject>, Database.Stateful { List<Address_Master__c> addressList = new List<Address_Master__c>(); Set<String> addresses = new Set<String>(); // Start Method global Database.QueryLocator start(Database.BatchableContext BC) { return Database.getQueryLocator('SELECT Street_Address__c,City_Name__c FROM MDU_Squad_Raw_Data__c'); } // Execute method global void execute(Database.BatchableContext BC, List<MDU_Squad_Raw_Data__c> rawData) { for(MDU_Squad_Raw_Data__c mduRawData: rawData) { List<MDU_Squad_Raw_Data__c> addressData = [SELECT Street_Address__c,City_Name__c,Province_Code__c,Postal_Code__c,Internet_Service__c,Video_Service__c,Phone_Service__c FROM MDU_Squad_Raw_Data__c WHERE Street_Address__c=:mduRawData.Street_Address__c AND City_Name__c=:mduRawData.City_Name__c]; String fullAddress = addressData[0].Street_Address__c+' '+addressData[0].City_Name__c+' '+addressData[0].Province_Code__c+' '+addressData[0].Postal_Code__c; Address_Master__c theAddress = new Address_Master__c(); if(!addresses.contains(fullAddress.substringBeforeLast(' '))) { theAddress.Name = addressData[0].Street_Address__c; theAddress.City_Name__c = addressData[0].City_Name__c; theAddress.Province_Code__c = addressData[0].Province_Code__c; theAddress.Postal_Code__c = addressData[0].Postal_Code__c; fullAddress = addressData[0].Street_Address__c+' '+addressData[0].City_Name__c+' '+addressData[0].Province_Code__c+' '+addressData[0].Postal_Code__c; theAddress.Full_Address_Ext_Id__c = fullAddress; addresses.add(fullAddress.substringBeforeLast(' ')); addressList.add(theAddress); } } Database.Upsert(addressList, Address_Master__c.Fields.Full_Address_Ext_Id__c, true); } // Finish Method global void finish(Database.BatchableContext BC) { } }
I request if someone could please help me with this, as I am dealing with this for some time, with no idea on how to fix this.
Regards
Andrew
All Answers
loop the raw data to build a list of elements that you want to use in your query. e.g. street and city
once loop has finished, do the query using those elements
build a map using street and city as keys
loop the raw data and search the Map using the key
update the record if key found and add to list
once loop finishes, update the list
this if fairly standard process for bulking code where a query is involved in the update of records.
Regards
Andrew
Regards
Andrew
To answer your question re: the SOQL query:
That loop would return definitely only the records that you are chasing.The data that is returned will contain all the address data that you require. In fact, it is highly likely to return more addresses than required.
If we go back a step and consider the two lists we are creating and do some assumptions on the data passed.
Lets assume that we had 2 x initial rawData records with addresses as
First Avenue in Big City
Second Avenue in Other City.
What the lists will contain is (First Avenue:Second Avenue) and (Big City:Other City)
When we run the query it will return all address records with combinations of the two lists so, it may return
First Avenue in Big City
Second Avenue in Other City
but also
First Avenue in Other City
Second Avenue in Big City
assuming those combinations also exist in your address data.
As I now re-read your code, it is possible that a better loop to start would be:
But i think you would need to adjust your initial start method
Also, Morales is correct in that the error is due to the nature of the query. SInce the initial query is non selective, it can throw an error.
Consider if there is a way to reduce the nature/number of records returned by perhaps throwing in a WHERE clause on the query.
Regards
Andrew
Below is what I have as Id's in my custom objects MDU_Squad_Data_Raw
I believe, I will still be having duplicates on my initial rawData, if I go by Id's. As you said, I wish we didn't have any duplicates in the initial rawData.
Why are there multiple records for "10 Dover PTSE" in "Calgary"?
What is the difference between the records as shown?
Regards
Andrew
Below is the final code I have. My code worked good when I had 20000 records and now I bumped it up to 50000 which I when I get the Toom many DML rows error.
Eventually this code to supposed to run on a record set of 2.5 million records.
Please help!
On the finish method of the above class I have the below:
Please help!
Thanks!
1. reduce the number of records returned by the initial query , or
2. chain batch jobs together with smaller queries
The other option would be to revisit the business requirement and determine what they are actually trying to do.
The code takes "raw data" and updates/inserts the "Address Master".
Questions for the business:
Once the Address Master exists, do we need the Raw Data record? Can we delete it?
Other Questions
If we can't delete it, can we mark it as processed?
possible solution if we can mark the raw data as processed.
If we can mark it processed, can we then update the batch query to ignore processed records?
If so adjust the query to have a LIMIT of 10000 and set the WHERE clause to ignore processed records
and then in processing, update the flag on the raw data record
Set the schedule to run the batch
Regards
Andrew
Start Method: The signature of this method is
global (Database.QueryLocator | Iterable<sobject>) start(Database.BatchableContext bc) {}
This method is used to collect the records that we want to process. This method can return either Database.QueryLocator or an Iterable. The Database.QueryLocator return type is used when we want to run a simple select statement. With this return type, the select statement can return up to 50Million records. When we want to write a custom logic (like aggregations), then we have to use the Iterable return type. With this return type, the batch can only process a maximum of 50K records.(https://upser.online/)
global (Database.QueryLocator | Iterable<sobject>) start(Database.BatchableContext bc) {}
This method is used to collect the records that we want to process.
Bigg Boss Contestants List (https://teqip.in/bigg-boss-contestants-list.html)