Hiya,

One of my websites has a LOT of forms, and they are heavily used. This means that the Webform module's "SID" is rapidly approaching 1 million, as it notches up a +1 for every form submission across the entire site.

Is there not a form-specific submission ID which starts at 0 for every unique instance of a webform? I need to access it the instant the form is submitted, as my submissions also write CSV files which are immediately transferred over sFTP, and this ID needs to be transmitted in them.

I guess I could make something to utilise a new SQL table, but it seems crazy this has been overlooked?

Thanks,
H

Comments

quicksketch’s picture

quicksketch
he/him
commented
Category: feature » support

I don't understand why the Submission ID approaching 1 million is a problem. By default the webform_submissions table uses a regular INT column, which has a limit of 4,294,967,295 (about 4 billion) before the ID runs out of space. If that happens you could in theory increase the size of the column to a BIGINT and then it would hold much more yet.

In any case, having per-form IDs won't help you at all anyway, since even if the IDs were separate the problem you're likely to have is just the table getting too big and slow for MySQL to handle. I'd suggest trimming submissions that are no longer necessary or copying them to a backup table. I've seen Webform used in situations that have gone well in excess of 100 million submissions using similar methods.

hfidge’s picture

hfidge commented

Hiya, I don't think it's a problem that the SID is getting big, it's just that it's caused some post-processing issues:

- Form A set up first, submissions roll from 0 to say.. 10
- Form B set up. Very popular competition. SID starts at 11 but rapidly progresses to 100,000
- Next submission on form A has SID of 100001
- The pattern for form A is then 0,1,2,3,4,5,6,7,8,9,10,100001

They are unique, which is OK, but our CRM system throws a wobbly when it sees that, so we're having to spend quite a lot of time post-processing the submissions to ensure the IDs are regular and with no gaps.

I take your point about MySQL efficiency, but would this actually be such a problem?

Cheers for the thoughts, H

quicksketch’s picture

quicksketch
he/him
commented

I take your point about MySQL efficiency, but would this actually be such a problem?

I think it would be more difficult to do per-form submission IDs in a lot of ways:
- Doing per-form IDs means that all tables would have to have two IDs (nid and sid) as the primary key in the database, making for more complicated and slower joins.
- By the same token the globally unique SID makes things like Views integration significantly easier.
- The webform_submissions database table would have to LOCK the table when inserting data to ensure key conflicts do not happen, similar to what we have to do with webform_components table (see #389282: Webform shouldn't lock the "webform_components" table on insert, use locking framework instead). This could be a huge performance problem because it'd make it so that only one submission could be saved at a time; a serious problem for sites with a lot of submissions.

hfidge’s picture

hfidge commented

OK thats fair enough. I'll see if we HAVE to have this and if so I'll do it post-process, perhaps on CRON run or something via a custom module.

Thanks for the pointers!

quicksketch’s picture

quicksketch
he/him
commented
Status: Active » Closed (fixed)