Hi
I am having issues with the following sql running too slow so that the Tempo report using it fails.
CREATE OR REPLACE VIEW TCAP_PROPOSAL_RECORD_VW ASSELECT DISTINCT S.SECURITY_ROLE AS SECURITY_ROLE ,Z.ID AS ID ,Z.COLLEGE_CL_CODE AS COLLEGE_CL_CODE ,Z.COLLEGE_CL_DESC AS COLLEGE_CL_DESC ,Z.TYPE_OF_CHANGE AS TYPE_OF_CHANGE ,Z.LEVEL_OF_CHANGE AS LEVEL_OF_CHANGE ,Z.PROPOSAL_TITLE AS PROPOSAL_TITLE ,Z.PROPOSAL_FOR AS PROPOSAL_FOR ,Z.ACADEMIC_FAN AS ACADEMIC_FAN ,Z.ACADEMIC_NAME AS ACADEMIC_NAME ,Z.INITIALISED_BY AS INITIALISED_BY ,Z.INITIALISED_BY_NAME AS INITIALISED_BY_NAME ,Z.STATUS AS STATUS ,Z.FOLDER_ID AS FOLDER_ID ,Z.COURSE_TOPIC_CODES AS COURSE_TOPIC_CODES ,Z.UNRESOLVED_COMMENTS AS UNRESOLVED_COMMENTS ,Z.NOTIFICATION_DATE AS NOTIFICATION_DATE ,Z.RESPONSES_RECEIVED AS RESPONSES_RECEIVEDFROM ( ( SELECT P.id AS ID ,P.collegeclcode AS COLLEGE_CL_CODE ,P.collegecldesc AS COLLEGE_CL_DESC ,P.typeofchange AS TYPE_OF_CHANGE ,P.levelofchange AS LEVEL_OF_CHANGE ,P.proposaltitle AS PROPOSAL_TITLE ,P.proposalfor AS PROPOSAL_FOR ,P.academicfan AS ACADEMIC_FAN ,P.academicname AS ACADEMIC_NAME ,P.initialisedby AS INITIALISED_BY ,P.initialisedbyname AS INITIALISED_BY_NAME ,P.STATUS AS STATUS ,P.folderid AS FOLDER_ID ,coalesce(group_concat(DISTINCT ct.coursetopiccode separator ';'), '') AS COURSE_TOPIC_CODES ,c.resolved AS UNRESOLVED_COMMENTS ,n.notificationDate AS NOTIFICATION_DATE ,P.responsesreceived AS RESPONSES_RECEIVED FROM ( ( ( Appian.TCAP_PROPOSAL P LEFT JOIN Appian.TCAP_COMMENT c ON ( P.id = c.proposalid AND c.type = 'Comment' AND c.resolved = 0 ) ) LEFT JOIN Appian.TCAP_COURSE_TOPIC ct ON (P.id = ct.proposalid) ) LEFT JOIN ( SELECT Appian.TCAP_NOTIFICATION.proposalid AS proposalid ,max(Appian.TCAP_NOTIFICATION.notificationdate) AS notificationDate FROM Appian.TCAP_NOTIFICATION GROUP BY Appian.TCAP_NOTIFICATION.proposalid ) n ON (P.id = n.proposalid) ) WHERE P.STATUS <> 'Initialised' GROUP BY P.id ) Z JOIN Appian.TCAP_PROPOSAL_SECURITY_VW S )WHERE Z.ID = S.PROPOSAL_ID
Could someone please suggest how it may be rewritten to respond quicker?
Thanks
Irene
Discussion posts and replies are publicly visible