I have below query which is having high Sub-tree cost. I have created appropriate Indexes for for the query which are getting seek but, still the sub-tree cost in high.
however, I can see nested Loop (Inner Join) operator have 92% cost in the execution plan.
Can someone suggest any query level optimisation which will help in reducing the sub-tree cost of the query?
SELECT EVT_ID, ENC_ID, PROH_ID FROM TRN_PROCEDURE_ORDER, TRN_ENCOUNTERS,TRN_EVENT_LOG WHERE PROH_STATUS IN ('C','R') AND ((PROH_RESULT_ENC_ID IS NULL AND PROH_ENC_ID = ENC_ID) OR PROH_RESULT_ENC_ID = ENC_ID) AND ENC_BOOL_DELETED = 0 AND EVT_ENC_ID = ENC_ID AND EVT_TYPE = 2 AND EVT_BOOL_ACTION2 = 0 AND ENC_APPT_LOCATION='AS' AND EVT_TIMESTAMP > '2022-04-08 02:19:03' ORDER BY ENC_ID DESC,PROH_ID DESC
This is the equivalent query with aliases and ANSI-92 style joins:
SELECT C.EVT_ID, A.ENC_ID, PROH_ID FROM TRN_ENCOUNTERS A INNER JOIN TRN_PROCEDURE_ORDER B ON ((B.PROH_RESULT_ENC_ID IS NULL AND B.PROH_ENC_ID = A.ENC_ID) OR B.PROH_RESULT_ENC_ID = A.ENC_ID) INNER JOIN TRN_EVENT_LOG C ON C.EVT_ENC_ID = A.ENC_ID WHERE B.PROH_STATUS IN ('C','R') AND A.ENC_BOOL_DELETED = 0 AND C.EVT_ENC_ID = ENC_ID AND C.EVT_TYPE = 2 AND C.EVT_BOOL_ACTION2 = 0 AND A.ENC_APPT_LOCATION='AS' AND C.EVT_TIMESTAMP > '2022-04-07 02:19:03'
Also, find actual execution plan in below URL for your reference https://www.brentozar.com/pastetheplan/?id=rydro3uAq