09620: proposed fix to the 1600 column issue - LimeSurvey bugs and feature requests

This bug affects 1 person(s).

4

ID	Project	Category	View Status	Date Submitted	Last Update

09620	Feature requests	Other	public	2015-05-06 01:12	2021-03-07 20:43

Reporter	ronny_todgers	Assigned To	c_schmitz
Priority	normal	Severity	feature
Status	closed	Resolution	duplicate

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)[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?
Tags	No tags attached.

Bug heat	4
Story point estimate
Users affected %

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 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.

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