0

I want to process over one million records synchronously in apex controller and want to show records SUM, COUNT, MAX, AVG etc on visualforce page as per the requirement. e.g. suppose a custom object has more than one million records and i want to show the sum of particular field having integer type, but i am receiving error : Too many query rows : 1000001

I have to show this count on visualforce page on button click event.

Please suggest any suitable solution for this. Your timely help would be highly appreciated.

Thanks Sachin

Improve this question
5
  • This is a tough one. You can't even use roll up summary fields (if you were able to define a relationship) since best practice states no more than 10000 child records for a given master record. Are you able to incrementally calculate these values in triggers for the objects for which you are wanting to show these values or to use a batch to calculate and store these values for presentation? The latter means async so the figures would not always be up-to-date but is that really important in your use case?
    – Phil W
    Commented Aug 13, 2019 at 8:42
  • Yes, my requirement is to calculate and to show the data on VF page synchronously, as client requirement is to select the object name and show its count of records as per record type, sum of currency field data, show Max and Min data value of Date field etc. All this must happen on object selection from dropdown list.
    – Sachin Singhal
    Commented Aug 13, 2019 at 9:48
  • The question is, can this value be calculated asynchronously and you show the date/time at which it was last updated as part of the presentation? Fundamentally, from my perspective and due to Salesforce restrictions, you either have to incrementally calculate these values during creation, updates and deletion of the relevant objects, holding the aggregate values in a special object from which you display the values, or you need to present details that are asynchronously calculated - and in this case you'd want to show when the calculation was last performed too.
    – Phil W
    Commented Aug 13, 2019 at 10:05
  • yes, my requirement is to calculate the values synchronously as records are being added, modified or deleted continuously as per client business. Client also wants to export the data in excel/pdf files. so they need latest data.
    – Sachin Singhal
    Commented Aug 13, 2019 at 10:44
  • OK, so you need to go the route of data that is computed and stored when changes happen to the source records. Note that this can be quite difficult to get right and you need to make sure each update to the computed "aggregates" is performed in a thread-safe manner (you will want to use FOR UPDATE to lock the record where you store these aggregates). It sounds like you will need different "aggregate" instances per record type given your comment above. Good luck!
    – Phil W
    Commented Aug 13, 2019 at 11:03

1 Answer 1

Reset to default
2

Salesforce has specific limits on aggregate queries, as you are encountering. There seem to be a few different options you could consider:

  1. Try to use roll up summaries; however, this requires a master/detail relationship tha you probably don't have. Also this won't work given that "best practice" states that a given parent should only have up to 10000 children
  2. Have a batch job that asynchronously computes and stores "aggregate results" in some custom object instance(s) and present these values along with the time the results were last computed. You have stated that you need the results synchronously, so this isn't going to fly
  3. Ensure that you have triggers on create, update, delete, undelete etc. on your source objects and have these incrementally update "aggregate results" held in some custom object instance(s) so these can be presented in your UI.

It appears, from what you've said, you probably need to take option 3.

Note that you will probably need to write a batch to calculate the initial "aggregate results" values to be stored so that later changes can then correctly update these values, in an incremental manner.

Improve this answer

You must log in to answer this question.

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