1

I have the dreaded Non-Selective query error but I am not sure why. I am on the Lead object and querying against 3 fields which are indexed so I am not sure why I am getting this error. I do have over 200K records.

The error is related to the Lead query. Do I need to put the Lead query into a for Loop?

I am trying to associate Leads to Warranty Record where the email address are the same on both record.

trigger Warranty_Add_Leads on Warranties_and_Surveys__c (after update) {

    List<Lead> Leads = new List<Lead>([
        SELECT Account_Type__c,Email,Id,Name 
        FROM Lead 
        WHERE Email like '%@%' AND RecordTypeId  = '012j0000000gcNp' AND Name != '' 
            AND Account_Type__c = 'Web Application'
    ]);

    List<Lead> lstLeads = new List<Lead>(); // added ;()
    List<Warranties_and_Surveys__c> warranty = new List<Warranties_and_Surveys__c>([select id, Contact_Email__c from Warranties_and_Surveys__c where Contact_Email__c != '' AND Registration_Status__c = 'Registered' ]);

    for(Warranties_and_Surveys__c WTY : Trigger.new)
    {
        for (Lead LL : Leads) 
        {
            if(LL.email == WTY.Contact_Email__c) 
            {  
                WTY.Leads__c = LL.id;
                lstLeads.add(ll);
            }
        }   
    } 

    if (lstLeads.size()>0)
        update lstLeads;  
}
0
3

Figuring out the selectivity of SOQL queries is something of a dark art. I've sat through several presentations on it, and I find that it still boggles my mind.

Salesforce does provide a 'cheat sheet' that covers some details about what Salesforce considers a 'selective' query, the query & search optimization cheat sheet

From that, we can see that your query hits on several of the things that can make a query non-selective (query selectivity is determined solely by the WHERE clause of that query)

  • Email LIKE '%@%' has a leading wildcard
  • Name != null is a negative filter, and it uses null as well (which is also bad for performance)

The Email LIKE '%@%' filter doesn't make much sense to me, and Name != null absolutely needs to be removed. With the code that you've provided, it doesn't look like that second filter has any practical use. If you really do need to ignore Leads without a name, you can do that outside of the query (i.e. in Apex).

Supposing that we remove those two problematic filters from your query, that leaves us with only one filter on an indexed field, RecordTypeId = '012j0000000gcNp'.

That alone may not make your query selective enough. We'll need to come up with some extra data that you can plug into a filter on an indexed field. Lucky for you, there is something readily available for you to use, emails!

No, you didn't read that wrong. We're going to use emails as a filter for your query on Lead. The difference is that we'll be switching perspectives a bit. Instead of querying for all Leads with any email, and later seeing if it matches any of our Warranties_and_Surveys__c, we'll be gathering the emails from Warranties_and_Surveys__c, and finding the Leads that match those emails.

An example of what I'm talking about is this

// This doesn't need to be a Set, that's just my preference.
// It could be a List, but you'd likely need to check for nulls inside of
//   the loop (because it's harder to remove things from a list than it is 
//   to stop them from being put into it in the first place).
Set<String> emails = new Set<String>();

for(Warranties_and_Surveys__c was :trigger.new){
    emails.add(was.email);
}

// If there happens to be a null email in our set, we want to get rid of it.
// Otherwise, it may degrade the selectivity of the filter used in the query below
emails.remove(null);

// Your existing query, but with the problematic filters removed,
//   and a better filter on Email put into place to (hopefully) make the query
//   very selective.
List<Lead> Leads = new List<Lead>([
        SELECT Account_Type__c,Email,Id,Name 
        FROM Lead 
        WHERE Email IN :emails AND RecordTypeId  = '012j0000000gcNp'
            AND Account_Type__c = 'Web Application'
    ]);
7
  • Ah yes, we identified the same culprits, but you're probably right that outright removal makes more sense. – Adrian Larson Feb 17 '17 at 17:55
  • Wow. Thanks to you both for the informative summaries. I tried Derek's approach first but it still exploded on my existing query. List<Lead> Leads = new List<Lead>([SELECT Account_Type__c,Email,Id,Name FROM Lead WHERE RecordTypeId = '012j0000000gcNp' AND Account_Type__c = 'Web Application']); – Michael Feb 17 '17 at 18:47
  • @Michael The point is you should filter on an Email whitelist, only getting back records you know will be relevant. Look at the Set<String> emails logic and make sure you include that. – Adrian Larson Feb 17 '17 at 18:59
  • User error. I thought I saved the modified trigger but I not. Too many windows opened at the same time..... I will try again. Thx – Michael Feb 17 '17 at 19:08
  • So after testing a record it gave me a read only and I believe it had to do with the fact that I had the trigger set for after update. Changed to before update and I did not receive an error. But it also did not associate the Lead to the Warranty. Is there a way to confirm that there are values in the initial Warranty Set? Also, as the code is now. Will this add the same lead multiple times? Thank you for your help.....M – Michael Feb 20 '17 at 13:32
1

Have a read of this Developer's Blog post. Querying for Name != '' is the likely culprit here, but you're not helping yourself by searching for %@% either. Neither of these filters will be indexed. How to replace them? I would use a formula checkbox, myself. You might do one each for Name and Email:

HasName__c

NOT(ISNULL(Name))

HasValidEmail__c

CONTAINS(Email, "@")

It's not immediately clear if the CONTAINS formula will actually resolve the issue, but it's worth a shot.

With the above in place you can then rewrite your query without any of the offending filters:

WHERE HasName__c = true AND HasValidEmail__c = true AND ...

Here's the relevant portion of the aforementioned blog post:

Using Leading % Wildcards

A LIKE condition with a leading % wildcard does not use an index.

SELECT id FROM Account WHERE Name LIKE ‘%Acme%’

This is the type of query that would normally work better with SOSL. However, if you need real-time results, an alternative is to create a custom search page, which restricts leading % wildcards and adds governance on the search string(s).

Note: Within a report/list view, the CONTAINS clause translates into ‘%string%’.

Using NOT and !=

When your filter uses != or NOT—which includes using NOT EQUALS/CONTAINS for reports, even if the field is indexed—the Force.com query optimizer can’t use the index to drive the query. For better performance, filter using = or IN, and the reciprocal values.

Consider this example. You have 1 million cases and a custom index on the Status field, which has the following values and distribution of returned records.

New       50,000
Closed   880,000
On Hold   20,000
Pending   30,000
ReOpened  20,000

This query won’t use the index on the Status field because of the !=. Use the reciprocal values instead. Also note that if you take the four Status values from the updated query, the record count (50,000 + 20,000 + 30,000 + 20,000) meets the selectivity threshold.

SELECT id FROM Case WHERE Status != ‘Closed’
SELECT id FROM Case WHERE Status IN (‘New’, ‘On Hold’, ‘Pending’, ‘ReOpened’)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.