Query optimisation to remove nested loop

Question

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?

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

Thank you.

• sqL_handLe: it may not be the nested loop you want to focus on in the plan (which had 0 actual rows) rather the preceding spool which was estimated at ~86000 rows but read ~43 million rows.
• Charlieface: For query performance questions, we need to see the full table and index schemas of the relevant tables. Adding table aliases and referencing those aliases on each column, as well as using explicit join syntax rather than the old comma-joins, would help immensely in understanding your query.

Answer 1

As mentioned, the loop join itself is not likely the issue, rather it's the giant table spool, which is expecting a small amount of distinct rows. A lazy table spool is usually an indication of poor indexing.

Firstly, rewrite your query to use explicit joins, and use table aliases everywhere, and use ISNULL rather than that OR.

I've had to guess which columns are on which table, and this answer is based on that.

SELECT
 evt.EVT_ID,
 enc.ENC_ID,
 proh.PROH_ID
FROM
 TRN_PROCEDURE_ORDER proh
 JOIN TRN_ENCOUNTERS enc ON ISNULL(proh.PROH_RESULT_ENC_ID, proh.PROH_ENC_ID) = enc.ENC_ID
 JOIN TRN_EVENT_LOG evt ON evt.EVT_ENC_ID = enc.ENC_ID
WHERE proh.PROH_STATUS IN ('C', 'R')
 AND enc.ENC_BOOL_DELETED = 0 
 AND evt.EVT_TYPE = 2
 AND evt.EVT_BOOL_ACTION2 = 0 
 AND enc.ENC_APPT_LOCATION = 'AS'
 AND evt.EVT_TIMESTAMP > '2022-04-08 02:19:03' 
ORDER BY
 enc.ENC_ID DESC,
 proh.PROH_ID DESC;

To make this performant, you are first going to need a computed column for the ISNULL

ALTER TABLE TRN_PROCEDURE_ORDER
 ADD Final_ENC_ID AS ISNULL(PROH_RESULT_ENC_ID, PROH_ENC_ID)

Then you need the following filtered indexes

CREATE INDEX IX ON TRN_PROCEDURE_ORDER (
 Final_ENC_ID,
 PROH_ID
)
WHERE (
 PROH_STATUS IN ('C', 'R')
)

CREATE INDEX IX ON TRN_ENCOUNTERS (
 ENC_ID
)
WHERE (
 ENC_BOOL_DELETED = 0
 AND ENC_APPT_LOCATION = 'AS'
)

CREATE INDEX IX ON TRN_EVENT_LOG (
 EVT_TIMESTAMP,
 EVT_ENC_ID
)
WHERE (
 EVT_TYPE = 2
 AND EVT_BOOL_ACTION2 = 0
)

At this point, I would imagine a merge join or an indexed nested loop may be the best. Please share the latest query plan.

An alternative to the computed column is to use a UNION ALL instead of the OR, and then have separate filtered indexes WHERE PROH_RESULT_ENC_ID IS NULL and WHERE PROH_RESULT_ENC_ID IS NOT NULL.