CODEWITHSUNDEEP
sqlsql-serverperformance
Ram
Ram

Reputation: 805

Simplifying the SQL Server query

Below query is taking a lot of time to return results in two different databases. Is there any way to simplify this query?

WITH tblParent AS 
(
    SELECT * 
    FROM REFERENCES 
    WHERE referenced_id = 208593

    UNION ALL

    SELECT REFERENCES.* 
    FROM REFERENCES
    JOIN tblParent ON REFERENCES.referenced_id = tblParent.entity_Id
)
SELECT DISTINCT(entity_Id)
FROM tblParent
WHERE entity_Id <> 208593 AND field_type = 'ChildField'
OPTION(MAXRECURSION 5)

Upvotes: 1

Views: 138

Answers (2)

LukStorms
LukStorms

Reputation: 29647

Since you're only interested in the child entityId's, then just select the fields you need in the recursive CTE. 

WITH tblParent AS (
    SELECT entity_Id, referenced_id as baseId
    FROM [REFERENCES]
    WHERE referenced_id = 208593

    UNION ALL

    SELECT t.entity_Id, cte.baseId
    FROM tblParent cte
    JOIN [REFERENCES] t 
      ON (t.referenced_id = cte.entity_Id 
          AND t.entity_Id <> cte.baseId -- to avoid a circular reference
          )
    WHERE t.field_type = 'ChildField'
)
SELECT DISTINCT entity_Id
FROM tblParent
WHERE entity_Id <> baseId 
OPTION(MAXRECURSION 5)

And you might want to double-check if there's an index on referenced_id.

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44316

This should simplify it:

WITH tblParent AS 
(
    SELECT entity_Id, 0 c
    FROM [REFERENCES]
    WHERE referenced_id = 208593
    UNION ALL
    SELECT r.entity_Id, 1
    FROM [REFERENCES] r
    JOIN tblParent 
    ON r.referenced_id = tblParent.entity_Id
)
SELECT DISTINCT entity_Id
FROM tblParent
WHERE c = 1
OPTION(MAXRECURSION 5)

By checking the c value, it becomes apparent that it is a child value. I am assuming that this text has field_type = 'ChildField' for all childs.

(REFERENCES is a reserved word and DISTINCT is not a function)

Upvotes: 1

Related Questions