+ Start a Discussion
Amit VaidyaAmit Vaidya 

Non-selective query against large object type (more than 100000 rows).

Error:

Test failure, method: NewMDPFormSubmitTest.createRecordsOnMDPFormInsertTest -- System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, MDPFormSubmissionTrigger: execution of AfterInsert

caused by: System.QueryException: Non-selective query against large object type (more than 100000 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 am getting above error for following query:

 

SELECT id, Context_ID__c, Name from Account where Context_ID__c =: practiceid AND Context_ID__c != '' order by LastModifiedDate desc

 

This query i have used in following code for after insert trigger:

 

public with sharing class NewMDPFormSubmit {

public static void createRecordsOnMDPFormInsert(List<MDP_Form_Submission__c> newMDPForms) {

//List<Account> accs = [SELECT id, Context_ID__c, Name from Account where Context_ID__c != '' order by LastModifiedDate desc];
List<Contact> cList = new List<Contact>();
List<Opportunity> oppList = new List<Opportunity>();
List<CampaignMember> cmpgnmemListForContact = new List<CampaignMember>();
List<OpportunityLineItem> oliList = new List<OpportunityLineItem>();
List<Task> tListForOpportunity = new List<Task>();
List<Partner_Lock__c> plList = new List<Partner_Lock__c>();
List<Lead> lList = new List<Lead>();
List<CampaignMember> cmpgnmemListForLead = new List<CampaignMember>();
List<Task> tListForLead = new List<Task>();

List<String> s = new List<String>();
for(MDP_Form_Submission__c mdps: newMDPForms){
s.add('MDP -'+mdps.Product_Name__c);
}

List<Campaign> cmpgn = [SELECT Id, Name from Campaign where Name IN: (s)];
List<Partner_Contract__c> pcWithMatchProductName = [SELECT Id, Name from Partner_Contract__c where Name IN: (s)];

for(MDP_Form_Submission__c m: newMDPForms){

String practiceid = m.Practice_ID__c;
if(practiceid.length() < 5 ){
Integer strlength = 5- practiceid.length();
for(Integer i=0; i<strlength; i++){
practiceid = '0'+practiceid;
}
}else if(practiceid.length() > 4){
practiceid = practiceid;
}
List<Account> newAcc = new List<Account>();
if(practiceid != null && practiceid.length() == 5){
newAcc = [SELECT id, Context_ID__c, Name from Account where Context_ID__c =: practiceid AND Context_ID__c != '' order by LastModifiedDate desc];
}
/*for(Account ac: accs){
if(practiceid == ac.Context_ID__c){
newAcc.add(ac);
}
}*/
if(newAcc.size() > 0){
//if(practiceid == newAcc[0].Context_ID__c){
//Insert Contact related to Account
Contact con = new Contact();
con.Accountid = newAcc[0].id;
con.FirstName = m.First_Name__c;
con.LastName = m.Last_Name__c;
con.Email = m.Email__c;
con.Phone = m.Phone_Number__c;

if(m.Receive_athenahealth_News__c == false){
con.HasOptedOutOfEmail = true;
}else{
con.HasOptedOutOfEmail = false;
}
if(m.Receive_athenahealth_News__c == true){
con.Invite_Opt_In__c = true;
con.Newsletter_Opt_In__c = true;
con.Stream_Opt_In__c = true;
}else{
con.Invite_Opt_In__c = false;
con.Newsletter_Opt_In__c = false;
con.Stream_Opt_In__c = false;
}
cList.add(con);

//Insert Opportunity related to Account
Opportunity o = new Opportunity();
o.AccountID = newAcc[0].id;
o.Name = newAcc[0].Name +'- MDP Partner:'+m.Product_Name__c;
o.StageName = 'Qualification';
o.CloseDate = System.Today();
o.Type = 'New Service';
oppList.add(o);

//Insert Partner Lock related to Account
Partner_Lock__c pl = new Partner_Lock__c();
pl.Account__c = newAcc[0].id;
if(pcWithMatchProductName.size() > 0){
pl.Partner_Contract__c = pcWithMatchProductName[0].id;
}
plList.add(pl);
//}
}else {
//If no matching Accounts found then create Lead
Lead l = new Lead();
l.Company = m.Practice_Name__c;
l.FirstName = m.First_Name__c;
l.LastName = m.Last_Name__c;
l.Email = m.Email__c;
l.Phone = m.Phone_Number__c;

if(m.Receive_athenahealth_News__c == false){
l.HasOptedOutOfEmail = true;
}else{
l.HasOptedOutOfEmail = false;
}
if(m.Receive_athenahealth_News__c == true){
l.Invite_Opt_In__c = true;
l.Newsletter_Opt_In__c = true;
l.Stream_Opt_In__c = true;
}else{
l.Invite_Opt_In__c = false;
l.Newsletter_Opt_In__c = false;
l.Stream_Opt_In__c = false;
}
lList.add(l);
}
}

//Insert Contacts if there are matching Accounts
if(cList.size() > 0){
insert cList;
}
//For inserting Campaign Member to related Contact
for(Contact c:cList){
CampaignMember cm = new CampaignMember();
cm.ContactId = c.id;
if(cmpgn.size()>0){
cm.CampaignId = cmpgn[0].id;
}
cm.Status = 'Responded';
cmpgnmemListForContact.add(cm);
}
if(cmpgn.size()>0){
if(cmpgn[0].id != null){
insert cmpgnmemListForContact;
}
}

//Insert Opportunities if there are matching Accounts
if(oppList.size() > 0){
insert oppList;
}
//For inserting Opportunity Services to related Opportunity
for(Opportunity opp:oppList){

OpportunityLineItem oli = new OpportunityLineItem();
oli.OpportunityId = opp.id;
oli.PricebookEntryId = '01uG000000CuPnK';
oli.MDP_Application__c = newMDPForms[0].Product_Name__c;
oli.Quantity = 1;
oli.UnitPrice = 0;
oliList.add(oli);//Adding Opportunity Services

Task t = new Task();
t.WhatId = opp.id;
t.OwnerId = '005G000000366nY';//Replace this with '005G000000366nY' while deploying to production
t.Subject = 'MDP Storefront Submission';
t.Task_Type__c = 'Outbound Call';
t.ActivityDate = System.Today();
t.Priority = '3';
t.Status = 'Not Started';
t.Description = 'Form Type: '+newMDPForms[0].Form_Type__c+'\n'+'Product Name: '+newMDPForms[0].Product_Name__c+'\n'+'First Name: '+newMDPForms[0].First_Name__c+'\n'+'Last Name: '
+newMDPForms[0].Last_Name__c+'\n'+'Email: '+newMDPForms[0].Email__c+'\n'+'Phone Number: '+newMDPForms[0].Phone_Number__c+'\n'+'Contact Preference: '
+newMDPForms[0].Contact_Preference__c+'\n'+'Practice Name: '+newMDPForms[0].Practice_Name__c+'\n'+'Existing Client: '+newMDPForms[0].Existing_Client__c+'\n'+'Practice ID: '
+newMDPForms[0].Practice_ID__c+'\n'+'Submitting For: '+newMDPForms[0].Submitting_For__c+'\n'+'Submitting For Full Name: '+newMDPForms[0].Submitting_For_Full_Name__c+'\n'+'Submitting For Company Name: '
+newMDPForms[0].Submitting_For_Company_Name__c+'\n'+'Receive athenahealth News: '+newMDPForms[0].Receive_athenahealth_News__c;

tListForOpportunity.add(t);//Adding Tasks in list for Opportunity
}
//Insert Task and Opportunity Services on Opportunity
if(oliList.size() > 0 ){
insert oliList;
}
if(tListForOpportunity.size() > 0){
insert tListForOpportunity;
}
//Insert Partner Locks on Account
if(pcWithMatchProductName.size()>0){
if(pcWithMatchProductName[0].id != null){
insert plList;
}
}

//Insert Lead if not matching accounts
if(lList.size() > 0){
insert lList;
}
for(Lead l:lList){
CampaignMember cm = new CampaignMember();
cm.LeadId = l.id;
if(cmpgn.size()>0){
cm.CampaignId = cmpgn[0].id;
}
cm.Status = 'Responded';
cmpgnmemListForLead.add(cm);

Task t = new Task();
t.WhoId = l.id;
t.OwnerId = '005G000000366nY';//Replace this with '005G000000366nY' while deploying to production
t.Subject = 'MDP Storefront Submission';
t.Task_Type__c = 'Outbound Call';
t.ActivityDate = System.Today();
t.Priority = '3';
t.Status = 'Not Started';
t.Description = 'Form Type: '+newMDPForms[0].Form_Type__c+'\n'+'Product Name: '+newMDPForms[0].Product_Name__c+'\n'+'First Name: '+newMDPForms[0].First_Name__c+'\n'+'Last Name: '
+newMDPForms[0].Last_Name__c+'\n'+'Email: '+newMDPForms[0].Email__c+'\n'+'Phone Number: '+newMDPForms[0].Phone_Number__c+'\n'+'Contact Preference: '
+newMDPForms[0].Contact_Preference__c+'\n'+'Practice Name: '+newMDPForms[0].Practice_Name__c+'\n'+'Existing Client: '+newMDPForms[0].Existing_Client__c+'\n'+'Practice ID: '
+newMDPForms[0].Practice_ID__c+'\n'+'Submitting For: '+newMDPForms[0].Submitting_For__c+'\n'+'Submitting For Full Name: '+newMDPForms[0].Submitting_For_Full_Name__c+'\n'+'Submitting For Company Name: '
+newMDPForms[0].Submitting_For_Company_Name__c+'\n'+'Receive athenahealth News: '+newMDPForms[0].Receive_athenahealth_News__c;

tListForLead.add(t);//Adding Task in list for Lead
}
//Insert Campaign Member on Lead
if(cmpgn.size()>0){
if(cmpgn[0].id != null){
insert cmpgnmemListForLead;
}
}
//Insert Task on Lead
if(tListForLead.size() > 0){
insert tListForLead;
}
}
}

 

Please let me know if anyone has solution for this error.

sfdcfoxsfdcfox
You could add a filter to the query such as "ORDER BY CreatedDate DESC LIMIT 10000", which would automatically create selectivity (because of the Order By indexed field plus a limit). This is covered in the SOQL optimization video on youtube here: https://www.youtube.com/watch?v=mrOzUcycqZM ).