1

I am using SOSL query in a method in Apex class.This method is getting called from trigger,when a custom object DCR is created.

SOSL Query:

List<List<Account>> acclist = [FIND :newstring IN NAME FIELDS RETURNING Account(Id,FirstName,LastName,Territory_vod__c where RecordType.Name='Prescriber' ) ];

DCR is having lookup with Account.newstring is having string value.I am using SOSL (instead of SOQL) due to requirement.I cannot use any other indexed field in WHERE condition due to requirement constraint.

I am getting this error while creating DCR record:

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):

What could be the workarounds for this exception?

  • NO,this didn't help because I cannot use any other indexed field in WHERE condition due to requirement constraint. – devsfdc Mar 7 '17 at 5:02
  • If requirements are in direct conflict with governor limits the limits will always win. If the requirements cannot be changed then you are in a pickle. You can use the information gained to take back and figure out a new set of requirements. I see by your answer you found a way to meet the requirements (with sharing) which was actually the one of the correct things to do. But keep in mind the issue could still occur, especially when an admin attempts to do the same thing and can see all records – Eric Mar 7 '17 at 5:05
2

It appears your Account object has more than 200,000 records and the WHERE clause is not providing appropriate filtering.

According to the Salesforce standards & best practices - the where clause needs to subset 10% or less of the data.

You have three possible solutions, as per the article linked below:

  1. You may find that the query in question needs to be more selective in the WHERE clause. According to the Salesforce standards & best practices - the where clause needs to subset 10% or less of the data.
  2. A custom index on the field.
  3. A possible quick fix may be to make the field in question an external ID. Since external IDs are indexed automatically, this will create the index and may solve the problem.

    • Go to Setup.
    • Under "Build", click Customize then click the object name.
    • Click Fields.
    • Then, edit field name and check the box External ID.

This article explains the problem and possible solutions.

Edit

If you must search through a huge amount of data then you should consider using OFFSET in your SOSL queries.

When expecting many records in a query’s results, you can display the results in multiple pages by using the OFFSET clause in a SOSL query.

You code would then look something like

List<List<Account>> acclist = [FIND :newstring IN NAME 
                               FIELDS RETURNING Account(Id,FirstName,LastName,Territory_vod__c WHERE RecordType.Name='Prescriber' OFFSET 200) 
                              ];

Keep in mind that the maximum OFFSET limit is 2000.

  • Actually total number of account records are 256414 and if I use recordtype.name ='Prescriber' in where clause,then there will be 62186 records which is less than 10%. – devsfdc Mar 6 '17 at 10:29
  • 25,641 is the 10% of 256,414. I have updated my answer with a workaround. – Mahmood Mar 6 '17 at 10:38
  • Using OFFSET is also not working.OFFSET 2000 will only skip 2000 records.But I have 25,641 records.And also desired record can be anywhere and it could be skipped. – devsfdc Mar 6 '17 at 11:08
  • OFFSET lets you display records on multiple pages. OFFSET is not skipping anything. What error are you getting? – Mahmood Mar 6 '17 at 11:15
  • I am not using any VF Pages and don't have to display records. – devsfdc Mar 7 '17 at 4:55
0

Error I was getting was because there were too many account records.This was solved by using with Sharing in the class.

  • 1
    keep in mind the issue could still occur, especially when an admin attempts to do the same thing and can see all records – Eric Mar 7 '17 at 5:09

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.