Issue

The forms with more entries fails on data export to csv

• Form export fails with no message.
• Export too slow if it works.
  In my case I had a form that had close to 25k records and the export was not working or neither showing any errors. I found the API call failed for export after few mins with a server error. Check the network tab in the browser.

Workaround

Quick and easy way to avoid exporting all entries. I was able to export the data in smaller date interval. I started by month it worked mostly, if that failed then went with two weeks and eventually combine all the files. Its time consuming but this fix is easy to achieve in a timely manner. It took a lot of time each export took close to 5 minutes and overall task took close to 30 minutes to get the data.

Solution

All the forms data is stored in the FormEntry table. Every time we export it internally, a sql query is used to filter and pull all the data specific to the form. The best way to optimize this is to add Index to the field used for querying.
We will create three indexes. Formitemid and created in the formentry table and formentryid in the fielddata table.

Before

Back up your “ExperienceForms” database
Execute the following query in “ExperienceForms” database

After

The execution time for the same export with 25K+ records was less than 2 seconds after creating the indexes.