0

I understand this exception. But what I don't understand is this:

THIS APPROACH WORKS:

public List<String> optionType {
            get {
                if (optionType == null) { 
                    optionType = new List<String>();              
                    for( AggregateResult ar : [SELECT Option_Type__c FROM Custom_Object__c WHERE Option_Type__c != '' GROUP BY Option_Type__c]){ 
                        optionType.add(String.valueOf(ar.get('Option_Type__c'))); 
                    } 
                }
                optionType.sort();
                return optionType;          
            }
            set;
    }

THIS APPROACH THROWS LIMIT EXCEPTION:

public List<String> optionType {
        get {
            if (optionType == null) { 
                optionType = Global_Utils.getAllPossibleValues('Custom_Object__c','Option_Type__c',true,true);  
            }
            return optionType;          
        }
        set;
    }

and all I am doing in public static List<String> Global_Utils.getAllPossibleValues(String object_name, String field_name, Boolean sorted, Boolean remove_blank_values) is returning a List with the same query as in the first approach.

EDIT: IF you are interested in seeing what is going on in Global_Utils.getAllPossibleValues: (the field type is a 'String' and it is 'Groupable')

public static List<String> getAllPossibleValues(String object_name, String field_name, Boolean sorted, Boolean remove_blank_values){

    // This method currently supports picklists and string type fields only. Please add logic for other types as they come. 
    List<String> allPossibleValues;

    try{
        String field_type = getFieldType(object_name, field_name);
        Schema.SObjectType ObjectSchema = Schema.getGlobalDescribe().get(object_name);
        Map<String, Schema.SObjectField> fieldMap = ObjectSchema.getDescribe().fields.getMap();
        Schema.DescribeFieldResult field = fieldMap.get(field_name).getDescribe();

        if(field_type.equals('picklist')){
            allPossibleValues = new List<String>();

            for (Schema.PicklistEntry f : field.getPicklistValues()){
                allPossibleValues.add(f.getLabel());
            }
        }

        if(field_type.equals('string')){
            allPossibleValues = new List<String>();

            String soql = 'SELECT ' + field_name + ' FROM ' + object_name;
            if(remove_blank_values)
                soql = soql + ' WHERE ' + field_name + ' != \'\' ';

            if(field.Groupable){
                soql = soql + ' GROUP BY ' + field_name;
                List<AggregateResult> results = new List<AggregateResult>();
                results = Database.query(soql);

                for( AggregateResult ar : results){     
                    allPossibleValues.add(String.valueOf(ar.get(field_name)));
                } 
            }
            else{
                List<sObject> results = new List<sObject>();

                results = Database.query(soql);
                Set<String> setOfAllPossibleValues = new Set<String>();

                for( sObject obj : results){ 
                    setOfAllPossibleValues.add(String.valueOf(obj.get(field_name))); 
                } 
                allPossibleValues.addAll(setOfAllPossibleValues);
            }
        }

        if(sorted)
            allPossibleValues.sort();

        return allPossibleValues;
    }
    catch(Exception e){
        return null;
    }    
}

EDIT: And, the Global_Utils class is defined like so: public with sharing class Global_Utils{...}

Why does the second approach fail when the first one works just fine?

EDIT 2: I figured the cause. Can someone please explain why? and perhaps suggest a workaround?

String soql = 'SELECT State__c FROM Custom_Object__c WHERE State__c != \'\' GROUP BY State__c';
List<AggregateResult> results = new List<AggregateResult>();
results = Database.query(soql);

LIMIT_USAGE_FOR_NS Number of query rows: 34095 out of 50000 ******* CLOSE TO LIMIT

Set<String> stateSet = new Set<String>();
for( AggregateResult ar : [SELECT State__c FROM Custom_Object__c WHERE State__c != '' GROUP BY State__c]){     
    stateSet.add(String.valueOf(ar.get('State__c')));
}

LIMIT_USAGE_FOR_NS Number of query rows: 114 out of 50000

Why is Database.query so bad as opposed to [... my_soql_here ...]?

1

2 Answers 2

2

Global_Utils is most likely a "without sharing" class, while the other is most likely "default" mode, or possibly "with sharing." Your custom object most likely uses a Private sharing model. You're most likely testing each with a non-administrator account.

Moving the function from one to the other causes the query to run in a different sharing context, returning far more records, since that user is then technically a View All Data user.

Remove the without sharing keywords from your utility class. Dynamic and static queries use the same engine, which is also the same one used in, for example, the API. The only difference is the preprocessing that occurs in static queries: it's allowed to use bound Apex Code instead of just simple variables.

0

Most likely because Force.com will optimize the query given the filter criteria and getAllPossibleValues is not optimized in the same manner.

Unless you know for a fact that "getAllPossibleValues" uses the same exact query (which I doubt it does given the inputs and the fact that it doesn't filter out empty strings), the data collection can and will be vastly different.

Check this out: https://help.salesforce.com/help/pdfs/en/salesforce_query_search_optimization_developer_cheatsheet.pdf

4
  • Just added details about getAllPossibleValues method in OP. Apr 22, 2015 at 15:39
  • I wonder if the schema dips at the top of your try are counting towards the record counts? I can't imagine you have a significant amount of objects and fields. I assume your field is Groupable, but I don't think that would affect the number of rows returned anyways. It would probably be worth using Limit.getLimitDMLRows() in a System.Debug output and the Limits.getDMLRows() to see what is generating additional rows since your filtering seems to be the same on the query. How many rows does that custom object have?
    – JeffyB
    Apr 22, 2015 at 16:44
  • I figured what is causing this. Please see EDIT 2 in OP. Thanks. Apr 22, 2015 at 17:04
  • Inline or Static SOQL queries are technically different from Dynamic SOQL queries which are executed through Database.query(). Supposedly (according to Salesforce) these are treated the same, but I wouldn't be surprised if there was additional overhead involved in using Database.query() before it gets to the Force.com SOQL optimizer.
    – JeffyB
    Apr 22, 2015 at 18:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .