Hi Guys,

On my site most of the data is getting submitted through webform submissions, which is now causing performance hit for the site. some details for the submitted data through webforms is written below :

  1. webform_submission = 7,00,000 records
  2. webform_submitted_data = 60,00,000 records

And also i need the reports for these submissions time to time [for current year more frequently].

Due to this data SELECT & INSERT queries to the webform submission tables has become expensive and my site is now responding very slow and some time it do not responds whenever a user try to download the reports for these submissions OR creates a webform submission and it is getting worst as the data is increasing. The key thing for my case is i do not download the reports [more frequently] for the submissions submitted before a particular time (Say 1 year).

Now what i am planning is to create a generic solution for this as this might be the problem with the other users, The approach will be

1. Creating an interface which will provide an option for archiving the data based on date and the archived data will be moved from webform tables to some archive tables (I can not remove them as they are important to me) and it will reduce the number of records in the webform tables and this will make Query execution faster.

2. Creating another interface from where user can select time for which he wants to download the reports from archive and user can continue downloading the reports for the current year from the webform tables.

For E.g. :

1. I have the submission for last 5 years say (2010 - 2014).
2. I frequently download the submissions for current year only.

What i will do now with this interface :

3. Archive all submissions for last 4 years. (2010 - 2013) from interface (#1)
4. If i need to download the report for year 2012, I will go to interface (#2) and will download the report.
5. If i want to download the report for current submissions, the i will go to webform reports download interface and then i will download the reports.

This will reduce the overhead from webforms tables, and will improve the performance.

Has anyone faced this problem earlier, has there any other way by which i can achieve this without losing my submission. This thing is really blowing my head, and i have already started scratching my head. Please suggest something.

Thanks.

Comments

gaurav.goyal’s picture

gaurav.goyal CreditAttribution: gaurav.goyal commented
Issue summary: View changes
gaurav.goyal’s picture

gaurav.goyal CreditAttribution: gaurav.goyal commented
Issue summary: View changes
DanChadwick’s picture

DanChadwick CreditAttribution: DanChadwick commented
Priority: Major » Normal

Issue queue guidelines:

Support requests should never be marked "critical" or "major".

You might consider creating drush commands for this archiving, as it is likely to take a long time. I would also create your module for 7.x-4.x, as -3.x won't see further development.

DanChadwick’s picture

DanChadwick CreditAttribution: DanChadwick commented
Status: Needs review » Active

Nothing to review here.

gaurav.goyal’s picture

gaurav.goyal CreditAttribution: gaurav.goyal commented

Hi DanChadwick,

Thanks for your suggestion..:)

I think archiving with drush commands is really a good option. And i have already built the module which supports webform 7.x-3.x and will also built for 7.x because a lot of sites are still using 7.x-3.x version.

Thanks for your help.

DanChadwick’s picture

DanChadwick CreditAttribution: DanChadwick commented
Status: Active » Fixed

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

svt’s picture

svt CreditAttribution: svt commented

I am facing the same issue, also I have some real time query interface for admin users, who needs to see the webform submission data for let say insurance quotes like data. I am also planning a parallel web service based application for webform data archiving and fetch from that application when required.

But it seems time consuming task to create another parallel application to store archived data through web service. Any other easy and less time consuming solution you can provide.

gaurav.goyal’s picture

gaurav.goyal CreditAttribution: gaurav.goyal at Innoraft commented

Hey @svt,

I created a module to handle this - https://www.drupal.org/project/webform_archive. It should be helpful enough. Let me know if you need anything here.

jenna_katie_matthews’s picture

jenna_katie_matthews CreditAttribution: jenna_katie_matthews commented

I installed webform_archive, however when I enter in the "Archive Active Webforms" I see the message, "There are no active web forms". How do I make active my existing webforms so that they can be archived?

jenna_katie_matthews’s picture

jenna_katie_matthews CreditAttribution: jenna_katie_matthews commented

After installing the Webform Archive module I don't see any webforms in the "Archive Active Webforms" section. I'm not sure why since I have several active webforms with submissions.

I even closed a webform and I don't see it listed in the "Archive Closed Webforms" section.

Help!?