9

I have 557,029 Foo objects in my org. I want to count how many of each kind of picklist value there are within these:

Type 1 - 100,000 Type 2 - 60,000 Type 3 - 40,000 Type N - N

I thought I could do this with a @ReadOnly @RemoteAction and Javascript Remoting, as well as the GROUP BY ROLLUP(picklist api name) option.

Code:

@Readonly
    @RemoteAction
    global static Map<String, Map<String, Integer>> loadObjectValues(){

        Map<String, Map<String, Integer>> res = new Map<String, Map<String, Integer>>();
        res.put('Bar__c', new Map<String, Integer>());

        List<AggregateResult> ar = [Select Bar__c, COUNT(Name) total from Foo__c WHERE Bar__c != null GROUP BY ROLLUP(Bar__c)];

        for(AggregateResult a: ar){

            res.get('Bar__c').put((String)a.get('Bar__c'), (Integer)a.get('total'));
        }

        return res;
    }

This gives me 7 Rows, and it explodes with this remoting message:

Visualforce Remoting Exception: Too many query rows: 1000001

Now, I know that the number of query rows for AggregateFunction are the number of records that were "touched" but not necessary returned, but if I only have 557k records, how can it count the same record twice? I'd like to know the exact calculation it's doing (the debug logs tell me nothing) so I can know if this is possible or not.

EDIT:

See Query Plan Tool screenshot below

enter image description here

EDIT 2: Experimenting with other records, it appears that the number of query rows returned is always double what it should be. In other words, it is 557029 * 2 > 1kk. I can't find any mention of that in the docs, maybe a bug?

3

Surprising.

It would be worth looking at the query in the Developer Console Query Plan Tool. (That article includes several links about how Salesforce queries are optimised.) In case it made a difference, you could also ask Salesforce support to add an index on Bar__c.

2
  • Thanks for the tip, I didn't know about that. See screenshot above from when I ran the query. No mention of multiple queries or the same record being counted twice.
    – George S.
    Oct 13 '14 at 12:18
  • 1
    @GeorgeS. Yeah the information presented is pretty limited. Adding the index might kick it out of using "TableScan" and make a difference but definitely "might". A colleague got some good help from salesforce support on a query problem so you could create a case and see if anything useful comes of that.
    – Keith C
    Oct 13 '14 at 13:13

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.