Submissions: Modifying CSV download function, or SQL statement [#392290]

Hi there!

I have a webform set up on a site that is seeing unexpectedly high traffic. The webform_submitted_data table has almost 1.5 million records in it.

I've increased the php memory limit and execution time to extremely high levels, but the CSV download is still timing out or exceeding memory.

I've been poring over the module's code, looking for one of two things:
1. An SQL statement that I can run in phpmyadmin and export to CSV (unlikely, given the structure of the table).
2. A method to pass parameters to the download function, so it only downloads "records X to Y". I see reference to a pager_query in the module, but I'm not sure if/how it can be applied to the CSV download.

I haven't had any luck with either option. I'm hoping that someone more familiar with this module in particular, and Drupal modules in general, might be able to provide some advice.

Thank you in advance!
Dave

Comments

Comment #1

AtomicStudios commented 6 March 2009 at 19:51

We have the same problem, only we have about 5.7 million records in the submission data table.

Here is the query for an 18k + entries in a large form (231075 records total called), the query runs in 8 seconds in Dev and 3 in production.

SELECT s.*, sd.cid, sd.no, sd.data, u.name, u.mail, u.status
FROM base_webform_submissions s
LEFT JOIN base_webform_submitted_data sd ON sd.sid = s.sid
LEFT JOIN base_users u ON u.uid = s.uid
WHERE sd.nid = 2243
ORDER BY sid ASC, cid ASC, no ASC

1 SIMPLE sd ref PRIMARY,nid,sid_nid,nid_2 nid_2 4 const 231075 Using where; Using temporary; Using filesort
1 SIMPLE s eq_ref PRIMARY,sid_nid PRIMARY 4 drupaldb_quimby.sd.sid 1
1 SIMPLE u eq_ref PRIMARY PRIMARY 4 drupaldb_quimby.s.uid 1

The query doesn't seem to be the issue like I said, however we did add index's (nid,sid,cid) to that table which helped a ton. the place that is taking up the most time is in this loop turning the object into another object. (Row 184 in webform_submissions.inc)

while ($row = db_fetch_object($res)) {
if ($row->sid != $previous) {
$submissions[$row->sid] = new stdClass();
$submissions[$row->sid]->sid = $row->sid;
$submissions[$row->sid]->submitted = $row->submitted;
$submissions[$row->sid]->remote_addr = $row->remote_addr;
$submissions[$row->sid]->uid = $row->uid;
$submissions[$row->sid]->name = $row->name;
$submissions[$row->sid]->status = $row->status;
}
$submissions[$row->sid]->data[$row->cid]['value'][$row->no] = $row->data;
$previous = $row->sid;
}

I'm kind of stumped... I might try modifying it so it doesn't have to create this object over again and just use the $row object instead.