View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|09620||Feature requests||Other||public||2015-05-06 01:12||2021-03-07 20:43|
|Summary||09620: 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) 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?
|Tags||No tags attached.|
No array data type in MySQL os msSQL. Only in pgSQL.
PS: i think we need better DB system, but not used this one ;).
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.
|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||View Revisions|
|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|