Mahmoud Moravej Mahmoud Moravej - 1 month ago 10
SQL Question

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

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

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.

enter image description here

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.