Performance: CTE in an “IN”-Operator

Posted on

Question :

I’ve got a query with a CTE (getting all children of a parent) followed by a main statement using the “IN”-Operator to filter for the CTE’s output.

Roughly looks like this:

WITH cte_table AS (...)

SELECT (stuff)

FROM tables JOIN more tables

WHERE ID IN

(SELECT ID FROM cte_table)

The CTE runs fast by itself. The main query runs fast if fed with the CTE’s output manually. Fast means that it takes no noticeable time. But when I combine both it takes almost 20 seconds.

Any ideas for solutions? Or, what is probably more important to me, the reason why it could take that long?

Answer :

You could try refactoring the query to use equivalent, but different, syntax.

IN is more-or-less an inner join. So your query could become

...
FROM cte_table JOIN tables JOIN more tables
...

For your query a row has to be in the CTE output to be returned. The same applies to this inner join syntax. The two queries are equivalent.

Although you and I know these two are interchangeable the query optimizer doesn’t always realize this. So by giving it a different place to start from you can (sometimes) end up with a different plan.

Really need to see the table definitions and the “slow” plan for a proper answer, though.

Leave a Reply

Your email address will not be published.