help improving 'join' efficiency in sub-querys

[Penfold Horwood]

Hello all,

I am working for a client who is looking for a view, the rows come from one area however the data they want in the columns come from a removed area of the database.

In order to get the rows, I perform the following select:



Code:
select
grp.identifier
from ABM_form_group as frm_grp
join ABM_form_domain as frm_dom on frm_grp.ABM_form_domain_id = frm_dom.id
join ABM_group as grp on frm_grp.ABM_group_id = grp.id
where ABM_form_id = 10this is successful and quick, there is no issue with this element on its own however the value within the first column is obtained with the following sub-query:


Code:
(select case e.status when 1 then 'REJ' when 2 then 'PCON' when 3 then 'CON' when 4 then 'RFR' else 'OPN' end
from ABM_data_group_evidence as e
join ABM_form_group as d2 on e.ABM_form_group_id = d2.id
join ABM_group as d on d2.ABM_group_id = d.id
join ABM_evidence on e.ABM_evidence_id = ABM_evidence.id
join ABM_link_form_group as lnk on (ABM_evidence.id = lnk.ABM_evidence_id and d2.id = lnk.ABM_form_group_id)
join ABM_form_requirement as frm_req on lnk.ABM_form_requirement_id = frm_req.id
where d2.id = frm_grp.id and frm_req.id = 335) as 'Question 1'as you can see, I need to jump through several tables to get to the data that I require for the column.
This starts to become very slow when I am required to have 200 of these with different "frm_req" id numbers.

I am sure there is an easier way (maybe by taking the joins out of the sub-query and putting it into the main query?)
can someone advise me on a way to improve this?

Kind Regards,
Grant