performance penalty when using "join with temp table " in contrast of "IN clause with constant values"

Question

I have a temp table with two records like this:

select * into #Tbl from (select 1 id union select 2) tbl

and also the related index:

Create nonclustered index IX_1 on #T(id)

The following query takes 4000ms to run:

SELECT   AncestorId
FROM     myView 
WHERE    AncestorId =ANY(select id from #t)

But the equivalent query (with IN and literal values) takes only 3ms to run!:

SELECT  ProjectStructureId
FROM    myView
WHERE   AncestorId in (1,2)

Why this huge difference and how can I change the first query to be as fast as the second one?

P.S.

1. SQL SERVER 2014 SP2
2. myView is a Recursive CTE
3. Changing the first query to INNER JOIN model or EXISTS model didn't help
4. Changing the IX_1 Index to a cluster index didn't help
5. Using FORSEEK didn't help

P.S.2

The execution plans of both can be downloaded here : https://www.dropbox.com/s/pas1ovyamqojhba/Query-With-In.sqlplan?dl=0

Execution plans in Paste the Plan

P.S. 3

The view definition is :

ALTER VIEW [dbo].[myView] 
AS
WITH parents AS (SELECT        main.Id, main.NodeTypeCode, main.ParentProjectStructureId AS DirectParentId, parentInfo.Id AS AncestorId, parentInfo.ParentProjectStructureId AS AncestorParentId, CASE WHEN main.NodeTypeCode <> IsNull(parentInfo.NodeTypeCode, 0) 
                                                   THEN 1 ELSE 0 END AS AncestorTypeDiffLevel
                          FROM            dbo.ProjectStructures AS main LEFT OUTER JOIN
                                                   dbo.ProjectStructures AS parentInfo ON main.ParentProjectStructureId = parentInfo.Id
                          UNION ALL
                          SELECT        m.Id, m.NodeTypeCode, m.ParentProjectStructureId, parents.AncestorId, parents.AncestorParentId,
                                                   CASE WHEN m.NodeTypeCode <> parents.NodeTypeCode THEN AncestorTypeDiffLevel + 1 ELSE AncestorTypeDiffLevel END AS AncestorTypeDiffLevel

                          FROM            dbo.ProjectStructures AS m INNER JOIN
                                                   parents ON m.ParentProjectStructureId = parents.Id)
    SELECT          ISNULL(Id, - 1) AS ProjectStructureId, 
                    ISNULL(NodeTypeCode,-1) NodeTypeCode,
                    DirectParentId, 
                    ISNULL(AncestorId, - 1) AS AncestorId, 
                    AncestorParentId, 
                    AncestorTypeDiffLevel
    FROM            parents
    WHERE        (AncestorId IS NOT NULL)

Answer 1

In your good plan it is able to push the literal values right into the index seek of the anchor part of the recursive CTE.

It refuses to do that when they come from a table.

You could create a table type

CREATE TYPE IntegerSet AS TABLE 
( 
Integer int PRIMARY KEY WITH (IGNORE_DUP_KEY = ON)
);

And then pass that to an inline TVF written to use that in the anchor part directly.

Then just call it like

DECLARE @AncestorIds INTEGERSET;

INSERT INTO @AncestorIds
VALUES      (1),
            (2);

SELECT *
FROM   [dbo].[myFn](@AncestorIds); 

The inline TVF would be much the same as the view but with

 WHERE parentInfo.Id IN (SELECT Integer FROM @AncestorIds)

in the anchor part of the recursive CTE.

CREATE FUNCTION [dbo].[myFn]
(
@AncestorIds IntegerSet READONLY
)
RETURNS TABLE
AS
RETURN 
  WITH parents
       AS (SELECT  /*omitted for clarity*/
           WHERE parentInfo.Id IN (SELECT Integer FROM @AncestorIds)
           UNION ALL
           SELECT/* Rest omitted for clarity*/

Also you might as well change that LEFT JOIN to an INNER JOIN though the optimiser does that for you.