2

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?

4
  • 3
    Adding a "slow" execution plan would be helpful Nov 9, 2018 at 8:02
  • Does id in cte_table is unique? If not - distinct it (maybe using second CTE). Than move this condition from WHERE clause to FROM clause in a form of FROM tables JOIN more tables INNER JOIN cte_table ON sometable.id = cte_table.id.
    – Akina
    Nov 9, 2018 at 9:09
  • @DenisRubashkin Didn't know about the execution plans, analyzing that atm. There's a clustered index scan that apparently takes 73% of the total cost, not sure why it does that only when I combine CTE and main query though.@Akina The IDs are unique and that part doesn't create too much cost I believe, with my current test scenario it only gives out some 5 values.
    – Infrisios
    Nov 9, 2018 at 9:27
  • Basically, it has to check cursively for each row. Why not try am exists or possibly better, an Outer Join?
    – clifton_h
    Nov 9, 2018 at 13:34

1 Answer 1

1

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.

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.