Рекурсия с использованием виртуальных представлений
Одним из основных преимуществ виртуальных представлений (CTE), является простое и наглядное построение рекурсивных выражений.
Достаточно часто встречаются таблицы с иерархической структурой данных ("деревья"). В случае, когда необходимо получить уровень вложенности элемента, лучшим решением будет использование рекурсивного запроса с использованием виртуального представления. Допустим, в магазине, использующем демонстрационную базу данных, решили добавить возможность задавать "степень родства" фирм, для определения дочерних компаний известных брендов.
Для таблицы Brands нужно создать виртуальное представление, использующее рекурсию для получения уровня вложенности: WITH C (BrandID, [Name], ParentID, NestingLevel) AS
( SELECT B.BrandID, B.[Name], B.ParentID, 1 FROM Brands AS B WHERE ParentID = 0 UNION ALL
SELECT B.BrandID, B.[Name], B.ParentID, (NestingLevel + 1) FROM Brands AS B INNER JOIN C ON C.BrandID = B.ParentID )
SELECT * FROM C
Результатом выполнения запроса будет таблица, например такая: BrandID Name ParentID NestingLevel -------------------------------------------------- 1 Parent1 0 1 2 Parent2 0 1 3 Child1 1 2 4 Child11 3 3 5 Child12 3 3
Общий принцип построения рекурсивного выражения WITH ИмяCTE (Определение) AS
( SELECT … -- Выборка с начальным условием, UNION ALL -– Объединение результатов SELECT … -- Выборка определяющаяя шаг рекурсии INNER JOIN CTE.ДочернийID = ИмяТаблицы.РодительскийID –- Присоединение "по родителю" )
Без использования виртуального представления, для достижения того же результата придется написать значительно более сложный запрос: DECLARE @CurrentID int DECLARE @Level int SELECT TOP(1) @CurrentID = BrandID FROM Brands ORDER BY BrandID
DECLARE @StackTable TABLE (ID int, Level int) DECLARE @OutputTable TABLE (ID int, [Name] nvarchar(32), ParentID int, Level int) INSERT INTO @StackTable VALUES(@CurrentID, 1) SET @Level = 1
WHILE @Level > 0 BEGIN
IF EXISTS (SELECT * FROM @StackTable WHERE Level = @Level) BEGIN
SELECT @CurrentID = ID FROM @StackTable WHERE Level = @Level INSERT INTO @OutputTable SELECT BrandID, [Name], ParentID, @Level AS Level FROM Brands WHERE BrandID = @CurrentID DELETE FROM @StackTable WHERE Level = @Level AND ID = @CurrentID
INSERT @StackTable SELECT BrandID, @Level + 1 FROM Brands WHERE ParentID = @CurrentID
IF @@ROWCOUNT > 0 SET @Level = @Level + 1 END
ELSE
SET @Level = @Level - 1 END
SELECT * FROM @OutputTable ORDER BY ID
Как видит читатель, код без использования виртуальных представлений более громоздкий и сложный для понимания. Несмотря на то, что подобный код несложно написать, весьма просто допустить досадную ошибку, на поиски которой может уйти много времени.
Кроме того, рекурсия с использованием виртуального представления значительно превосходит в производительности подход с использованием временных таблиц. Так, для тестового набора из ста тысяч записей в таблице Brands, время выполнения кода с использованием виртуального представления оказалось в четыре раза меньше, чем время выполнения кода без использования оного. Примерно то же отношение получено и для меньших наборов данных.
