Feature #16638

Optimizing CSV export

Added by Johan Guilbaud over 3 years ago. Updated 11 months ago.

Status:NewStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Performance
Target version:-
Resolution:

Description

#16091 closed, i open this new request

When exporting CSV export thousands of issues with tens of custom field export CSV is very very long.

When looking for generated sql request we can see there are thousands similar requests

SELECT  `issues`.* FROM `issues`  WHERE `issues`.`id` = <issue id> 

=> there is one of this request for each issue exported

Why not do one grouped request Like as request for custom fields

=> SELECT  `issues`.* FROM `issues`  WHERE `issues`.`id` IN( <issue id 1> ,<issue id 2>,<issue id ...>)

?
thanks


Related issues

Related to Redmine - Defect #16091: Export CSV with many custom field runs many queries Closed

History

#1 Updated by Johan Guilbaud over 3 years ago

please give me feedback...

thanks

#2 Updated by Toshi MARUYAMA over 3 years ago

  • Related to Defect #16091: Export CSV with many custom field runs many queries added

#3 Updated by Stephane Evr 11 months ago

Same problem here, with Redmine 3.3.0-stable. It goes on and on for thousand of log lines:

CACHE (0.0ms)  SELECT  `users`.* FROM `users` WHERE `users`.`type` IN ('User', 'AnonymousUser') AND `users`.`id` = 87 LIMIT 1  [["id", 87]]
   (0.4ms)  SELECT SUM(`time_entries`.`hours`) FROM `time_entries` WHERE `time_entries`.`issue_id` = 93903
  IssueRelation Load (0.2ms)  SELECT `issue_relations`.* FROM `issue_relations` WHERE `issue_relations`.`issue_from_id` = 93903
  IssueRelation Load (0.2ms)  SELECT `issue_relations`.* FROM `issue_relations` WHERE `issue_relations`.`issue_to_id` = 93903
   (0.4ms)  SELECT SUM(CAST(custom_values.value AS decimal(30,3))) FROM `issues` INNER JOIN `custom_values` ON `custom_values`.`customized_id` = `issues`.`id` AND `custom_values`.`customized_type` = 'Issue' WHERE `issues`.`root_id` = 93903 AND (issues.lft >= 1 AND issues.rgt <= 2) AND `custom_values`.`custom_field_id` = 410 AND (`custom_values`.`value` != '')
  Issue Load (0.2ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `users`.* FROM `users` WHERE `users`.`type` IN ('User', 'AnonymousUser') AND `users`.`id` = 0 LIMIT 1  [["id", 0]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93903 LIMIT 1  [["id", 93903]]
  CACHE (0.0ms)  SELECT  `users`.* FROM `users` WHERE `users`.`type` IN ('User', 'AnonymousUser') AND `users`.`id` = 604 LIMIT 1  [["id", 604]]
  CACHE (0.0ms)  SELECT  `users`.* FROM `users` WHERE `users`.`type` IN ('User', 'AnonymousUser') AND `users`.`id` = 87 LIMIT 1  [["id", 87]]
   (0.4ms)  SELECT SUM(`time_entries`.`hours`) FROM `time_entries` WHERE `time_entries`.`issue_id` = 93902
  IssueRelation Load (0.2ms)  SELECT `issue_relations`.* FROM `issue_relations` WHERE `issue_relations`.`issue_from_id` = 93902
  IssueRelation Load (0.2ms)  SELECT `issue_relations`.* FROM `issue_relations` WHERE `issue_relations`.`issue_to_id` = 93902
   (0.5ms)  SELECT SUM(CAST(custom_values.value AS decimal(30,3))) FROM `issues` INNER JOIN `custom_values` ON `custom_values`.`customized_id` = `issues`.`id` AND `custom_values`.`customized_type` = 'Issue' WHERE `issues`.`root_id` = 93902 AND (issues.lft >= 1 AND issues.rgt <= 2) AND `custom_values`.`custom_field_id` = 410 AND (`custom_values`.`value` != '')
  Issue Load (0.2ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 93902 LIMIT 1

#4 Updated by Toshi MARUYAMA 11 months ago

  • Description updated (diff)

#5 Updated by Toshi MARUYAMA 11 months ago

Stephane Evr wrote:

Same problem here, with Redmine 3.3.0-stable. It goes on and on for thousand of log lines:

[...]

FTR: #24473.

Also available in: Atom PDF