2

I am writing a simple trigger to tag lead duplicates based on two different fields, Email and our internal Lead ID (which we keep consisted between our own server DB and salesforce via integration).

Here is the initial trigger setup and the queries themselves, but I am getting Non-selective Query errors...

Map<ID, String> dupeFinderMap = new Map<ID, String>();
Map<String, ID> leadEmailMap = new Map<String, ID>();
Map<String, ID> leadILIDMap = new Map<String, ID>();

if(!trigger.isDelete) {
    for (Lead l : trigger.new){
        dupeFinderMap.put(l.id, '');
        leadEmailMap.put(l.Email, l.id);
        leadILIDMap.put(l.Internal_Lead_ID__c, l.id);
    }
} else {
    for (Lead l : trigger.old) {
        dupeFinderMap.put(l.id, '');
        leadEmailMap.put(l.Email, l.id);
        leadILIDMap.put(l.Internal_Lead_ID__c, l.id);
    }
}

List<Lead> dupeEmailLeads = [SELECT Id, Email, Internal_Lead_ID__c FROM Lead WHERE Email IN :leadEmailMap.keySet() AND Id NOT IN :dupeFinderMap.keySet()];
List<Lead> dupePILIDLeads = [SELECT Id, Email, Internal_Lead_ID__c FROM Lead WHERE Internal_Lead_ID__c IN :leadPILIDMap.keySet() AND Id NOT IN :dupeFinderMap.keySet()];

Is it something to do with the fact that I'm checking against a map keyset? Or is it the In or NOT IN conditions? Is there a better way to build these queries? My goal is to collect all Leads that have Email or Internal Lead ID the same as a Lead in the trigger, but are not in the trigger themselves.

1
  • When ever you are doing update or insert on before or after events you were looping though and creating the maps. One thing I noticed here every time you are creating all the maps and you used them in the query, if you observe closely in the where condition every record will have an instance in each map so the conditions it self fail to fetch the records. if you want to recheck write some debug logs and see weather you were fetching any records or not from your quries.
    – Suresh
    May 7, 2015 at 16:10

2 Answers 2

2

You should check for null before adding email in Map, as it may have null or blank value. Other suggestion is dont use 'NOT' operator in SOQL. You should query all records and then iterate to check if email not exist in Apex.

Also you can combine two SOQL, Please try below code :

Map<ID, String> dupeFinderMap = new Map<ID, String>();
Map<String, ID> leadEmailMap = new Map<String, ID>();
Map<String, ID> leadILIDMap = new Map<String, ID>();

if(!trigger.isDelete) {
    for (Lead l : trigger.new){
        dupeFinderMap.put(l.id, '');
        if(l.Email != null && l.Email != '')
        {
            leadEmailMap.put(l.Email, l.id);
        }
        leadILIDMap.put(l.Internal_Lead_ID__c, l.id);
    }
} else {
    for (Lead l : trigger.old) {
        dupeFinderMap.put(l.id, '');
        if(l.Email != null && l.Email != '')
        {
            leadEmailMap.put(l.Email, l.id);
        }
        leadILIDMap.put(l.Internal_Lead_ID__c, l.id);
    }
}

List<Lead> dupeEmailLeads = null;
List<Lead> dupePILIDLeads = null;

if(!leadEmailMap.keySet().isEmpty())
{
    dupeEmailLeads = [SELECT Id, Email, Internal_Lead_ID__c FROM Lead WHERE Email IN :leadEmailMap.keySet() ]; 
}

if(!leadILIDMap.keySet().isEmpty())
{
    dupePILIDLeads = [SELECT Id, Email, Internal_Lead_ID__c FROM Lead WHERE Email Internal_Lead_ID__c IN :leadILIDMap.keySet()]; 
}

if(leadEmailMap != null)
{
    //Iterate through above records and process
}

if(leadILIDMap != null)
{
    //Iterate through above records and process
}

Please go though this documentation to understand How to write efficient queries for LDV and some high level LDV questions and answers.

9
  • I have done all of this, checked for null/blank email values used the single query exactly as you show here, but I am still getting non-selective query errors.
    – OBerm
    May 7, 2015 at 17:56
  • Ohhk, How many records do you have in Lead Object ? May 7, 2015 at 17:58
  • In production we have almost 250k, but in the sandbox where I am building this we have zero (error occurs when I try to create one).
    – OBerm
    May 7, 2015 at 18:02
  • 250k is not big volume.. I have updated code, can you check ? May 7, 2015 at 18:07
  • Nope, still giving the error when I try to save a new Lead...
    – OBerm
    May 7, 2015 at 18:21
1

The check id not in list will require the complete table scan which will error out when data is huge. Please try the below version.

Map<ID, String> dupeFinderMap = new Map<ID, String>();
Map<String, ID> leadEmailMap = new Map<String, ID>();
Map<String, ID> leadILIDMap = new Map<String, ID>();

if(!trigger.isDelete) {
    for (Lead l : trigger.new){
        dupeFinderMap.put(l.id, '');
        leadEmailMap.put(l.Email, l.id);
        leadILIDMap.put(l.Internal_Lead_ID__c, l.id);
    }
} else {
    for (Lead l : trigger.old) {
        dupeFinderMap.put(l.id, '');
        leadEmailMap.put(l.Email, l.id);
        leadILIDMap.put(l.Internal_Lead_ID__c, l.id);
    }
}
List<Lead> dupeEmailLeads = new List<Lead>();
List<Lead> dupePILIDLeads = new List<Lead>();
for( Lead l: [SELECT Id, Email, Internal_Lead_ID__c FROM Lead WHERE Email IN :leadEmailMap.keySet()])
{
    if(!dupeFinderMap.containksKey(l.id))
    {
        dupeEmailLeads.add(l);
    }
}

for(Lead L: [SELECT Id, Email, Internal_Lead_ID__c FROM Lead WHERE Internal_Lead_ID__c IN :leadPILIDMap.keySet()])
{
    if(!dupeFinderMap.containksKey(L.id))
    {
        dupePILIDLeads.add(l);
    }
}

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .