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
This has been fixed. The issue was not with the sql but with the query rule that was calling the view. Slight changes were made to the view, however.
If at all possible, I would still consider doing something about all those sub-queries. In many cases they wind up in a place where they can't be optimized by the DB engine. See if there's any way to convert even one of the sub-queries into a simple join it may result in huge time savings, especially if you can index some of the tables involved.