How to avoid this "Too many query rows: 50001"?

Question

I have an afterInsert trigger for finding duplicates leads and if duplicate lead is inserted take its values and put it in a custom object record and lastly delete this new duplicate Lead record.

It seems to me The cause of error is 1st query, as it will query all records in the system.

trigger LeadDuplicateTrigger on Lead(after insert) {

    List<Lead> Exleads = [Select id, name, email, MobilePhone, Program__c From Lead WHERE Id NOT IN : Trigger.new ];
    List <Duplicate_Lead__c> DupLeads = new List <Duplicate_Lead__c>();
    List<Lead> dupLeadsDelete = new List<Lead>();

    for(Lead ld : Trigger.new){ 
        for(Lead Ele : Exleads){

            if(( (ld.email != null && ld.email == Ele.email) && (ld.Program__c != null && ld.Program__c==Ele.Program__c) ) || ( (ld.MobilePhone != null && ld.MobilePhone == ELe.MobilePhone) && (ld.Program__c != null && ld.Program__c==Ele.Program__c) )){
            Duplicate_Lead__c DPLead = new Duplicate_Lead__c();

            DPLead.Name                = 'Duplicate Of'+' '+ Ele.Name;
            DPLead.Lead_Name__c        = ld.FirstName+ ' '+ld.LastName ;
            DPLead.Email__c            = ld.Email;
            DPLead.Mobile__c           = ld.MobilePhone;
            DPLead.Work_Experience__c  = ld.Work_Experience__c;
            DPLead.program__c          = ld.Program__c;
            DPLead.Lead_City__c        = ld.Lead_City__c;
            DPLead.Lead_State__c       = ld.Lead_State__c;
            DPLead.Lead_Country__c     = ld.Lead_Country__c;             

            DupLeads.add(DPLead);

            Lead leadDel = new Lead(Id=ld.Id);
            dupLeadsDelete.add(leadDel);
            } 

        }

    }


    if(!DupLeads.isEmpty()){
        Insert DupLeads;
    }

    if(!dupLeadsDelete.isEmpty()){
        Delete dupLeadsDelete;
    }
}    

Answer 1

You should filter only records that can be matched. You will get some extra results with the below approach, but hopefully fewer than 50k. Also note that NOT IN filters will give you selectivity problems if your table goes over 100k records total.

Set<String> emails = new Set <String>();
Set<String> emails = new Set <String>();
// etc. 

for (Lead record : trigger.new)
{
    emails.add(record.Email);
    programs.add(record.Program__c);
    // etc. 
}

emails.remove(null);
programs.remove(null);
// etc. 

List<Lead> potentialDuplicates = [
    SELECT ... FROM Lead
    WHERE Email IN :emails
    AND Program__c IN :programs
    // etc. 
];

But that still may not be enough. It would be more robust to move your logic to a batch and process asynchronously.

Answer 2

This looks to me like you should be using duplicate management rules instead of dedupe'ing in a trigger.

In setup go to:

setup >> Data.com Administration >> Duplicate Management

From there you can create your duplication and matching rules and how to handle them.