Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
DbjensenDbjensen 

SOQL a million records

Hello - What is the best way to query a million leads? When a lead is created, I need to search all leads to see if there is a matching field. The matching field is a formula field called LastNameStreetAndZipCode__c. If a match is found, I'll add the Id of the matching lead to a lookup field on the new Lead. Below is the SOQL I have.  

public static void potentialDupes (List<Lead> newLead){

        //Lead Maps and list
        Map<String, Lead> mapOfLastNameStreet = new Map<String, Lead>();
        
        for(Lead ld : newLead){
            if(ld.PC_Submission_Id__c == null && ld.Lead_Id__c == null){
                mapOfLastNameStreet.put(ld.LastNameStreetAndZipCode__c, ld);
            } 
        }
        
        //query for existing leads that match new lead
        List<Lead> existingLead = [SELECT ID, LastNameStreetAndZipCode__c, Status, Home_Phone__c, CreatedDate 
                                   FROM LEAD 
                                   WHERE LastNameStreetAndZipCode__c IN :mapOfLastNameStreet.keySet()
                                   AND Duplicate__c != true
                                   ORDER BY CreatedDate ASC];
Raj VakatiRaj Vakati
Use some of the index fields in where condition and there are lot of best practies and refer this link 



Like this ..  
//query for existing leads that match new lead
        List<Lead> existingLead = [SELECT ID, LastNameStreetAndZipCode__c, Status, Home_Phone__c, CreatedDate 
                                   FROM LEAD 
                                   WHERE LastNameStreetAndZipCode__c IN :mapOfLastNameStreet.keySet() AND NAME!=NULL 
                                   AND Duplicate__c != true
                                   ORDER BY CreatedDate ASC];


https://developer.salesforce.com/blogs/developer-relations/2011/06/working-with-large-data-sets-in-apex.html
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm

https://trailhead.salesforce.com/en/content/learn/modules/large-data-volumes
https://trailhead.salesforce.com/en/content/learn/modules/large-data-volumes/conduct-data-queries-and-searches