SQL improve performance (Left join?)

Question

Due performance issues, the query below is not working anymore. I have few left joins.

The main issue is that I'm searching 3 "MAX" in the same table, in order to get max from different values.

Is there are way to optimize this?

SELECT B.BG_BUG_ID "Defect ID" ,
       BG_USER_TEMPLATE_16 "Status" ,
       BG_USER_TEMPLATE_12 "Priority"  ,
       BG_USER_TEMPLATE_11 "Severity",
       BG_DETECTION_DATE "Detectado em",
       BG_USER_TEMPLATE_01 "Fase Detecção",
       BG_USER_TEMPLATE_10 "Fornecedor",
       BG_USER_TEMPLATE_18 "Area Responsavel",
       BG_RESPONSIBLE "Assinado para",
       BG_USER_TEMPLATE_09 "Sistema Correção",
       BG_USER_TEMPLATE_17 "Ambiente",
       BG_USER_TEMPLATE_19 "Causa Raiz",
       c.rtt_Time "RTT time",
       BG_CLOSING_DATE "Data de Fechamento",
       BG_USER_17 "Data Alteração Status",
       al_description "Caminho Test Plan",
       BG_SUMMARY  "Descrição Resumida",
       B.Fixed_Time "Fixed time",
       BG_DETECTED_BY   "Detectado por",
       BG_USER_TEMPLATE_15  "Motivo",
       BG_USER_TEMPLATE_04  "Motivo Status Waiting",
       BG_USER_01 "Pendente com",
       A.reopen_Time "lAST REOPEN DATE",
       BG_TARGET_RCYC "Target Cycle",
       R_CYCLE.rcyc_name "Target Cycle",
       REL_NAME "Target Release",
       BG_TARGET_REL "Target Release" ,
       BG_USER_TEMPLATE_08 "Tipo",
       BG_USER_TEMPLATE_07 "Tipo de Solicitação",
       BG_USER_TEMPLATE_06 "Status da Solicitação",
       BG_USER_04 "Contador Fixing",
       BG_USER_26 /*Defect.Reaberturas Validas*/ "Contador Reopened",
       BG_USER_TEMPLATE_20 "CTs Impactados",
       BG_USER_20 "Data Prevista Correção",
       BG_USER_23 "Macro Processo",
       BG_USER_TEMPLATE_03 "Defeito Associado"
FROM BUG B LEFT JOIN  ALL_LISTS AL
ON B.bg_subject = AL.al_item_id
LEFT JOIN (SELECT bg_bug_id, max(au_time) reopen_Time
     from
       bug,audit_log, audit_properties
     where AU_ENTITY_TYPE='BUG'
     and au_entity_id=bg_bug_id
     AND AP_ACTION_ID=au_ACTION_ID
     AND AP_FIELD_NAME='BG_USER_TEMPLATE_16'
     AND AP_NEW_VALUE='Reopened'
     group by bg_bug_id)  A
ON A.BG_BUG_ID=B.BG_BUG_ID
LEFT JOIN (SELECT bg_bug_id, max(au_time) fixed_time
     from
       bug,audit_log, audit_properties
     where AU_ENTITY_TYPE='BUG'
     and au_entity_id=bg_bug_id
     AND AP_ACTION_ID=au_ACTION_ID
     AND AP_FIELD_NAME='BG_USER_TEMPLATE_16'
     AND AP_NEW_VALUE='Ready to Application'
     group by bg_bug_id)  B
ON A.BG_BUG_ID=B.BG_BUG_ID
LEFT JOIN (SELECT bg_bug_id, max(au_time) rtt_time
     from
       bug,audit_log, audit_properties
     where AU_ENTITY_TYPE='BUG'
     and au_entity_id=bg_bug_id
     AND AP_ACTION_ID=au_ACTION_ID
     AND AP_FIELD_NAME='BG_USER_TEMPLATE_16'
     AND AP_NEW_VALUE='Ready to Test'
     group by bg_bug_id)  C
ON A.BG_BUG_ID=B.BG_BUG_ID
left join RELEASE_CYCLES R_CYCLE
on b.BG_TARGET_RCYC = R_CYCLE.rcyc_id
Where   1=1
and BG_TARGET_RCYC= 1085
Order by B.BG_BUG_ID

You could get easy performance gains by not using old JOIN syntax. By using "FROM bug, audit_log, audit_properties" you're turning that JOIN into a CROSS JOIN. And you're doing it three times. You should have a read here: https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins — digital.aaron, Dec 07 '16 at 22:53
"Application Lifecycle Management" : kill it with fire. Hint: common subquery elimination. — wildplasser, Dec 08 '16 at 00:55

score 0 · Answer 1 · answered Dec 08 '16 at 00:02

0

Could you try putting your subqueries into temp tables ahead of time? Then just join the temp tables?

answered Dec 08 '16 at 00:02

Learning2Code

521
9
21

shawnt00 · Answer 2 · 2016-12-08T00:22:45.390

Looks to me like you can collapse those three subqueries/derived tables to the single one below. I don't want to get yelled at for not updating your join syntax but without table aliases I don't feel like guessing which columns correspond to what.

select
    bg_bug_id,
    max(case when AP_NEW_VALUE = 'Reopened' then au_time end) reopen_Time,
    max(case when AP_NEW_VALUE = 'Ready to Application' then au_time end) fixed_Time,
    max(case when AP_NEW_VALUE = 'Ready to Test' then au_time end) rtt_Time,
from bug, audit_log, audit_properties
where
    au_entity_id = bg_bug_id AND AP_ACTION_ID = au_ACTION_ID /* join conditions */
    AND AU_ENTITY_TYPE = 'BUG' AND AP_FIELD_NAME = 'BG_USER_TEMPLATE_16'
    AND AP_NEW_VALUE IN
        ('Reopened', 'Ready to Application', 'Ready to Test') /* not really necessary? */
group by bg_bug_id

The trick here is using case expressions to filter out the appropriate values for each of the aggregates.

Despite what I just said, here's a stab at the inner joins:

from
    bug b
    inner join audit_log l on l.au_entity = b.bg_bug_id
    inner join audit_properties p on p.ap_action_id = l.au_action_id

SQL improve performance (Left join?)

2 Answers2