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?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
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.
    Commented Oct 13, 2014 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
    Commented Oct 13, 2014 at 13:13

You must log in to answer this question.

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