+ Start a Discussion
jd_06jd_06 

Non-Selective Query - Developer Sandbox Only

Hi All - I'm somewhat familiar with the Non-Selective query problem, however, this one has got me stumpped.  My company has a full sandbox and I have my own development sandbox.  The same piece of code in our Full Sandbox and Production is causing a non-selective query in my dev sandbox.  No issue at all in Full SB or Production.

The query is as follows:  Note that this is in an After Update / After Insert Opportunity Trigger helper class.

for(CampaignMember cm: [SELECT id, ContactId FROM CampaignMember WHERE Campaign.Id =: campaignId])
{
    existingCampMembers.add(cm.ContactId);
}

I have one campaign member on one campaign in my dev sandbox.....that's it!!

If I execute query in developer console it returns the record.  If I execute anonymous, it finds the record no problem.  Also, if I were to change the filter to specify a contactid instead of campaign.id I do not get non-selective error.  So clearly there is something going on with Campaign.Id, but why here and not in Full SB or Production?

So does anyone have any ideas as to why I'm getting a non-selective query in my dev sandbox but not full sb or production?

Thank you in advance for any suggestions.

Jason

Below is the complete error message.

Error: Invalid Data.
Review all error messages below to correct your data.
Apex trigger OpportunityTrigger caused an unexpected exception, contact your administrator: OpportunityTrigger: execution of AfterUpdate 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): Class.OpportunityTriggerLogic.addCampMbrToBldrPrgm: line 79, column 1
Eli Flores, SFDC DevEli Flores, SFDC Dev
will it work if you change

WHERE Campaign.Id =: campaignId 
to
WHERE CampaignId =: campaignId//i.e. drop the periord between Campaign and Id
jd_06jd_06
Thank you Eli for response.  I had tried that as well, but had no luck.  Again, what is so curious to me is why is same code behaving differently in Dev Sandbox vs. Full and Production.  Especially considering I only have 1 row of data for this Campaign Member Object.

Again Thank you
Jason
rajcrm86rajcrm86
I think the variable 'campaignId' is null in the trigger context, just debug that variable. Also try these steps,

1) Limit that query results to 2 and see the results in debug log (since u have only one record in your system, it should not return more than one)
or
2) Add one more filter condition 'IsDeleted = false'

--
Raj
jd_06jd_06
Thanks for the response Raj.  I too thought that campaignId may have been null, so I debugged that yesterday, and it was not null.

However, this morning I logged into that devleoper org and was going to try your two suggestions.  And I'm NOT getting the error anymore.  Yesterday I commented out the function call just so I could create opportunities without getting the error.  So this morning I uncommented the function call and tried to create an opportunity and it worked!  I put a debug in that function to see if the query was getting executed, and it was.  I am completely puzzled as to why I would get error yesterday and not today.

One curious note is that when I look at the query in debug log it looks like this

06:45:27.307 (2307666612)|SOQL_EXECUTE_BEGIN|[80]|Aggregations:0|select id, ContactId from CampaignMember

why does it not include the WHERE clause?  I checked in our Full Sandbox and the query in debug log looks the same....no WHERE clause.  This seems a bit odd to me.

But thank you again to Eli and Raj for trying to help me troubleshoot this.  It was very much appreciated!

Jason