View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 09044 | Bug reports | Response browsing | public | 2014-05-20 18:58 | 2014-05-28 16:01 |
| Reporter | pfpDave | Assigned To | c_schmitz | ||
| Priority | normal | Severity | partial_block | ||
| Status | closed | Resolution | won't fix | ||
| Product Version | 2.00+ | ||||
| Summary | 09044: Exporting results from very large survey fails on SQL Server YII Order By triggers SQL Server 8KB Row Limit | ||||
| Description | I don't know where to start with this one really and I can't upload an LSA because of the data in it but here goes anyway: I have a VERY large survey (with timings, tokens and questions I have 785 columns in the export and 479 result rows!) and my LimeSurvey instance is sat on SQL Server Express 2012. Exporting results now fails and here's why: \application\helpers\admin\exportresults_helper.php function loadSurveyResults approx line 320 executes the YII query builder like this: $survey->responses=$oRecordSet->select($aSelectFields)->order('{{survey_' . $survey->id . '}}.id')->limit($iLimit, $iOffset)->query()->readAll(); Using this before this line shows the query looks like the example below: SELECT * When executed in SQL Express Mgmt Studio this returns error: Msg 511, Level 16, State 1, Line 1 Which it seems to because of SQL Server's 8k limit on the 'work table' used to build the query results: http://msdn.microsoft.com/en-us/library/ms186981 Interestingly I can get the results to return if I remove the order method in the YII call like this: $survey->responses=$oRecordSet->select($aSelectFields)->limit($iLimit, $iOffset)->query()->readAll(); but of course the results are returned in no particular order. | ||||
| Tags | No tags attached. | ||||
| Bug heat | 4 | ||||
| Complete LimeSurvey version number (& build) | 130611 | ||||
| I will donate to the project if issue is resolved | No | ||||
| Browser | Chrome | ||||
| Database type & version | SQL Server Express 2012 | ||||
| Server OS (if known) | Windows Server 2008 R2 | ||||
| Webserver software & version (if known) | IIS 7 | ||||
| PHP Version | 5.4.8 | ||||
| Git branch / Pull Request URL | |||||
|
You can always do an export of only the first x fields and then the rest later. |
|
|
I thought that may be the case (won't fix) ... and I totally understand why ... however, just a couple of questions:
Cheers |
|
|
When exporting you can choose which fields (of your 758 columns) to export. Columns, not rows ;). This is not a Yii issue because the database is just not supporting such a big number of columns in a result set. |
|
|
Yeah of course I'm with you now ... I tried that too and it didn't seem to make a difference ... I clearly need to do some more testing. It's not that the DB is not allowing a large number of columns ... rather the size of the result set cannot exceed 8KB IF certain conditions are true (such has having an order by clause). I guess it's probably a rare issue though because most Limesurvey users won't be using MSSQL and other non-Limesurvey DBs will have a more normalised/efficient schema so we'll just have to work around it some how for now. Cheers again. Dave |
|
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2014-05-20 18:58 | pfpDave | New Issue | |
| 2014-05-25 21:59 | c_schmitz | Note Added: 30019 | |
| 2014-05-25 21:59 | c_schmitz | Status | new => closed |
| 2014-05-25 21:59 | c_schmitz | Assigned To | => c_schmitz |
| 2014-05-25 21:59 | c_schmitz | Resolution | open => won't fix |
| 2014-05-25 22:11 | pfpDave | Note Added: 30020 | |
| 2014-05-25 22:11 | pfpDave | Status | closed => feedback |
| 2014-05-25 22:11 | pfpDave | Resolution | won't fix => reopened |
| 2014-05-25 22:14 | c_schmitz | Note Added: 30021 | |
| 2014-05-25 22:18 | pfpDave | Note Added: 30022 | |
| 2014-05-25 22:18 | pfpDave | Status | feedback => assigned |
| 2014-05-25 22:18 | pfpDave | Note Edited: 30022 | |
| 2014-05-28 16:01 | c_schmitz | Status | assigned => closed |
| 2014-05-28 16:01 | c_schmitz | Resolution | reopened => won't fix |