Оптимизация темпоральной таблицы с помощью CTE

Производительность может снизиться, поскольку доступ к кластеризованному индексу таблицы dbo.Design осуществляется внутри вложенного цикла. Согласно оценке стоимости, база данных тратит 66% своего времени на сканирование этого индекса. Зацикливание на этом только усугубляет ситуацию.

См. связанный вопрос

Рассмотрите возможность изменения индекса dbo.Design на некластеризованный или попробуйте создать другую временную таблицу с некластеризованным индексом и использовать ее для своего рекурсивного запроса:

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];

Ваш вопрос неполный, «запрос медленный», но какая часть запроса медленная?

CTEQuery or LEFT JOIN in really big query

Я думаю, что требуется сценарий большого запроса, а также детали, например, какая таблица содержит, сколько строк, их тип данных и т. д.

Киньте подробнее про большой запрос.

Также дайте нам знать, если какой-либо UDF участвует в условии соединения.

Зачем вам left join временная таблица? ПОЧЕМУ НЕ INNER JOIN

Тестируйте производительность отдельно или CTE и Big Query.

Один раз используйте [D].[ParentDesignKey] is not null в рекурсивной части,

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

ПРИМЕЧАНИЕ. В CTE используйте только те столбцы, которые требуются.

Если это возможно Pre- Calculate [Lvl], потому что Recursive CTE производительность особенно плохая, связанная с большим количеством записей.

Сколько строк в среднем будет обрабатываться в каждом CTE Query?

Если временная таблица будет содержать более 100 rows, тогда да, создайте для нее кластеризованный индекс,

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

Если вы не используете [DesignLevel] в условии соединения, удалите его из индекса.

Также, Показать индекс таблицы [dbo].[Design] и несколько данных DesignKey и ParentDesignKey.

Есть несколько причин для получения Index Scan, одна из них Selectivity of Key.

Итак, сколько строк может быть у одного DesignKey, а сколько строк у одного ParentDesignKey?

Таким образом, в зависимости от приведенного выше ответа Create Composite Clustered Index для обоих ключей таблицы [dbo].[Design]

Поэтому считайте, что мой ответ неполный, я обновлю его соответствующим образом.

Согласно результатам тестирования, опубликованным в этой статье, loop может дать вам улучшение производительности по сравнению с рекурсивным CTE.

DECLARE @DesignLevel int = 0;

INSERT INTO [#DesignLvl]
SELECT [DesignKey], 1
FROM [RCTE];

WHILE @@ROWCOUNT > 0
BEGIN
    SET @DesignLevel += 1;

    INSERT INTO [#DesignLvl]
    SELECT [D].[DesignKey], dl.DesignLevel
    FROM [dbo].[Design] AS [D]
    JOIN [#DesignLvl] AS [dl] ON [dl].[DesignKey] = [D].[ParentDesignKey]
    WHERE dl.DesignLevel = @DesignLevel;
END;

попробуйте @table, вы запрашиваете временную таблицу памяти вместо временной таблицы диска

declare @DesignLvl table
(
    [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]

может немного помочь, о скольких строках идет речь и какая версия сервера sql? @@версия?

Как уже говорили другие, не совсем понятно, какая часть вашего запроса медленная. Мы также не имеем ни малейшего представления о количестве записей (может быть 100, может быть 100 миллионов) или фактическом времени (вы можете считать 10 секунд медленной загрузкой миллионов строк?!?).

Мы также не знаем, насколько "тяжел" ваш really big query; насколько мы знаем, без LEFT OUTER JOIN тоже может быть медленно.

В любом случае, чтобы лучше понять, что произойдет, если вы запустите это: (непроверенный код, вам может потребоваться исправить некоторые вещи)

DECLARE @level int = 0,
        @rowcount int

-- create working table to calculate levels
SELECT lvl = @level,
       D.[DesignKey]
  INTO #hierarchy
  FROM [Design] D
 WHERE D.[ParentDesignKey] IS NULL

SELECT @rowcount = @@ROWCOUNT

PRINT Convert(nvarchar, CURRENT_TIMESTAMP, 113) + ' - Loaded ' + Convert(nvarchar, @rowcount) + N' level ' + Convert(nvarchar, @rowcount) + ' records...'

CREATE UNIQUE CLUSTERED INDEX uq0 ON #hierarchy (lvl, [DesignKey])

WHILE @rowcount > 0
    BEGIN

        INSERT #hierarchy        
        SELECT lvl = @level + 1,
               D.[DesignKey]
          FROM #hierarchy t
          JOIN [Design] D
            ON D.[ParentDesignKey] = t.[DesignKey]
         WHERE t.lvl = @level

        SELECT @rowcount = @@ROWCOUNT,
               @level = @level + 1

        PRINT Convert(nvarchar, CURRENT_TIMESTAMP, 113) + ' - Loaded ' + Convert(nvarchar, @rowcount) + N' level ' + Convert(nvarchar, @rowcount) + ' records...'
    END

GO

-- we now have a lvl value for each DesignKey but the index is backwards for future use; so add index in the other direction
PRINT Convert(nvarchar, CURRENT_TIMESTAMP, 113) + ' - re-indexing...'
CREATE UNIQUE INDEX uq1 ON #hiearchy ([DesignKey]) INCLUDE (lvl) WITH (FILLFACTOR = 100)

PRINT Convert(nvarchar, CURRENT_TIMESTAMP, 113) + ' - done.'

GO

PRINT Convert(nvarchar, CURRENT_TIMESTAMP, 113) + ' - Starting query...'

-- actual use:
;WITH DesignLvlCTE
   AS (SELECT h.lvl, D.*
         FROM [Design] D
         JOIN #hierarchy h
           ON h.[DesignKey] = D.[DesignKey])
SELECT... 
 INTO #result -- leave this in to exclude overhead time of client
FROM.. 
LEFT JOIN DesignLvlCTE AS [dl] ON d.DesignKey = dl.DesignKey
WHERE ...

PRINT Convert(nvarchar, CURRENT_TIMESTAMP, 113) + ' - Done fetching data.'

-- get results
SELECT * FROM #result

PRINT Convert(nvarchar, CURRENT_TIMESTAMP, 113) + ' - Done.'

-- DROP TABLE #result

Пробовали ли вы таблицы, оптимизированные для памяти? Я использовал их в аналогичном процессе (рекурсивный CTE) и получил впечатляющие результаты. В SQL Server 2017 также должен быть включен в Standard Edition. Сначала вам нужно создать файловую группу для данных, оптимизированных для памяти:

ALTER DATABASE MyDB 
ADD FILEGROUP mem_data CONTAINS MEMORY_OPTIMIZED_DATA; 
GO 
ALTER DATABASE MyDB 
ADD FILE (NAME = 'MemData', FILENAME = 'D:\Data\MyDB_MemData.ndf') TO FILEGROUP mem_data; 

Затем вы создаете (или конвертируете) свою таблицу:

CREATETABLE dbo.MemoryTable
(
Col1 INT IDENTITY PRIMARY KEY
...
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Пробовали ли вы изменить SELECT * только на SELECT DesignLevel, я обнаружил, что для широких строк этого было достаточно, чтобы изменить план выполнения, чтобы выбрать использование нетерпеливой очереди для сканирования индекса:

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

    UNION ALL

    SELECT
        [D].[DesignKey],
        [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]

План и тест SQL можно найти здесь: https://www.brentozar.com/pastetheplan/?id=BymxTD4wV

Проблема могла заключаться в том, что таблица Design была огромной, и объединение ее с самой собой без каких-либо основных условий фильтрации приводило к сканированию всей таблицы.

Поскольку вас интересует только очень небольшое число столбцов, таких как designkey и parentdesignkey, попробуйте разбить запрос заполнения данных (вставьте в #designlvl) на несколько частей.

Убедитесь, что у вас есть индекс (designkey,parentdesignkey)

INSERT INTO #DesignLevel
SELECT DISTINCT DesignKey, 1 FROM Design WHERE ParentDesignKey IS NULL

INSERT INTO #DesignLevel
SELECT DISTINCT ParentDesignKey, Lvl+1 FROM Design WHERE ParentDesignKey is NOT NULL

Убедитесь, что в столбцах DesignKey.ParentDesignKey и #DesignLv1.DesignKey нет пустых значений, и если это так, используйте ограничение not null там, где это возможно. я видел нули для создания перекрестных соединений.

Если таблица Design — это транзакционная таблица, в которую очень часто записываются данные, часто перестраивайте индексы для этой таблицы.

Создайте один некластеризованный индекс для Design.DesignKey и Design.ParentDesignKey в этой последовательности.

Создайте некластеризованный индекс для #DesignLvl DesignKey.

Если таблица Design большая (> 10 миллионов строк) и содержит целый набор столбцов, создайте индексированное представление отдельных столбцов, которые вам нужны только для этого запроса, и используйте его.

Проверьте журнал системных событий на наличие ошибок чтения и записи на диске с базой данных tempdb и (базу tempdb следует поместить в массив RAID 1 или RAID 10, поскольку они оптимизированы для приложений с большим объемом операций записи.) from ( https://searchsqlserver.techtarget.com/tip/SQL-Server-tempdb-best-practices-increase-performance )