Improve efficiency of options in select.inc
| Project: | Webform |
| Version: | 6.x-2.6 |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | needs work |
Given that there are several duplicates of the same issue, but none that stood out to cover all parts of the problem, I wanted to create this "Master Issue" that could be used to cover all parts of this optimization & configuration effort.
Many users have complained of problems exporting aka downloading the results/submissions from webforms, either as CSV/delimited files or as Excel spreadsheets with either error messages or more commonly just blank screens after several minutes waiting. Further, the watchdog log / dblog may not record any error messages.
There are two parts to this problem:
- memory limits building the output
- timeouts because it takes too long to finish building the output
Solving Problem 1: Memory Limits
Resolving memory limits may be as simple as adjusting the .htaccess file where it says:
# PHP 5, Apache 1 and 2.
<IfModule mod_php5.c>
php_value magic_quotes_gpc 0
php_value register_globals 0
php_value session.auto_start 0
php_value mbstring.http_input pass
php_value mbstring.http_output pass
php_value mbstring.encoding_translation 0
</IfModule>just add the following extra lines before the line:
# New settings:
php_value memory_limit 128MUnfortunately some web hosts may limit your control over this and may not allow the memory_limit value be configured through a .htaccess file, in which case it may have to be modified in the system's primary php.ini file. The standard system requirements call for this to be 16M (i.e. 16 megabytes) but you may find a need for it to be 64M, 96M or even 128M depending on the number of records or fields in the webform.
Solving Problem 2: Timeout
The PHP configuration variable max_execution_time controls how long the PHP parser will allow a script to run before being forcibly terminated. By default this is set to 30 seconds, but depending on the number of records and fields this will probably not be sufficient. Similarly to the memory limit, this can be configured through the .htaccess file, as follows:
# New settings:
php_value max_execution_time 180 #secondsUnfortunately, even this may not be sufficient as it may still take a very long time to complete.
Proposed Solution
The output generation is controlled by the webform_results_download() function in webform_report.inc which generates a delimited file in the desired format. Tracing through the file, the initial lines run rather quickly but the
<?php
"foreach ($submissions..."
?>
#1
Thanks Damien for taking initiative on this. I marked the other issues you found as duplicates.
#273885: blank page when downloading results (memory exhausted)
#392290: Submissions: Modifying CSV download function, or SQL statement
#323493: Cannot download results of webform
#407460: "Download" results link leads to blank screen
#2
I added a ton of microtimer() statements to the webform_results_download() function and recorded the execution time taken to output ten records on my MacBook Pro. The top row indicates what the field was to store, the second indicates the time taken to output that one cell; the first column contains the row number, second-last column indicates how long the fwrite() call took, the last column indicates how long the processing of the entire record took; the State select field stores a list of all US States, Canadian provinces and US outlying territories grouped with optgroups, while the Country select stores a list of ~240 countries.
As you can see, textfield and email fields fly through in no time, but selects take a very long time, especially if there are a lot of predefined options to check against.
#3
Oops, I renamed the file before uploading..
#4
Here's a patch for _webform_filter_values() that keeps track of whether it has already loaded the user profile, which saves on a large number of unneeded queries.
#5
FYI the first patch has boosted the local execution time from ~0.7s per row to ~0.29s, as seen in the attached CSV.
#6
Another patch, this one keeps a static array of the processed options variable from components/select.inc, indexed by the md5sum of the unprocessed options variable.
#7
FYI the second patch has given an approximate 100:1 speed optimization, as seen in the attached CSV.
#8
I've tested both of the patches on a copy of our production database and I can now successfully output a 6877 record webform in a matter of minutes versus it timing out after ten minutes having barely gotten half-way. Please test the patches and provide feedback. Thank you.
#9
Please also test the attached cumulative patch for Dupal 5.
#10
Have identified a problem stemming from not separating the cached select lists based on whether they were set to be $flat or not. Here's a new patch for D6 that takes this into account.
#11
An updated patch for D5 based on the bug discovered for comment 10.
#12
FYI the problem identified in comment 10 would only be a problem when using option groups (optgroup).
#13
FWIW, my issue at #407460: "Download" results link leads to blank screen is NOT a timeout issue. My webform had 5 fields, and only 4 submissions -- I'm pretty certain that if the server can handle views and aggregator and all of the other parts of that site, it's not a memory error that leaves this screen blank.
#14
Just to see, please try the patch and see if it makes a difference.
#15
Oooh, turns out I had some other problem. I tried to apply the patch and got a big FAIL message. So I re-extracted the webform module and now everything works perfectly (sans patch). Thanks for your help!
(I always think it is so weird when that happens -- I expect that if some file gets corrupted I will end up with big PHP errors and a huge mess, not some stealth unobtrusive error.)
#16
leenwebb: What was the error message? D5 or D6? I patched against the relevant branch in CVS rather than the release versions.
#17
D5; it was a patch error that said that Chunk 2 had failed during the webform.module patching. I figured that since I hadn't changed anything in the actual webform module, something was amiss! Hence the re-extraction of a re-downloaded zip and now everything works as expected.
#18
I came up with a similar patch (to #4) before seeing this issue, and for me it alone speeds things up enough to avoid the PHP timeout problem.
#19
I applied the patches in #4 and #6 and it fixed my timeout issue - Havent noticed any bad effects yet. Thanks for the patch
#20
jakraska: thanks. Maybe we'll be able to twist Quicksketch's arm into pushing out an update to lock it in? :)
#21
I definitely like the improvements to _webform_filter_values(). There's no good reason to load that multiple times. The changes to select.inc look extremely suspicious though. Right now there's accounting in the static variable for "$flat = TRUE" and "$flat = FALSE", but it doesn't account for multiple select lists that are both flat or both not flat. It looks like this would erroneously return options for the first select list in the form for all the remaining select lists. Perhaps the $cid needs to be passed into the select list options.
A small code-style issue also: comments should be formatted as sentences, starting with capital letters and ending with periods.
#22
Just a note that caching to _webform_filter_values() have been included in the 2.7 version. The select list options would still be good to cache, but they still require work per my comments in #21.
#23
Thanks, I'll look into it.