1

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.

2
  • 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.
    – sqL_handLe
    Aug 17, 2022 at 11:28
  • 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. Aug 17, 2022 at 13:31

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.