Dependency Graph

Dependency Graph
related to related to child of child of duplicate of duplicate of

View Issue Details

This bug affects 1 person(s).
 4
IDProjectCategoryView StatusLast Update
09620Feature requestsOtherpublic2021-03-07 20:43
Reporterronny_todgers Assigned Toc_schmitz  
PrioritynormalSeverityfeature 
Status closedResolutionduplicate 
Summary09620: proposed fix to the 1600 column issue
Description

The current data table model in use in limesurvey makes a lot of sense for small simple surveys but it's actually remarkably easy to hit the 1600 column limit if you have even just a few pages of large array questions. Yes I know you can hack the db server to increase this a bit but its not really the right way to address the issue.

The bigger issue is that database performance when dealing with tables this wide is pretty horrible. There's obviously a wider related issue surrounding this and it touches on the great conveniences EM have brought to limesurvey. BUT rather than get too bogged down in the major issues - how about a quick and significant improvement?

Big array questions generate vast numbers of varchar(5) columns - why not use the array data type instead? So instead of 500 varchar(5) columns you have 1 varchar(5)[500] column. Arrays of texts as text[] instead of text etc etc.

I haven't scoured the source to try and work out everywhere this would need to go but from a quick look, given the fact you support multiple db types this would be a small change in a localised place? It would only be Postgres of course - I don't think MySQl does this - but it would pretty much eliminate the big table issue as you only get close to that limit with the help of huge array questions.

Feels like a potentially easy win to me - but maybe I'm the only one regularly getting irritated by this? Anyone else?

TagsNo tags attached.
Bug heat4

Relationships

duplicate of 05289 acknowledgedc_schmitz Redesign response storage to support more questions+answers (database columns) in a survey 

Activities

DenisChenu

DenisChenu

2015-05-06 11:39

developer   ~32099

Last edited: 2015-05-06 11:40

No array data type in MySQL os msSQL. Only in pgSQL.

PS: i think we need better DB system, but not used this one ;).

ronny_todgers

ronny_todgers

2015-05-06 11:49

reporter   ~32102

Hi Denis - yes it could only be for Postgres, and many people's needs are served within the column limits and can choose any db backend. However it would be great to have the option of setting it up with Postgres and effectively removing this limit.

Essentially the problem could be solved for all databases by having a table corresponding to each data type you support and EAV the data table rather than having a single wide data table - but this feels like a awful lot of work AND would have to be combined with modifications to not just return the whole super big table in one call as the column limits essentially apply to select queries too.

As I say the "proper" solution is hard and touches on all sorts of deep design issues. This could be a relatively easy improvement which would allow much bigger surveys without having to deal with those other thorny issues.

Issue History

Date Modified Username Field Change
2015-05-06 01:12 ronny_todgers New Issue
2015-05-06 11:39 DenisChenu Note Added: 32099
2015-05-06 11:40 DenisChenu Note Edited: 32099
2015-05-06 11:49 ronny_todgers Note Added: 32102
2021-03-07 20:43 c_schmitz Relationship added duplicate of 05289
2021-03-07 20:43 c_schmitz Assigned To => c_schmitz
2021-03-07 20:43 c_schmitz Status new => closed
2021-03-07 20:43 c_schmitz Resolution open => duplicate