Defect #16091

Export CSV with many custom field runs many queries

Added by Johan Guilbaud about 4 years ago. Updated about 4 years ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:Jean-Philippe Lang% Done:

0%

Category:Custom fields
Target version:2.5.0
Resolution:Fixed Affected version:2.4.2

Description

Hello!

When exporting issues in CSV with lot of custom field (more than 20) it's very slow. We've made an audit and we have found there are many sql requests like that : (we use mysql)

140127 16:38:49 2 Query SELECT `issues`.* FROM `issues` WHERE `issues`.`id` = 35006 LIMIT 1
2 Query SELECT `issue_relations`.* FROM `issue_relations` WHERE `issue_relations`.`issue_from_id` = 35007
2 Query SELECT `issue_relations`.* FROM `issue_relations` WHERE `issue_relations`.`issue_to_id` = 35007
2 Query SELECT `custom_values`.`id` AS t0_r0, `custom_values`.`customized_type` AS t0_r1, `custom_values`.`customized_id` AS t0_r2, `custom_values`.`custom_field_id` AS t0_r3, `custom_values`.`value` AS t0_r4, `custom_fields`.`id` AS t1_r0, `custom_fields`.`type` AS t1_r1, `custom_fields`.`name` AS t1_r2, `custom_fields`.`field_format` AS t1_r3, `custom_fields`.`possible_values` AS t1_r4, `custom_fields`.`regexp` AS t1_r5, `custom_fields`.`min_length` AS t1_r6, `custom_fields`.`max_length` AS t1_r7, `custom_fields`.`is_required` AS t1_r8, `custom_fields`.`is_for_all` AS t1_r9, `custom_fields`.`is_filter` AS t1_r10, `custom_fields`.`position` AS t1_r11, `custom_fields`.`searchable` AS t1_r12, `custom_fields`.`default_value` AS t1_r13, `custom_fields`.`editable` AS t1_r14, `custom_fields`.`visible` AS t1_r15, `custom_fields`.`multiple` AS t1_r16 FROM `custom_values` LEFT OUTER JOIN `custom_fields` ON `custom_fields`.`id` = `custom_values`.`custom_field_id` WHERE `custom_values`.`customized_id` = 35007 AND `custom_values`.`customized_type` = 'Issue' ORDER BY custom_fields.position

NB : there are differences in sql requests used between using a filter with all columns with doing export on selected column and no filter and doing export on all columns...

Whe are about 5 minutes to export 3300 issues with 20 customs fields...


Related issues

Related to Redmine - Feature #16638: Optimizing CSV export New

Associated revisions

Revision 12894
Added by Jean-Philippe Lang about 4 years ago

Always preload issues custom values (#16091).

Revision 12910
Added by Jean-Philippe Lang about 4 years ago

Preload issue relations when exporting issues to CSV with all columns (#16091).

Revision 12911
Added by Jean-Philippe Lang about 4 years ago

Preload issue authors when displayed on the issue list (#16091).

History

#1 Updated by Jean-Philippe Lang about 4 years ago

  • Subject changed from Export CSV with many custom field to Export CSV with many custom field runs many queries
  • Status changed from New to Closed
  • Assignee set to Jean-Philippe Lang
  • Target version set to 2.5.0
  • Resolution set to Fixed

Fixed in r12894.

#2 Updated by Johan Guilbaud about 4 years ago

Nice !
thanks

#3 Updated by Johan Guilbaud about 4 years ago

i applied patch but i still have thousand of sql requests like :

SELECT `issue_relations`.* FROM `issue_relations` WHERE `issue_relations`.`issue_from_id` = 31071
SELECT `issue_relations`.* FROM `issue_relations` WHERE `issue_relations`.`issue_to_id` = 31071

#4 Updated by Jean-Philippe Lang about 4 years ago

  • Status changed from Closed to Resolved

This is fixed in r12910.

#5 Updated by Jean-Philippe Lang about 4 years ago

FWIW, exporting 3000 issues to CSV with all columns (including custom fields and relations) now takes about 13s.

#6 Updated by Jean-Philippe Lang about 4 years ago

  • Status changed from Resolved to Closed

Merged.

#7 Updated by Johan Guilbaud about 4 years ago

i've applied patches r12910 & r12911 but it always takes more than 5min and i still have the thousands of sql requests...

#8 Updated by Johan Guilbaud about 4 years ago

Johan Guilbaud wrote:

i've applied patches 12910 & 12911 but it always takes more than 5min and i still have the thousands of sql requests...

no answer ???

#9 Updated by Johan Guilbaud about 4 years ago

I'm still waiting for a response...

thanks.

#10 Updated by Toshi MARUYAMA about 4 years ago

Johan Guilbaud wrote:

I'm still waiting for a response...

This issue was closed with target version.
Please create a new issue if you have a problem in clean Redmine.

#11 Updated by Toshi MARUYAMA almost 4 years ago

Also available in: Atom PDF