Новость из категории: Информация

Группирование связанных элементов в T-SQL. Часть III

Содержание:
1. Часть I;
2. Часть II ;
3. Часть III (Вы читаете данный раздел).
Группирование связанных элементов в T-SQL. Часть III

На рисунке 8 показана производительность этого решения в зависимости от размера группы. Как мы видим, это решение линейно масштабируется в зависимости от размера группы и может обрабатывать гораздо более многочисленные группы по сравнению с Решением 2.

Группирование связанных элементов в T-SQL. Часть III
Рисунок 8. Производительность Решения 3 относительно размера группы

Однако, если увеличить количество групп, сохраняя размеры группы неизменными, время выполнения круто идет вверх линейным образом (детальный график производительности приведен на рисунке 11). Например, вот как выглядит время выполнения для трех прогонов Решения 3 с тремя различными количествами групп:
• 100 групп со 100 строками в каждой — 1 секунда;
• 500 групп со 100 строками в каждой — 18 секунд;
• 1000 групп со 100 строками в каждой — 71 секунда.

После выполнения Решения 3 с большим количеством групп обратитесь к разделу Recent Expensive Queries («Последние ресурсоемкие запросы») в мониторе активности. На рисунке 9 показан снимок экрана этого раздела на моем компьютере.

Группирование связанных элементов в T-SQL. Часть III
Рисунок 9. Монитор активности — последние ресурсоемкие запросы

Больше всего ресурсов потребляет, похоже, выделенный запрос TOP (1). Это не первый запрос TOP (1) из Решения 3; в действительности это элемент в конце внешнего цикла. Первый запрос TOP (1) идентифицирует первую пару в первой группе (на основе упорядочения id1, id2). В ней отсутствует предложение WHERE, поскольку пока не обработано ни одного узла. Ресурсоемкость этого запроса очень мала. Запрос TOP (1) в конце внешнего цикла (идентифицированного как ресурсоемкий в мониторе активности) должен найти первую пару следующей необработанной группы и поэтому использует предикат NOT EXISTS, чтобы проверять только строки, где id1 не присутствует в #G. Проблема в том, что по мере продвижения по группе просматривается нарастающее число строк в Т1, пока не будет найден первый необработанный элемент. Щелкните правой кнопкой мыши на запросе TOP (1) в мониторе активности, выберите в контекстном меню пункт Show Execution Plan («Показать план выполнения»), а затем откройте этот план в обозревателе планов ScntryOne. Полученный мною план показан на рисунке 10.

Группирование связанных элементов в T-SQL. Часть III
Рисунок 10. План для запроса ТОР (1) в Решении 3

Обозреватель планов предупреждает, что, хотя от запроса ожидается возвращение только одной строки, операция может привести к остаточному вводу-выводу с прочтением приблизительно 100000 строк. Это число, конечно, зависит от количества групп и размера групп, используемых в процессе тестирования. Главное, что это решение плохо масштабируется при увеличении числа групп.

Группирование связанных элементов в T-SQL. Часть III

Решение 4. Развертывание связанных узлов с удалением обработанных ребер
Группирование связанных элементов в T-SQL. Часть III
Рисунок 11. Влияние количества групп на производительность Решений 3 и 4

Чтобы обойти недостаток Решения 3 и избежать остаточного ввода-вывода, в Решении 4 сначала выполняется копирование содержимого Т1 во временную таблицу с именем #Т1, и на каждом проходе обработанные пары удаляются. Для обработки пар текущего прохода, представляющих соединения слева или справа, инструкция DELETE удаляет их из #Т1 и использует предложение OUTPUT для записи идентификаторов в табличную переменную с именем @CurIDs. Идентификаторы из @CurIDs, которые пока не существуют в #G, затем записываются в #G с групповым идентификатором текущей группы. Это означает, что запрос TOP (1) в конце внешнего цикла, который идентифицирует первую строку в следующей группе, не нуждается в предложении WHERE и затрагивает только одну строку. Остаточный ввод-вывод отсутствует. Полный программный код решения приведен в коде 9. На рисунке 11 сравнивается производительность Решений 3 и 4 и видно, как оба решения масштабируются в зависимости от количества групп.

Группирование связанных элементов в T-SQL. Часть III
Код 9. Код решения 4

Итак, победитель очевиден.

Группирование связанных элементов в T-SQL. Часть III
Рисунок 12. Сводка производительности (логарифмическая шкала)

В заключение я хочу сказать, что задачи, связанные с графами, часто оказываются сложными, так как легко напасть на плохо масштабируемое решение. Это в полной мере относится и к рассмотренной в данной статье задаче группирования связанных элементов. Производительность Решения 1, основанного на транзитивном закрытии и табличной переменной, очень низкая даже с небольшими объемами данных. Решение 2 на основе транзитивного закрытия и временной таблицы имеет более высокую производительность, но все же масштабируется экспоненциально. Решение 3 обрабатывает группы по одной, начиная с первой пары и развертывая соединения подобно развертыванию иерархий «родитель-потомок». Оно прекрасно масштабируется относительно размера группы, но не так хорошо относительно количества групп, из-за того что запрос, отыскивающий первую пару в группе, должен просмотреть и обработать нарастающее число строк. Решение 4 добавляет измерение в Решение 3, работая с копией данных во временной таблице и удаляя все обработанные строки на каждом проходе. Таким образом значительно улучшается масштабирование относительно количества групп. На рисунке 12 приведен сводный график производительности решений (Решение 1 не показано на графике, так как оно слишком медленное).


Так и не смогли понять суть материала данной статьи? В этом случае вначале рекомендую вам посетить блог о программировании shwanoff.ru и подтянуть свои знания в данной области. После этого смело можете заново приступать к изучению данного материала.

Рейтинг статьи

Оценка
0/5
голосов: 0
Ваша оценка статье по пятибальной шкале:
 
 
   

Поделиться

Похожие новости

Комментарии

^ Наверх