Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012

    Question Unanswered: help improving 'join' efficiency in sub-querys

    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:

    from ABM_form_group as frm_grp
    join ABM_form_domain as frm_dom on frm_grp.ABM_form_domain_id =
    join ABM_group as grp on frm_grp.ABM_group_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 =
          join ABM_group as d on d2.ABM_group_id =
          join ABM_evidence on e.ABM_evidence_id =
          join ABM_link_form_group as lnk on ( = lnk.ABM_evidence_id and = lnk.ABM_form_group_id)
          join ABM_form_requirement as frm_req on lnk.ABM_form_requirement_id =  
        where = and = 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,

  2. #2
    Join Date
    Sep 2016

    help improving 'join' efficiency in sub-querys

    Hii ,

    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.

    Thank You!!

  3. #3
    Join Date
    Sep 2002
    Montreal, Canada
    Provided Answers: 1
    Can you post the SHOW CREATE TABLE for all tables, an EXPLAIN for all queries, some sample data and expected result?

  4. #4
    Join Date
    Oct 2007
    Provided Answers: 9
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts