Answers.Body: <p>As you suspected, Sprout Reports is not designed to handle large quantities of data if it's more than your server can handle. As for how many records you could export, it really just depends on how impressive your query is and how much your server can handle.</p>
  
  <p>We could potentially consider adding support for batching large exports. I've created a ticket if you'd like to add your vote for it: <a href="https://github.com/barrelstrength/craft-sprout-reports/issues/39" rel="nofollow noreferrer">Add support for batch exporting large data sets</a></p>
  
  <p>For now, if you plan to do this on a regular basis you may want to invest in writing a script that makes this a bit easier to do or reach out and contract us to prioritize adding support for this feature.</p>
  
  <p>Within the scope of optimizing the existing queries in your Twig template reports, you could potentially use techniques like:</p>
  
  <ul>
  <li><a href="https://docs.craftcms.com/v3/dev/eager-loading-elements.html" rel="nofollow noreferrer">Eager Loading</a>.</li>
  <li>Saving related data ahead of time using a field like the <a href="https://github.com/aelvan/Preparse-Field-Craft" rel="nofollow noreferrer">Preparse Field</a> which could potentially reduce the need for complex joins when performing the actual query. </li>
  <li>Advanced Twig query techniques (I don't know where these are documented but Brandon mentioned them in his slides <a href="https://speakerdeck.com/brandonkelly/intro-to-craft-3?slide=46" rel="nofollow noreferrer">here</a>).</li>
  <li>Create custom variables or twig functions in your own custom plugins to help optimize how the queries are actually being handled.</li>
  </ul>
  
  <p>Outside of optimizing the query, you could add date range (or some other) settings that lets you give the user control over reducing the size of the full query. There is an example of creating a <a href="https://github.com/barrelstrength/craft-sprout-reports/blob/v1/src/examples/tutorialreport/settings.html#L149" rel="nofollow noreferrer">date range field</a> in the example templates provided in the plugin.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-12-18T09:29:01.653Z
Answers.Text: Thanks Ben. I implemented a lot of what you said and can already see improvements. Can you elaborate on the 4th point? or point me in the direction of the docs? i may already be doing this but just want to be sure.
Answers.UserDisplayName: BillyMedia
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-12-22T15:34:01.587Z
Answers.Text: I had a similar issue with SproutReports where I was trying to output about 3K entries with 30+ fields in each record. I tried PHP tweaks (memory, max_execution, etc.), but the real solution here is Eager Loading. I went from about 30 seconds to execute the report to <10 seconds after eager loading. Just make sure you fully understand which fields can be eager loaded -- stick to assets and matrix fields. If you try to eager load a field which cannot be, you'll end up with no output.
Answers.UserDisplayName: Vaughn D. Taylor
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-01-02T20:17:47.460Z
Answers.Text: @BillyMedia The fourth point is just referring to creating a custom plugin or module (https://docs.craftcms.com/v3/extend/#modules-vs-plugins). Often, the default queries Craft uses for complex stuff are quite abstract, which is expected because Craft needs to make it easy for users to address lots of use cases. However, when it comes to things that need optimization there is often a lot of opportunity in doing a specific thing much more directly than Craft would do it by default. In your templates, custom variables and Twig filters can be useful toward helping create more optimized queries.
Answers.UserDisplayName: Ben Parizek
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-12-17T20:33:35.680Z
Answers.LastActivityDate: 2018-12-17T20:33:35.680Z
Answers.UserDisplayName: Ben Parizek
Body: <p>I have sprout reports (twig template) exporting tens of thousands of records. Problem is that its timing out. I appreciate that this is expected and my only likely option is to up the server spec.
  Is there anything i can do to optimise the report itself? e.g, through tasks where it exports the report over a period of time rather than all in one go?</p>
  
Tags: <plugin-sproutforms><performance>
Title: Sprout reports timing out on thousands of records
ThreadText: # Sprout reports timing out on thousands of records
  
  (Asked by: username_0 on 2018-12-17)
  
  I have sprout reports (twig template) exporting tens of thousands of records. Problem is that its timing out. I appreciate that this is expected and my only likely option is to up the server spec.
  Is there anything i can do to optimise the report itself? e.g, through tasks where it exports the report over a period of time rather than all in one go?
  
  **Tags:** plugin-sproutforms, performance
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2018-12-17. Score: 3 | Accepted answer
  
  As you suspected, Sprout Reports is not designed to handle large quantities of data if it's more than your server can handle. As for how many records you could export, it really just depends on how impressive your query is and how much your server can handle.
  
  We could potentially consider adding support for batching large exports. I've created a ticket if you'd like to add your vote for it: [Add support for batch exporting large data sets](https://github.com/barrelstrength/craft-sprout-reports/issues/39)
  
  For now, if you plan to do this on a regular basis you may want to invest in writing a script that makes this a bit easier to do or reach out and contract us to prioritize adding support for this feature.
  
  Within the scope of optimizing the existing queries in your Twig template reports, you could potentially use techniques like:
  
  * [Eager Loading](https://docs.craftcms.com/v3/dev/eager-loading-elements.html).
  * Saving related data ahead of time using a field like the [Preparse Field](https://github.com/aelvan/Preparse-Field-Craft) which could potentially reduce the need for complex joins when performing the actual query.
  * Advanced Twig query techniques (I don't know where these are documented but Brandon mentioned them in his slides [here](https://speakerdeck.com/brandonkelly/intro-to-craft-3?slide=46)).
  * Create custom variables or twig functions in your own custom plugins to help optimize how the queries are actually being handled.
  
  Outside of optimizing the query, you could add date range (or some other) settings that lets you give the user control over reducing the size of the full query. There is an example of creating a [date range field](https://github.com/barrelstrength/craft-sprout-reports/blob/v1/src/examples/tutorialreport/settings.html#L149) in the example templates provided in the plugin.
  
  > **Comments:**
  > **username_0**: Thanks Ben. I implemented a lot of what you said and can already see improvements. Can you elaborate on the 4th point? or point me in the direction of the docs? i may already be doing this but just want to be sure.  
  > **username_2**: I had a similar issue with SproutReports where I was trying to output about 3K entries with 30+ fields in each record. I tried PHP tweaks (memory, max_execution, etc.), but the real solution here is Eager Loading. I went from about 30 seconds to execute the report to <10 seconds after eager loading. Just make sure you fully understand which fields can be eager loaded -- stick to assets and matrix fields. If you try to eager load a field which cannot be, you'll end up with no output.  
  > **username_1**: @username_0 The fourth point is just referring to creating a custom plugin or module (https://docs.craftcms.com/v3/extend/#modules-vs-plugins). Often, the default queries Craft uses for complex stuff are quite abstract, which is expected because Craft needs to make it easy for users to address lots of use cases. However, when it comes to things that need optimization there is often a lot of opportunity in doing a specific thing much more directly than Craft would do it by default. In your templates, custom variables and Twig filters can be useful toward helping create more optimized queries.  
  
  ---
  
  