3

I have a field that stores an ID, and I need to find the Max value of that field. However I get an error a queryexception. For reference, the field is set up as unique and external Id.

Any ideas on how to get this?

Apex trigger AccountTrigger caused an unexpected exception, contact your administrator: AccountTrigger: 
execution of BeforeInsert 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.AccountHelper.checkprodSeed: line 35, column 1
 * = Required InformationAccount Information

Code

public class AccountHelper {
    Integer prodSeed,custSeed,prodIDSeed;
    Integer updateSeeds;
    Integer custMaxLength = 12;
    Integer prodMaxLength = 12;
    Integer prodIdMaxLength = 12;

    public AccountHelper () {
        this.updateSeeds = 0;
    }

    /*error code for seeds:
 * 1: every thing ok
 * 2: seed < highest MV #
 * 3: can't tell what highest MV #
 */

    public integer checkMVSeeds() {
        Variables__c mv  = Variables__c.getorgdefaults();
        this.prodSeed = (Integer) mv.MV_Record_Key_Prod_Seed__c;
        this.custSeed = (Integer) mv.MV_Record_Key_Cust_Seed__c;
        this.prodIDSeed = (Integer) mv.MV_Producer_ID_Seed__c;
        checkprodSeed();
        if (this.updateSeeds == 2) {
            mv.MV_Record_Key_Prod_Seed__c = prodSeed;   
            mv.MV_Record_Key_Cust_Seed__c = custSeed;
            mv.MV_Producer_ID_Seed__c     = prodIDSeed;
            upsert mv;
        }
        return this.updateSeeds;
    }

    public void checkprodSeed() {
        for (List < Account > accts: [select id, prod_id__c from account where name != 'Direct Sale' and prod_id__c != null and prod_id__c != ' ' order by prod_id__c desc limit 1]) {
            //build out string..
            for (account a: accts) {
                String s = a.prod_id__c;
                s = s.substring(2, prodIdMaxLength);
                integer num;
                try {
                    num = integer.valueOf(s);
                    if (num != prodSeed) {
                        //update prodseed to the next max
                        prodseed         = num;
                        this.updateSeeds = 2;
                        OppUtils.emailAdmins('prod_id__c number issue', a.id + ' had a prod id out of sequence:  ' + a.prod_id__c + 'and the seed was: ' + prodIDSeed + '. The seed was updated to the producer Id of the account');
                    } else {
                        this.updateSeeds = 1;
                    }

                } catch (exception e) {
                    OppUtils.emailAdmins('prod_id__c number issue', a.id + ' could not be mapped. its prod_id__c is: ' + a.prod_id__c + 'and the seed is: ' + prodIDSeed);
                    this.updateSeeds = 3;
                }
            }
        }
    }

}

References: https://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm

5
  • Where clauses based on != cause all the rows to be scanned. You need to figure out a way to select a small percentage of the rows using an indexed field to avoid the exception. As well as that, you can save CPU cycles by using an aggregate query rather than looping.
    – Keith C
    Aug 26 '14 at 18:52
  • any tips? the goal is to just find the max value of the field , but it's across all accounts Aug 26 '14 at 19:30
  • Well you could write a Batchable that runs through all the Accounts (no where query so no exception) but that seems pretty wasteful. Or make the logic that sets the prod_id__c bullet proof. But hopefully someone else has a better approach.
    – Keith C
    Aug 26 '14 at 19:59
  • I worry about user override. I'm automating a previously manual process that needs occasional manual override Aug 26 '14 at 20:08
  • If it is completely necessary you could write a workflow rule to set the value of a field to YES or NO and have that field indexed. Then use that field as the query criteria. You would set it to yes based on your criteria in the query string. Your problem is that you are using != which requires a scan of the entire database. You could do an aggregate rollup as well but would most likely hit the too many query rows in your situation of scanning all accounts. (potentially)
    – Eric
    Feb 7 '15 at 20:47
2

You need to add a filter according to an Index on the record. A lot of lookups are indexed and some date fields I believe. I can't remember off the top of my head though what is indexed. Regardless, it comes down to you need to add in some more concrete filters.

EDIT: Also, having a field where you are using = instead of != might help a lot too.

In addition to the above actual fix, you are adding in some unnecessary loops.

for (List < Account > accts: [select id, prod_id__c from account where name != 'Direct Sale' and prod_id__c != null and prod_id__c != ' ' order by prod_id__c desc limit 1]) {
  //build out string..
  for (account a: accts) {
    String s = a.prod_id__c;

Should be:

for (Account a: [select id, prod_id__c from account where name != 'Direct Sale' and prod_id__c != null and prod_id__c != ' ' order by prod_id__c desc limit 1]) {
  //build out string..
  String s = a.prod_id___c;
2
  • I thought the field was indexed since it requires a unique value and that it's an external ID, is that not the case? Aug 26 '14 at 18:48
  • @nivyaj Well, you are using != which isn't enough of a filter for the system I believe. I think you need to actually use =.
    – dphil
    Aug 26 '14 at 18:55

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.