0

I have a custom report where depending on the date range it will return way over 50001 aggregated rows. Through my research all I am finding is suggestions to limit the query but that won't work for a report. With Visualforce I read there is a @readonly attribute that will allow you to return 1 million rows and I am assuming this is not available with lightning. I am just hoping someone has found a way with Lightning to overcome this because I am currently at a loss.

  • Is this in a native report or in programmatic functionality? Your tags suggest the latter, but it's not clear. – David Reed Apr 11 '18 at 16:58
  • Hi David sorry my post was not clear, it's not a native report. What I am doing in my apex controller is returning an aggregate result list depending on a date range the user chooses. I use this aggregate result in a lightning component to display the rows using pivot.js. – DerrickE Apr 11 '18 at 17:06
  • 1
    It seems we already have an idea for this success.salesforce.com/ideaView?id=0873A000000cNPMQA2 – the_phantom Apr 11 '18 at 17:20
  • 1
    What kind of date ranges are you working with? A common approach is to roll up the data at a higher level (e.g. monthly), and then your data volume is dramatically reduced. This approach requires some schema changes and custom code, but otherwise is fairly easy to implement. – Adrian Larson Apr 11 '18 at 17:55
1

There's another route to process more than 50,000 records whether or not you're in a Visualforce page with read-only mode enabled: a batch job.

Your Apex controller can spawn a batch that implements Database.Stateful and performs a large-scale query while aggregating results into its instance variables. You can return the Id of the batch job via your Apex controller to your Lightning component, which can use a controller action to poll the status of the job (by querying the AsyncApexJob Sobject).

Once the batch job completes its work, it can write its results into a custom object; your Lightning component can then grab those results (or, optionally, cached results from a recent run) and render the data.

The chief shortcoming of this solution is that it is not very fast. Since you're allowing the user to choose a date range, you may benefit less from caching the results of future runs. You might consider addressing this by providing specific ranges for the user to select from, rather than allowing free input, and caching or even pre-calculating (with Scheduled Apex) results for those date ranges.

If the performance of the batch solution is not acceptable, another (very data-model-dependent) approach would be to apply chunking of your query on some indexed field. If you can divide your query into chunks of <50,000 records based upon some facet of your data model (whether or not that facet has anything to do with the report itself!), you can manually aggregate a series of smaller aggregate queries on the controller side and get the end result of an aggregate report > 50,000 rows.

|improve this answer|||||
  • Thanks for your suggestions David. I think I have enough info to come up with a solution. At least until @readonly will be allowed on a lighting page to return 1 million records. – DerrickE Apr 11 '18 at 18:10

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.