Defect #24473

CSV Export Very slow for issues with lots of Custom Fields

Added by Stephane Evr over 3 years ago. Updated over 2 years ago.

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

0%

Category:Performance
Target version:-
Resolution: Affected version:

Description

Environment:
  Redmine version                3.3.0.stable
  Ruby version                   2.2.1-p85 (2015-02-26) [x86_64-linux]
  Rails version                  4.2.6
  Environment                    production
  Database adapter               Mysql2

So I tried to investigate why, when exporting an issue with lots of custom fields, my log file would look like this:

CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]
  CACHE (0.0ms)  SELECT  `issues`.* FROM `issues` WHERE `issues`.`id` = 94261 LIMIT 1  [["id", 94261]]

There is one line per custom field apparently.

My investigation led this method (in lib/redmine/field_format.rb):

def formatted_custom_value(view, custom_value, html=false)
  formatted_value(view, custom_value.custom_field, custom_value.value, custom_value.customized, html)
                                                                               #   |
                                                                               #THIS CALL
end

When custom_value.customized is called, this leads to the Customized object being loaded. Here, Customized is an issue, and it would be reloaded once per custom field.

Some caching would be needed here, where we directly provide the customized object to this method, although I am not sure what's best to do...

fix_cache_issues.patch Magnifier (6.57 KB) Stephane Evr, 2016-11-28 16:15


Related issues

Related to Redmine - Feature #24477: CSV export feature for thousand of issues New
Related to Redmine - Patch #31076: Issues CSV / PDF export via ActiveJob New
Duplicated by Redmine - Defect #24755: Slow Queries because of Custom Fields Closed

History

#1 Updated by Stephane Evr over 3 years ago

I have attached a (dirty) patch which fixed the problem for me. The export time is basically twice faster.

#2 Updated by Toshi MARUYAMA over 3 years ago

  • Related to Feature #24477: CSV export feature for thousand of issues added

#3 Updated by Go MAEDA over 2 years ago

  • Category set to Performance
  • Priority changed from High to Normal

#4 Updated by Go MAEDA over 2 years ago

  • Duplicated by Defect #24755: Slow Queries because of Custom Fields added

#5 Updated by Go MAEDA about 1 year ago

  • Related to Patch #31076: Issues CSV / PDF export via ActiveJob added

Also available in: Atom PDF