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.
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
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments