Repository navigation
Generating several N+1 queries when adding multiple resources to an export thus slowing down query and timing out Heroku #40
Description
When I am trying to export a resource which has two or three other related models data to be exported along with it, it is generating several N+1 queries and with couple of thousand rows I am facing server time out issues.
Upon installing bullet gem I get following on my bullet.log
USE eager loading detected
Resource => [:sponsorships]
Add to your query: .includes([:sponsorships])
GET /admin/resources/export.csv
USE eager loading detected
Resource => [:organizations]
Add to your query: .includes([:organizations])
Just for example my resource_dashboard contains following:
```ATTRIBUTE_TYPES = {
id: Field::Number,
name: Field::String,
resource_type: Field::Enumerate,
organizations: Field::HasMany.with_options(
transform_on_export: -> (field) { field.data&.map(&:name)&.join(", ") if field.data }
),
event_detail: Field::HasOne,
questions: Field::NestedHasMany.with_options(
skip: :resource,
transform_on_export: -> (field) { field.data&.map(&:text)&.join(", ") if field.data }
),
Is there a way to speed up query or prevent eager loading?
Activity
andreibondarev commentedon Jun 6, 2021
@subratrout A couple of thoughts-- it looks like within the
transform_on_exportyou may beselect-ing all fields from associations. Replacingfield.data&.map(&:name)withfield.data&.pluck(:name)might pull a smaller ActiveRecord object.If you're on Heroku, how many records are you attempting to export and how much RAM do you have configured on the dyno?
subratrout commentedon Jun 7, 2021
The table contains only 2100 rows. I am able to export if exporting from single table, however when I join one or more tables that's when it times out. The app using two standard-2x-dyno with 1GB Memory each.
MichalJaneda commentedon Oct 5, 2022
You may preload them manualy by overriding controller method.
Here is my example:
DavidGeismarLtd commentedon Dec 1, 2022
Is there any way to configure the gem to run the export method in a sidekiq job ?
andreibondarev commentedon Dec 8, 2022
@DavidGeismarLtd Not currently. It's rather complex. What issue are you having? Server timing out?
mattcarey commentedon Nov 18, 2024
We are getting a
H12error on Heroku when exporting to CSV using this gem. Our export isn't big but has a few pieces of data from other tables. Any idea of a solution please?andreibondarev commentedon Nov 18, 2024
If you copied your production DB to local -- are you able to successfully generate the export?
andreibondarev commentedon Oct 21, 2026
This is addressed in #59 and will close automatically when it's merged.
Fix open issues: empty exports, N+1 queries, Turbolinks, plural routes (