Project

General

Profile

Actions

Defect #24473

open

CSV Export Very slow for issues with lots of Custom Fields

Added by Stephane Evr almost 8 years ago. Updated almost 7 years ago.

Status:
New
Priority:
Normal
Assignee:
-
Category:
Performance
Target version:
-
Start date:
Due date:
% Done:

0%

Estimated time:
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...


Files

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

Related issues

Related to Redmine - Feature #24477: CSV export feature for thousand of issuesNew

Actions
Related to Redmine - Patch #31076: Issues CSV / PDF export via ActiveJobNew

Actions
Has duplicate Redmine - Defect #24755: Slow Queries because of Custom FieldsClosed

Actions
Actions #1

Updated by Stephane Evr almost 8 years ago

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

Actions #2

Updated by Toshi MARUYAMA almost 8 years ago

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

Updated by Go MAEDA almost 7 years ago

  • Category set to Performance
  • Priority changed from High to Normal
Actions #4

Updated by Go MAEDA almost 7 years ago

  • Has duplicate Defect #24755: Slow Queries because of Custom Fields added
Actions #5

Updated by Go MAEDA over 5 years ago

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

Also available in: Atom PDF