View Issue Details

IDProjectCategoryView StatusLast Update
12990Bug reports[All Projects] Survey participants (Tokens)public2018-01-30 14:29
ReporterDeveloperChris Assigned Toc_schmitz  
Status closedResolutionfixed 
Product Version2.64.x 
Target VersionFixed in Version3.1.x 
Summary12990: Queries on large participants table uses 100% CPU and causes LS to become non responsive

When running a "not completed" export on the participants table that has a moderate number of recipients the mysql server spikes to 100% on a single CPU and the application fails to respond to further requests.

I am not sure why limesurvey stops responding until the report is complete. It should only prevent access to the effected survey but even the front page does not render! I have not resolved why that is.

The participant table we are querying has 81K records of which 53K have responses in the survey table.

The query created by LS is as shown in the slow query log which shows the query took ~8 Minutes to process

Time: 171205 8:46:46

User@Host: limesurvey[limesurvey] @ []

Query_time: 472.000994 Lock_time: 0.000173 Rows_sent: 25882 Rows_examined: 1491192966

SET timestamp=1512425806;
FROM lime_tokens_982413
WHERE ((1=1) AND (completed='N')) AND (token not in (select token from lime_survey_982413 group by token))

I have created a work around that avoids the use of mysql's IN statement a notoriously slow statement...

SELECT * FROM lime_tokens_982413 lt LEFT JOIN lime_survey_982413 ls ON lt.token=ls.token WHERE ((1=1) AND (lt.completed='N')) AND ( IS NULL) ORDER BY lt.tid

Additional Information

I have attached a diff file for the changes I made to resolve this issue. Please do not use it verbatim but instead verify it for use in all DB scenario's it seems to work fine with MYSQL/MariaDB

Please also avoid using the "IN" statement Joins are far more efficient. creating a join on a temporary table can also help avoid the IN statement.

TagsNo tags attached.
Complete LimeSurvey version number (& build)Version 2.64.7+170404
I will donate to the project if issue is resolvedNo
Database & DB-VersionServer version: 5.5.58-0+deb8u1 (Debian)
Server OS (if known)NA
Webserver software & version (if known)NA
PHP VersionNA




2017-12-05 02:45


export_helper.php.diff.txt (3,639 bytes)
diff --git a/public_html/application/helpers/export_helper.php b/public_html/application/helpers/export_helper.php
index ae83a08..7483ecc 100644
--- a/public_html/application/helpers/export_helper.php
+++ b/public_html/application/helpers/export_helper.php
@@ -1712,60 +1712,65 @@ function tokensExport($iSurveyID)
     $bIsNotAnonymous= ($oSurvey->anonymized=='N' && $oSurvey->active=='Y');// db table exist (survey_$iSurveyID) ?

-    $oRecordSet = Yii::app()->db->createCommand()->from("{{tokens_$iSurveyID}}");
+    $oRecordSet = Yii::app()->db->createCommand()->from("{{tokens_$iSurveyID}} lt");
     $databasetype = Yii::app()->db->getDriverName();
     if ($sEmailFiter!='')
         if (in_array($databasetype, array('mssql', 'sqlsrv', 'dblib')))
-            $oRecordSet->andWhere("CAST(email as varchar) like ".dbQuoteAll('%'.$sEmailFiter.'%', true));
+            $oRecordSet->andWhere("CAST( as varchar) like ".dbQuoteAll('%'.$sEmailFiter.'%', true));
-            $oRecordSet->andWhere("email like ".dbQuoteAll('%'.$sEmailFiter.'%', true));
+            $oRecordSet->andWhere(" like ".dbQuoteAll('%'.$sEmailFiter.'%', true));
     if ($iTokenStatus==1)
-        $oRecordSet->andWhere("completed<>'N'");
+        $oRecordSet->andWhere("lt.completed<>'N'");
     elseif ($iTokenStatus==2)
-        $oRecordSet->andWhere("completed='N'");
+        $oRecordSet->andWhere("lt.completed='N'");
         if ($bIsNotAnonymous)
-            $oRecordSet->andWhere("token not in (select token from {{survey_$iSurveyID}} group by token)");
+            // $oRecordSet->andWhere("token not in (select token from {{survey_$iSurveyID}} group by token)");
+          $oRecordSet->leftJoin("{{survey_$iSurveyID}} ls", 'lt.token=ls.token');
+          $oRecordSet->andWhere(" IS NULL");
     if ($iTokenStatus==3 && $bIsNotAnonymous)
-        $oRecordSet->andWhere("completed='N' and token in (select token from {{survey_$iSurveyID}} group by token)");
+         // $oRecordSet->andWhere("lt.completed='N' and token in (select token from {{survey_$iSurveyID}} group by token)");
+      $oRecordSet->leftJoin("{{survey_$iSurveyID}} ls", 'lt.token=ls.token');
+      $oRecordSet->andWhere("lt.completed='N'");
+      $oRecordSet->andWhere(" IS NULL");

     if ($iInvitationStatus==1)
-        $oRecordSet->andWhere("sent<>'N'");
+        $oRecordSet->andWhere("lt.sent<>'N'");
     if ($iInvitationStatus==2)
-        $oRecordSet->andWhere("sent='N'");
+        $oRecordSet->andWhere("lt.sent='N'");

     if ($iReminderStatus==1)
-        $oRecordSet->andWhere("remindersent<>'N'");
+        $oRecordSet->andWhere("lt.remindersent<>'N'");
     if ($iReminderStatus==2)
-        $oRecordSet->andWhere("remindersent='N'");
+        $oRecordSet->andWhere("lt.remindersent='N'");

     if ($sTokenLanguage!='')
-        $oRecordSet->andWhere("language=".dbQuoteAll($sTokenLanguage));
+        $oRecordSet->andWhere("lt.language=".dbQuoteAll($sTokenLanguage));
-    $oRecordSet->order("tid");
+    $oRecordSet->order("lt.tid");
     $bresult = $oRecordSet->query();
     //HEADERS should be after the above query else timeout errors in case there are lots of tokens!
     header("Content-Disposition: attachment; filename=tokens_".$iSurveyID.".csv");


2017-12-05 02:53

reporter   ~45248

Just as an extra note the reworked SQL Query using joins went from 472 seconds to 0.76 seconds

CREATE TEMPORARY TABLE table5 as (SELECT lt.* FROM lime_tokens_982413 lt LEFT JOIN lime_survey_982413 ls ON (lt.token=ls.token) WHERE lt.completed='N' AND IS NULL GROUP BY lt.token order by lt.tid);
Query OK, 25908 rows affected (0.76 sec)
Records: 25908 Duplicates: 0 Warnings: 0

Creating the temporary table has a small amount of overhead so the actual query. The purpose of creating the temporary table is to prevent echoing the results of the query to the screen.



2018-01-24 18:01

administrator   ~46045

Fix committed to master branch:;id=26224



2018-01-24 18:02

administrator   ~46046

Thank you very much. Please provide a pull request on GitHub next time. That way we can much faster merge your change.

Related Changesets

LimeSurvey: master 003356d1

2018-01-24 18:01:07


Details Diff
Fixed issue 12990: Exporting non-response participants very slow - fix by DeveloperChris Affected Issues
mod - application/helpers/export_helper.php Diff File

Issue History

Date Modified Username Field Change
2017-12-05 02:45 DeveloperChris New Issue
2017-12-05 02:45 DeveloperChris File Added: export_helper.php.diff.txt
2017-12-05 02:53 DeveloperChris Note Added: 45248
2018-01-24 17:46 c_schmitz Assigned To => c_schmitz
2018-01-24 17:46 c_schmitz Status new => assigned
2018-01-24 18:01 c_schmitz Changeset attached => LimeSurvey master 003356d1
2018-01-24 18:01 c_schmitz Note Added: 46045
2018-01-24 18:01 c_schmitz Resolution open => fixed
2018-01-24 18:02 c_schmitz Status assigned => resolved
2018-01-24 18:02 c_schmitz Fixed in Version => 3.0.x
2018-01-24 18:02 c_schmitz Note Added: 46046
2018-01-30 14:29 ollehar Status resolved => closed
2018-01-30 14:29 ollehar Fixed in Version 3.0.x => 3.1.x