Optimize temporal table with CTE

Question

I create temporal table in order to set level :

CREATE TABLE [#DesignLvl]
(
    [DesignKey] INT,
    [DesignLevel] INT
);

WITH RCTE AS 
(
    SELECT
        *,
        1 AS [Lvl]
    FROM 
        [Design]
    WHERE 
        [ParentDesignKey] IS NULL

    UNION ALL

    SELECT
        [D].*,
        [Lvl] + 1 AS [Lvl]
    FROM 
        [dbo].[Design] AS [D]
    INNER JOIN 
        [RCTE] AS [rc] ON [rc].[DesignKey] = [D].[ParentDesignKey]
)
INSERT INTO [#DesignLvl]
    SELECT
        [DesignKey], [Lvl]
    FROM 
        [RCTE]

Once created, I used as LEFT JOIN in really big query as:

SELECT... 
FROM.. 
LEFT JOIN [#DesignLvl] AS [dl] ON d.DesignKey = dl.DesignKey
WHERE ...

The query works, but performance has fallen and the query now is too slow. Is there any way to optimize this table?

Execution plan of CTE

I try to add CLUSTERED index as:

CREATE TABLE [#DesignLvl]
(
    [DesignKey] INT,
    [DesignLevel] INT
);

CREATE CLUSTERED INDEX ix_DesignLvl 
    ON [#DesignLvl] ([DesignKey], [DesignLevel]);

Also try:

    CREATE TABLE [#DesignLvl] 
( [DesignKey] INT INDEX IX1 CLUSTERED ,
 [DesignLevel] INT INDEX IX2 NONCLUSTERED );

But I get same result, it took long to execute

Answer 1

The performance could be slower because a clustered index on the dbo.Design table is being accessed inside a nested loop. According to the cost estimate, the database is spending 66% of it's time scanning that index. Looping over that just makes it worse.

See related question

Consider changing the index on dbo.Design to be non-clustered, or try creating another temporary table with a non-clustered index and use that for your recursive query:

CREATE TABLE [#DesignTemp]
(
    ParentDesignKey INT,
    DesignKey INT
);

-- Insert the data, then create the index.
INSERT INTO [#DesignTemp]
SELECT
ParentDesignKey,
DesignKey
FROM [dbo].[Design];

COMMIT;

-- Try this index, or create indexes for individual columns if the plan works better at high volumes.
CREATE NONCLUSTERED INDEX ix_DesignTemp1 ON [#DesignTemp] (ParentDesignKey, DesignKey);

CREATE TABLE [#DesignLvl]
(
    [DesignKey] INT,
    [DesignLevel] INT
);

WITH RCTE AS 
(
    SELECT
        *,
        1 AS [Lvl]
    FROM 
        [DesignTemp]
    WHERE 
        [ParentDesignKey] IS NULL

    UNION ALL

    SELECT
        [D].*,
        [Lvl] + 1 AS [Lvl]
    FROM 
        [DesignTemp] AS [D]
    INNER JOIN 
        [RCTE] AS [rc] ON [rc].[DesignKey] = [D].[ParentDesignKey]
)
INSERT INTO [#DesignLvl]
    SELECT
        [DesignKey], [Lvl]
    FROM 
        [RCTE];

Answer 2

Your question is incomplete, "query is slow", but which part of query is slow ?

CTEQuery or LEFT JOIN in really big query

I think Script of big query is required, along with details, like which table contain how many rows, their data type etc.

Throw more details about the big query.

Also let us know if any UDF is involve in join condition.

Why do you left join Temp table ? WHY NOT INNER JOIN

Test the performance separately or CTE and Big Query.

Once use [D].[ParentDesignKey] is not null in recursive part,

SELECT
        [D].*,
        [Lvl] + 1 AS [Lvl]
    FROM 
        [dbo].[Design] AS [D]
    INNER JOIN 
        [RCTE] AS [rc] ON [rc].[DesignKey] = [D].[ParentDesignKey]
and [D].[ParentDesignKey] is not null

NOTE : In CTE use only those columns which is require.

If it is possible to Pre- Calculate [Lvl],because Recursive CTE performance is specially bad involving lot of Records.

How many rows will be process in each CTE Query on average ?

If temp table will hold more than 100 rows then yes create Clustered index on it,

  CREATE CLUSTERED INDEX ix_DesignLvl 
        ON [#DesignLvl] ([DesignKey], [DesignLevel]);

If you are not using [DesignLevel] in join condition then remove from index.

Also ,Reveal index of table [dbo].[Design] and few data of DesignKey and ParentDesignKey.

There are several reason for getting Index Scan, one of them is Selectivity of Key .

So one DesignKey can have how many rows and one ParentDesignKey can have how many rows ?

So depending upon above answer Create Composite Clustered Index on both key of table [dbo].[Design]

So consider my answer is incomplete, I will update it accordingly.