View Issue Details

This issue affects 1 person(s).
 4
IDProjectCategoryView StatusLast Update
09044Bug reportsResponse browsingpublic2014-05-28 16:01
ReporterpfpDave Assigned Toc_schmitz  
PrioritynormalSeveritypartial_block 
Status closedResolutionwon't fix 
Product Version2.00+ 
Summary09044: 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:
echo $oRecordSet->getText();
die;

SELECT *
FROM [survey_816525]
LEFT JOIN [tokens_816525] [tokentable] ON tokentable.token=survey_816525.token
LEFT JOIN [survey_816525_timings] [survey_timings] ON survey_816525.id = survey_timings.id

When executed in SQL Express Mgmt Studio this returns error:

Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 12454 which is greater than the allowable maximum row size of 8060.

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.

TagsNo tags attached.
Bug heat4
Complete LimeSurvey version number (& build)130611
I will donate to the project if issue is resolvedNo
BrowserChrome
Database type & versionSQL Server Express 2012
Server OS (if known)Windows Server 2008 R2
Webserver software & version (if known)IIS 7
PHP Version5.4.8
Git branch / Pull Request URL

Users monitoring this issue

There are no users monitoring this issue.

Activities

c_schmitz

c_schmitz

2014-05-25 21:59

administrator   ~30019

You can always do an export of only the first x fields and then the rest later.
As it is DB dependent I don't see an immediate solution here.

pfpDave

pfpDave

2014-05-25 22:11

reporter   ~30020

I thought that may be the case (won't fix) ... and I totally understand why ... however, just a couple of questions:

  • when you say only export first x fields ... do you mean records and if so then I tried that and it doesn't seem to make a difference ... the filter seems to happen after the full recordset has been returned

  • Would it be worth raising this with the YII community as it's essentially a YII failing is it not?

Cheers

c_schmitz

c_schmitz

2014-05-25 22:14

administrator   ~30021

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.

pfpDave

pfpDave

2014-05-25 22:18

reporter   ~30022

Last edited: 2014-05-25 22:18

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

Issue History

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