Unanswered: help improving 'join' efficiency in sub-querys
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:
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 = 10
this 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:
(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?
In most cases JOINs are faster than sub-queries and it is very rare for a sub-query to be faster.
In JOINs RDBMS can create an execution plan that is better for your query and can predict what data should be loaded to be processed and save time, unlike the sub-query where it will run all the queries and load all their data to do the processing.
The good thing in sub-queries is that they are more readable than JOINs: that's why most new SQL people prefer them; it is the easy way; but when it comes to performance, JOINS are better in most cases even though they are not hard to read too.
how about the entire query? Also, for readability and ease of maintenance, when using explicit join syntax, you should keep all the conditions for a table, within that table's ON clause. Also, if such(some predicates in ON clause and others in WHERE clause) is done for a LEFT OUTER join, then your results will be different and performance affected.