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
-------------------------------------------------------------

hint: make sure you have indexes on the appropriate columns

free tool: EXPLAIN

Also set your expectation as to what is an acceptable amount of time for returning this type of result set.

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.