Export large list of Submissions (40k+)

[adrianjean]

Question

I'm trying to export my Formie submissions so that I can review the data in Excel. I have over 40k submissions and when I go to export it never completes, and times out. It also locks up the website while the export runs.

Is there a way to run it through the queue manager in chunks? Or a different way to export that's faster?

Additional context

Craft Pro 5.5.10
MySQL 8.0.30
PHP 8.3.6
Formie: 3.0.17

[engram-design]

Not at the moment, sorry. We rely on Craft's export behaviour, and that doesn't have such a feature. It'd be a tricky one to implement, and would need to have some sort of alert mechanism to let the user know their export is ready. Not impossible of course though, but there's some considerations to make when it comes to resources.

[adrianjean]

One thing I noticed is that the export Type is "Default" — but on normal entry exports there are options for Raw data and Expanded. Could it be the export format is adding to the slowness?

I'm going to post an issue on CraftCMS as well and reference this issue here.

[adrianjean]

For record — here is the cross posted issue in CraftCMS: craftcms/cms#16520

[engram-design]

That'll be because we override the default exports with our own.

Of course, we can implement a solution just for Formie hooked up with the queue to generate an export in the background, and that queue job sends off an email with a link to that exported (temp) file, and you download that. But I honestly think that'd be a neat core feature in Craft.

As for why it's taking so long, it's a little odd, but not surprised. Elements are pretty generally slow to deal with on a large scale no matter what you're doing.

We'll see how Craft respond to that feature request.

[adrianjean]

Just to update — I was using a hosted MySQL DB and suspected it might be slowing things down because the server CPU load was minimal during the export attempts.

So I tried it with a local MySQL server and the same DB, and it actually completed. It still did take a long while to export 40k+ submissions, but at least it completed. So seems the hosted DB has a massive impact on exporting this data.

[engram-design]

Interesting to know, thanks for the update!