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
Before we would want to begin tuning your SQL, we would want to confirm how your Tempo report is utilizing paging - are you returning say 10 or 15 rows, or trying to return all rows in the DB, etc?
Yeah I think it would be helpful to know your use full case - what data are you displaying and how are you showing it? Often in cases like this there are a lot of possible ways to fix the issue, and many of the possible fixes don't even involve database tuning at all.