Improving Jira Oracle query performance

Question

We have a process that imports jira data into an oracle database for reporting. The issue I am having at the moment is extracting custom fields and converting a row into a column in oracle.

jira custom data view

jira data view

This is how I am extracting the query, the problem here is that the performance just does not scale.

select A.*, (select cf.date_value from v_jira_custom_fields cf where cf.issue_id = a.issue_id and cf.custom_field_name = 'Start Date') Start_Date,
(select cf.number_value from v_jira_custom_fields cf where cf.issue_id = a.issue_id and cf.custom_field_name = 'Story Points') Story_Points,
(select cf.custom_value from v_jira_custom_fields cf where cf.issue_id = a.issue_id and cf.custom_field_name = 'Ready') Ready
from jira_data A
where A.project = 'DAK'
and A.issue_id = 2222

Please re-read the question and relalize that you provide close to no information to get help. You didn't mention your *source* database, is it MySQL or Oracle as well? Please see [here](https://stackoverflow.com/a/34975420/4808122) a minimum information for Oracle that you should provide. Without it, you can get only one safe advice: "Use bind variables!" — Marmite Bomber, Feb 11 '21 at 11:27

score 2 · Accepted Answer · answered Feb 11 '21 at 11:46

To really understand where the bottleneck is we'd need to get an execution plan and info about indexes that exists, at least.

Assuming you have indexes on issue_id and project in both tables, what I'd try next is to get rid of the 3 separate selects and join your jira_data to pivoted jira_custom_fields

with P as (
    select
      project
    , issue_id
    , story_type_s
    , impacted_application_s
    , impacted_application_c
    , story_points_n
    , start_date_d
    , end_date_d
    , ready_c
    
    from v_jira_custom_fields
    
    pivot (
      max(string_value) as s
    , max(number_value) as n
    , max(text_value)   as t
    , max(date_value)   as d
    , max(custom_value) as c
    
      for customfield_id in (
        1 story_type
      , 2 impacted_application
      , 3 story_points
      , 4 start_date
      , 5 end_date
      , 6 ready
      )
    )
)
select
  A.*
, P.start_date_d   start_date
, P.story_points_n story_points
, P.ready_c        ready
from jira_data A
join P on A.project = P.project and A.issue_id = P.issue_id 
where A.project = 'DAK'
and A.issue_id = 2222

Improving Jira Oracle query performance

1 Answers1