4

I have a Set called set_pvCorporativo with about 600 values. I want to bring exactly 600 accounts that have the pv_Corporativo value in set_pvCorporativo AND the Status 'Ativo' in a query like:

List<Account> list_Accounts = [SELECT Id, PV_Corporativo__c 
                            FROM Account 
                            WHERE Status__c = 'Ativo' 
                            AND PV_Corporativo__c IN :list_pvCorporativo];

But when I execute the query, I take the following error:

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 can't make this field unique, because there is more than one Account with the same PV_Corporativo, but I used the Status Ativo to filter the result. So with the status on the query, I have only one account for each value on the set. Can some one help me with this error?

2
  • do you want only 600 Account? – Manjot Singh Apr 26 '18 at 18:58
  • @ManjotSingh In this case I have 600 Pv_Corporativo and it should return to me the same account numbers, – Bruno Perdigão Apr 27 '18 at 12:20
4

I was going to write a long answer and then remembered this excellent article.

To simplify your work, I'll copy a a relevant paragraph from there, but you should read the entire thing

For best performance, SOQL queries must be selective, particularly for queries inside triggers. To avoid long execution times, the system can terminate nonselective SOQL queries. Developers receive an error message when a non-selective query in a trigger executes against an object that contains more than 200,000 records. To avoid this error, ensure that the query is selective.

Selective SOQL Query Criteria

A query is selective when one of the query filters is on an indexed field and the query filter reduces the resulting number of rows below a system-defined threshold. The performance of the SOQL query improves when two or more filters used in the WHERE clause meet the mentioned conditions.

The selectivity threshold is 10% of the first million records and less than 5% of the records after the first million records, up to a maximum of 333,333 records. In some circumstances, for example with a query filter that is an indexed standard field, the threshold can be higher. Also, the selectivity threshold is subject to change.

You can ask SFDC to make the field PV_Corporativo__c a custom index. That will definitely help with the selectivity (assuming you're returning # of records under the threshold). Alternatively, try adding WHERE clauses that bring the number of records down under the selectivity threshold (though that depends on whether you can change your requirements to suit the query issues).

Other resource: here.

3
  • 1
    Sebastian, thank you very much for explantion. Now it's clear to me. I'll try to solve the problem and than I give a feedback. – Bruno Perdigão Apr 27 '18 at 12:23
  • 1
    Sebastian, just for your feedback, I found a way to use RecordTypeId on query (indexed field) and evertything works fine. Thank you! – Bruno Perdigão May 4 '18 at 12:32
  • RecordTypeId would do it too. Almost any indexed field will help you there – Sebastian Kessel May 4 '18 at 18:16
2

Sebastian kessel answer explains most of part, one thing that I want to add is using limit clause in query. E.g:- you want only limited number of accounts depending upon count of Pv_Corporativo. So you can add limit clause in your query to improve performance of soql query.

Q: We include an "ORDER BY Name" statement in an important SOQL statement that has performance issues at times. We could probably get away with not doing this. Is this practice good or bad or does it not impact much? A: ORDER BY without LIMIT clause is not going to be optimizable. However, ORDER BY + LIMIT on an indexed field (standard or custom) can actually be a very useful optimization (i.e. a page displaying the first 10 rows of a potentially large, 1000+, list of records).

Link:- https://help.salesforce.com/articleView?id=000181277&type=1&language=en_US

1
  • 1
    In my opinion the problem is the account registers amount in database, not exactly the amount returned from query. Like I said before, the query should return me something like 600 registers. Recently in a call with cliente, I found a new way to do this query, I added a indexed field: RecordTypeId. I think it will solve the problem. I'm just waiting the client to test it, anyway thank you very much. I'll give you all a feedback later. – Bruno Perdigão Apr 27 '18 at 21:46

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.