Lead APEX Trigger (Non-selective query against large object type (more than 200000 rows))

Question

I want to write a trigger that creates a 6 digit code and checks if the code is unique.

First of all my trigger:

trigger LeadIdentCodeTrigger on Lead (before insert, before update) {  
for(Lead leadObject: Trigger.new) {    
    Boolean isUniqueIdent = true;

    // If lead cloned, check if ident is unique
    List<Lead> leadListWithSameIdent = [SELECT Id FROM Lead WHERE Ident__c=:leadObject.Ident__c AND Id!=:leadObject.Id LIMIT 1];
    if(leadListWithSameIdent.size() > 0) {
        isUniqueIdent = false;
    }

    if(leadObject.Ident__c == null || isUniqueIdent == false) {
        Boolean identExists = true;

        while(identExists == true) {
            // 1) Create new number

            identExists = false;

            // ---------------- Stringbuilder ------------------
            final String chars = '0123456789abcdefghijklmnopqrstuvwxyz';
            String ident = '';
            while (ident.length() < 6) {
                Integer idx = Math.mod(Math.abs(Crypto.getRandomInteger()), chars.length());
                ident += chars.substring(idx, idx+1);
            }
            // ------------------------------------------------

            // 2) Does the leadId exist?
            List<Lead> leadsWithSameIdent = [SELECT Id FROM Lead WHERE Ident__c=:ident AND Id!=:leadObject.Id LIMIT 1];
            if(leadsWithSameIdent.size() > 0) {
                identExists = true;
            }

            // 3) Yes => return Step 1, No => update lead
            if(identExists == false) {
                leadObject.Ident__c = ident;
            }
        }
    }
}
}

This code runs perfectly on our test system and creates unique 6 digit code. But when I deploy the code on production I get the following error:

LeadIdentCodeTrigger: execution of BeforeUpdate caused by: System.QueryException: Non-selective query against large object type (more than 200000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times) ()

I tried to mark the external id option of the Ident__c field, but it doesn't work - I get the same error.

In the past I tried to put the soql queries out of the for loop, but the same result and I saw the danger that I reach the limit of data size (because I have to get all the leads with an Ident__c value and this will be more than 200.000 leads in the future).

Now I don't have any idea to fix this problem. I hope someone can help me. Thanks!

Answer 1

You need to move that query out of the loop. Instead of querying each generated value separately, accumulate a set and query with an IN clause. Then, loop over the returned Leads and remove any duplicates from your generated ID values before populating them on your trigger set.

If your query is just an IN clause in your WHERE, filtering on an External Id field, which is indexed, that should cure your selectivity issues. You can process any Id exclusions in Apex.

That said, why not just use a native Auto Number field and save yourself the race condition that you cannot easily fix in this code? (What happens when two users insert a large number of leads at the same time?)