Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Posts
    84

    Question Unanswered: How to improve query performance

    Dear All,

    I have a complicated SQL query as below to be fine tuned. It needs to join some db instances, while it takes too long to get the result. Any hint on how to improve the performance, or any free analysis tool that we could turn to? Thank you.

    -------------------------------------------------------------
    SELECT * FROM (
    (
    SELECT
    STMT_DATE, ADM_CORP_no, STMT_S_TYPE, STMT_TITLE, STMT_DESC, STMT_PDF, STMT_PAGES, STMT_PART_RATING, STMT_PART_NAME_1, STMT_PDF_SIZE, STMT_ID, MAINT_group_NAME, 0 AS DB_SOURCE, STMT_PART_no_1
    FROM ACOM.T_STMTkrpt
    INNER JOIN ACOM.T_ADM_corp kcorp ON krpt.STMT_corp=kcorp.ADM_CORP_ID
    INNER JOIN ACOM.T_ADM_group kgroup ON kgroup.MAINT_group_ID= krpt.STMT_group_ID
    WHERE (STMT_S_TYPE = '7' OR STMT_S_TYPE = '6') AND STMT_STATUS = 'A'
    ORDER BY STMT_DATE DESC LIMIT 0, 1
    )

    UNION

    (
    SELECT * FROM (
    SELECT
    srpt.created_on AS STMT_DATE, kcorp.ADM_CORP_no AS ADM_CORP_no, krpttype.STMT_type_id AS STMT_S_TYPE, spart.title AS STMT_TITLE, spart.description AS STMT_DESC, srpt.pdf_file AS STMT_PDF, srpt.pdf_pages AS STMT_PAGES, srate.description AS STMT_PART_RATING, kPART.maint_PART_name AS STMT_PART_NAME_1, srpt.pdf_size AS STMT_PDF_SIZE, srpt.id AS STMT_ID, kgroup.maint_group_name AS MAINT_group_NAME, 1 AS DB_SOURCE, kPART.maint_PART_no AS STMT_PART_no_1
    FROM dbinst2.tbl_part_rpt spart
    INNER JOIN dbinst2.tbl_rpt srpt ON srpt.id = spart.STMT_id AND srpt.STMT_status = 'A'
    LEFT JOIN dbinst2.tbl_rating_type srate ON srate.id = spart.cur_rating_type_id
    INNER JOIN ACOM.T_ADM_corp kcorp ON kcorp.ADM_CORP_name = 'abc'
    INNER JOIN ACOM.T_STMT_type krpttype ON krpttype.STMT_type_name = 'PARTs'
    INNER JOIN ACOM.T_ADM_PART kPART ON kPART.maint_PART_no2 = spart.part_cde
    INNER JOIN ACOM.T_ADM_group kgroup ON kgroup.maint_group_id = spart.group_id
    WHERE spart.part_type_id = (
    SELECT id FROM dbinst2.tbl_PART_type WHERE description LIKE '%aabb%'
    )
    ) AS tbl
    WHERE STMT_PART_no_1 NOT IN (
    SELECT DISTINCT STMT_PART_no_1
    FROM ACOM.T_STMTkrpt
    INNER JOIN ACOM.T_user kuser ON kuser.user_id = krpt.STMT_user_id AND kuser.user_team = 'IS'
    WHERE krpt.STMT_status = 'A' AND krpt.STMT_corp = '4' AND (krpt.STMT_stmt_type = '12)
    )
    ORDER BY STMT_DATE DESC LIMIT 0, 1
    ) ORDER BY STMT_DATE DESC LIMIT 0, 1
    ) AS tbl1

    UNION

    SELECT * FROM (
    (
    SELECT
    STMT_DATE, ADM_CORP_no, STMT_S_TYPE, STMT_TITLE, STMT_DESC, STMT_PDF, STMT_PAGES, STMT_PART_RATING, STMT_PART_NAME_1, STMT_PDF_SIZE, STMT_ID, MAINT_group_NAME, 0 AS DB_SOURCE, STMT_PART_no_1
    FROM ACOM.T_STMTkrpt
    INNER JOIN ACOM.T_ADM_corp kcorp ON krpt.STMT_corp=kcorp.ADM_CORP_ID
    INNER JOIN ACOM.T_ADM_group kgroup ON kgroup.MAINT_group_ID= krpt.STMT_group_ID
    WHERE (STMT_S_TYPE = '7' OR STMT_S_TYPE = '6') AND STMT_STATUS = 'A'
    )

    UNION

    (
    SELECT * FROM (
    SELECT
    srpt.created_on AS STMT_DATE, kcorp.ADM_CORP_no AS ADM_CORP_no, krpttype.STMT_type_id AS STMT_S_TYPE, spart.title AS STMT_TITLE, spart.description AS STMT_DESC, srpt.pdf_file AS STMT_PDF, srpt.pdf_pages AS STMT_PAGES, srate.description AS STMT_PART_RATING, kPART.maint_PART_name AS STMT_PART_NAME_1, srpt.pdf_size AS STMT_PDF_SIZE, srpt.id AS STMT_ID, kgroup.maint_group_name AS MAINT_group_NAME, 1 AS DB_SOURCE, kPART.maint_PART_no AS STMT_PART_no_1
    FROM dbinst2.tbl_part_rpt spart
    INNER JOIN dbinst2.tbl_rpt srpt ON srpt.id = spart.STMT_id AND srpt.STMT_status = 'A'
    LEFT JOIN dbinst2.tbl_rating_type srate ON srate.id = spart.cur_rating_type_id
    INNER JOIN ACOM.T_ADM_corp kcorp ON kcorp.ADM_CORP_name = 'abc'
    INNER JOIN ACOM.T_STMT_type krpttype ON krpttype.STMT_type_name = 'PARTs'
    INNER JOIN ACOM.T_ADM_PART kPART ON kPART.maint_PART_no2 = spart.part_cde
    INNER JOIN ACOM.T_ADM_group kgroup ON kgroup.maint_group_id = spart.group_id
    WHERE spart.part_type_id = (
    SELECT id FROM dbinst2.tbl_PART_type WHERE description LIKE '%aabb%'
    )
    ) AS tbl0
    WHERE STMT_PART_no_1 NOT IN (
    SELECT DISTINCT STMT_PART_no_1
    FROM ACOM.T_STMTkrpt INNER JOIN ACOM.T_user kuser ON kuser.user_id = krpt.STMT_user_id AND kuser.user_team = 'IS'
    WHERE krpt.STMT_status = 'A' AND krpt.STMT_corp = '4' AND (krpt.STMT_stmt_type = '7' OR krpt.STMT_stmt_type = '8')
    )
    ) ORDER BY STMT_DATE DESC LIMIT 1,15
    ) AS tbl2
    -------------------------------------------------------------

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by qxz View Post
    Any hint on how to improve the performance, or any free analysis tool that we could turn to?
    hint: make sure you have indexes on the appropriate columns

    free tool: EXPLAIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Also set your expectation as to what is an acceptable amount of time for returning this type of result set.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Dec 2011
    Posts
    1

    How to improve query performance

    you can replace these complex queries with database views either if you can not /can fine tune them individually which would drastically improve the turnaround time of the query execution.

Posting Permissions

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