Группирование связанных элементов в T-SQL. Часть II
Содержание:
1. Часть I;
2. Часть II;
3. Часть III (Вы читаете данный раздел).
В Решении 2 реализован тот же алгоритм, что и в Решении 1, но вместо табличной переменной @T1TC используется временная таблица с именем #Т1ТС. Определяемые пользователем функции не поддерживают временные таблицы, поэтому необходимо реализовать решение в хранимой процедуре или как специальный пакет. Программный код 7 демонстрирует второй вариант.
Листинг 7. Второй вариант решения
Рисунок 5. План для внутреннего запроса 1 Решения 2
Время выполнения для Решения 2 значительно лучше, чем для Решения 1. Например, при одной группе со 100 строками Решение 2 завершается менее чем за секунду, в отличие от 40 секунд для Решения 1. На рисунках 5 и 6 приведено сравнение оценочного и действительного планов запроса для последнего выполнения внутренних запросов 1 и 2 цикла соответственно.
Рисунок 6. План для внутреннего запроса 2 Решения 2
Обратите внимание, насколько точнее оценки количества элементов на этот раз, а также на то обстоятельство, что оптимизатор выбрал хеш-соединения вместо оператора Loop Join как для соединений между временной таблицей и Т1, так и для оператора anti-semi join, используемого для обработки предиката NOT EXISTS.
Рисунок 7. Производительность Решения 2 относительно размера группы
Решение 2 действительно гораздо эффективнее, чем Решение 1, но ему свойственны недостатки производительности. Имея группу из N элементов, транзитивное закрытие такой группы имеет (1 + 2 + ... + N-1) х 2 = N∧2 — N ребер. Например, при наличии 10 элементов транзитивное закрытие имеет 90 ребер. При 100 элементах — 9900. При 1000 элементов — 999 000 ребер. Другими словами, количество ребер увеличивается экспоненциально в соответствии с количеством элементов. Это означает, что решение масштабируется слабо. На рисунке 7 показано время выполнения данного решения в секундах для одной группы с числом строк, увеличивающимся от 100 до 1000.
Поэтому, если вы не имеете дела с очень малым количеством групп. Решение 2 нельзя назвать удачным вариантом.
В Решении 2 сначала развернуты все возможные соединения между парами узлов в группе путем создания транзитивного закрытия, а затем оно свертывает их для вычисления идентификатора группы. Результатом становится экспоненциальное масштабирование. В Решении 3 подход с развертыванием-свертыванием не применяется. Вместо этого группы обрабатываются по одной, начиная с одной пары (TOP (1) для необработанной пары ORDER BY id1, id2), а затем развертываются соединения, подобно тому как развертывается иерархия «родитель-потомок» как в левом, так и в правом направлении. Значение id1 из первой пары группы используется как идентификатор группы. Решение записывает обнаруженные узлы с идентификатором группы (значение id1 из первой пары) во временной таблице с именем #G, используя предикат NOT EXISTS для проверки, добавляются ли только необработанные узлы. В коде 8 приведен полный программный код решения.
Код 8. Код решения 3
Решили отложить изучение данного материала до тех пор, пока не решите проблему со своим холодильником Electrolux, который перестал исполнять свою основную функцию? В этом случае обязательно загляните на holodservice.net.ua/electrolux. Здесь вы найдете опытных специалистов, которые в самые сжатые сроки вдохнут новую жизнь в ваш холодильник.
1. Часть I;
2. Часть II;
3.
Решение 2. Транзитивное закрытие с таблицей temp
В Решении 2 реализован тот же алгоритм, что и в Решении 1, но вместо табличной переменной @T1TC используется временная таблица с именем #Т1ТС. Определяемые пользователем функции не поддерживают временные таблицы, поэтому необходимо реализовать решение в хранимой процедуре или как специальный пакет. Программный код 7 демонстрирует второй вариант.
Листинг 7. Второй вариант решения
Рисунок 5. План для внутреннего запроса 1 Решения 2
Время выполнения для Решения 2 значительно лучше, чем для Решения 1. Например, при одной группе со 100 строками Решение 2 завершается менее чем за секунду, в отличие от 40 секунд для Решения 1. На рисунках 5 и 6 приведено сравнение оценочного и действительного планов запроса для последнего выполнения внутренних запросов 1 и 2 цикла соответственно.
Рисунок 6. План для внутреннего запроса 2 Решения 2
Обратите внимание, насколько точнее оценки количества элементов на этот раз, а также на то обстоятельство, что оптимизатор выбрал хеш-соединения вместо оператора Loop Join как для соединений между временной таблицей и Т1, так и для оператора anti-semi join, используемого для обработки предиката NOT EXISTS.
Рисунок 7. Производительность Решения 2 относительно размера группы
Решение 2 действительно гораздо эффективнее, чем Решение 1, но ему свойственны недостатки производительности. Имея группу из N элементов, транзитивное закрытие такой группы имеет (1 + 2 + ... + N-1) х 2 = N∧2 — N ребер. Например, при наличии 10 элементов транзитивное закрытие имеет 90 ребер. При 100 элементах — 9900. При 1000 элементов — 999 000 ребер. Другими словами, количество ребер увеличивается экспоненциально в соответствии с количеством элементов. Это означает, что решение масштабируется слабо. На рисунке 7 показано время выполнения данного решения в секундах для одной группы с числом строк, увеличивающимся от 100 до 1000.
Поэтому, если вы не имеете дела с очень малым количеством групп. Решение 2 нельзя назвать удачным вариантом.
Решение 3. Развертывание связанных узлов
В Решении 2 сначала развернуты все возможные соединения между парами узлов в группе путем создания транзитивного закрытия, а затем оно свертывает их для вычисления идентификатора группы. Результатом становится экспоненциальное масштабирование. В Решении 3 подход с развертыванием-свертыванием не применяется. Вместо этого группы обрабатываются по одной, начиная с одной пары (TOP (1) для необработанной пары ORDER BY id1, id2), а затем развертываются соединения, подобно тому как развертывается иерархия «родитель-потомок» как в левом, так и в правом направлении. Значение id1 из первой пары группы используется как идентификатор группы. Решение записывает обнаруженные узлы с идентификатором группы (значение id1 из первой пары) во временной таблице с именем #G, используя предикат NOT EXISTS для проверки, добавляются ли только необработанные узлы. В коде 8 приведен полный программный код решения.
Код 8. Код решения 3
Решили отложить изучение данного материала до тех пор, пока не решите проблему со своим холодильником Electrolux, который перестал исполнять свою основную функцию? В этом случае обязательно загляните на holodservice.net.ua/electrolux. Здесь вы найдете опытных специалистов, которые в самые сжатые сроки вдохнут новую жизнь в ваш холодильник.
