SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |

Уловка, добавляющая промежуточную материализацию в соединение

ПУБЛИКАЦИИ  

По материалам статьи Adam Machanic: Exploring the secrets of intermediate materialization

Работая с SQL Server 2000, я частенько использовал эту небольшую уловку, которая мне помогала, когда уже не было других идей по тюнингу запроса. И я уже думал, что эта уловка наверняка не будет работать с SQL Server 2005, но благодаря коллеге (являющемуся SQL Server MVP) Робу Фарлею, я могу смело представить эту уловку на страницах своего блога.
Но сначала давайте рассмотрим используемый для примеров запрос. У нас будет такой сценарий событий: Допустим, Вы работаете в компании AdventureWorks, и начальство попросило Вас подготовить докладную записку, которая показывала бы, сколько сотрудников равного уровня подчинения есть у каждого служащего компании. Вы догадываетесь, что начальство AdventureWorks возомнило, что если два служащих подчиняются одному и тому же человеку, то они должны исполняют одни и те же обязанности, т.е. вполне могут при необходимости подменять друг - друга. Кроме того, руководство хочет узнать, у кого слишком много подчинённых (наверное, что бы это можно было учесть при сокращении штата), а у кого их слишком мало, и куда следует нанять дополнительный штат, что бы всегда была возможность оперативной подмены сотрудников их коллегами. Впрочем, вне зависимости от того, разумны ли желания руководства или нет (подобные оценки выходят за рамки этой статьи), давайте сконцентрируемся на ключевых моментах поставленной задачи. Особо обратите внимание, на те идеи, которые, несомненно, уже у Вас возникли, и будут возникать в процессе прочтения этого материала, во время которого, я надеюсь, улыбка, вызванная удачными решениями, не раз украсит ваше лицо.
Но улыбаться мы будем позже. Пока что, я вынужден прервать это отступление от темы и вернуться непосредственно к самому запросу, позволяющему решить поставленную задачу:

SELECT x.EmployeeId, COUNT(*) AS TheCount FROM HumanResources.Employee x JOIN HumanResources.Employee y on y.ManagerId = x.ManagerId GROUP BY x.EmployeeId

Этот код находит и подсчитывает всех служащих, подчинённых одному и тому же менеджеру. Но где же обещанная уловка, спросите Вы? Чтобы ответить на этот вопрос, давайте вначале разберёмся с вводом-выводом и планом исполнения, который использует запрос. Сначала, посмотрим на основу для ввода-вывода, выполнив такую пробу:

SELECT * FROM HumanResources.Employee

Profiler показывает, что было выполнено 20 логических чтений. В то же время, наш первый запрос сделал 827 логических чтений. Многовато для запроса, который использует только одну таблицу… явно, много ресурсов потрачено впустую. После анализа плана запроса, становится понятно, что мы можем сделать запрос более эффективным. Сканирование внешней таблицы для поиска всех вхождений служащих - это пожирающая индекс операция.
Обычным методом оптимизации подобных запросов является перемещение агрегации в производную таблицу. Кроме того, внимательное изучение изменённого запроса позволяет сделать вывод, что нет причин агрегации по ManagerId больше одного раза для каждого менеджера. Почему же это делается один раз для служащего?

SELECT x.EmployeeId, y.theCount FROM HumanResources.Employee x JOIN ( SELECT p.ManagerId, COUNT(*) FROM HumanResources.Employee p GROUP BY p.ManagerId ) y (ManagerId, theCount) ON y.ManagerId = x.ManagerId

Уже лучше, но после исполнения этого варианта запроса, мы видим, что всё равно было выполнено 819 логических чтений при практически таком же плане исполнения запроса. Не очень то помогла нам такая оптимизация. Увы, идя по этому пути, мы не сможем добиться многого. У нас слишком мало возможностей, и каждая из них требует подсчёта в цикле… или нет, как думаете?
И вот, мы уже совсем близко подошли к описанию той самой "грязной" уловки. Но сначала давайте попробуем не такую "грязную" уловочку. Используя временную таблицу, мы могли бы избавиться от некоторой доли логических чтений, ведь так? Тогда нам придётся делать запрос к базовой таблице только один раз...

SELECT p.ManagerId, COUNT(*) AS theCount INTO #y FROM HumanResources.Employee p GROUP BY p.ManagerId SELECT x.EmployeeId, y.theCount FROM HumanResources.Employee x JOIN #y y ON y.ManagerId = x.ManagerId

207 логических чтений. Вот это уже оптимизация! Но вариант с временной таблицей все еще использует вложенный цикл, и слияние подошло бы лучше, не так ли? Используя подсказку для MERGE JOIN можно уменьшить число чтений до 115, но я всё еще чувствую, что мы можем сделать запрос ещё лучше.
Если бы этот запрос писался для SQL Server 2000, в целях оптимизации, я бы попробовал задействовать промежуточную материализацию результирующей таблицы, используя для просмотра временной таблицы TOP 100 PERCENT вместе с ORDER BY. К сожалению, команда разработки оптимизатора запросов SQL Server решила, что это плохая идея, и оптимизатор теперь игнорирует такие уловки.
До сегодняшнего дня я думал, что на этом игра закончена, пока Роб мне не напомнил, что TOP используется не только с процентами. И как же тут применить нашу уловку? Будем использовать больше строк, чем когда-либо может быть возвращено... Припоминаю, кажется, максимальное значение для типа INTEGER в SQL Server составляет 2147483647?
Применяя TOP и ORDER BY для промежуточной таблицы, мы можем вынудить SQL Server использовать промежуточную материализацию результата, а подбирая соответствующий вариант использования ORDER BY, мы можем вынудить оптимизатор использовать слияние...

SELECT x.EmployeeId, y.theCount FROM HumanResources.Employee x JOIN ( SELECT TOP (2147483647) p.ManagerId, COUNT(*) FROM HumanResources.Employee p GROUP BY p.ManagerId ORDER BY p.ManagerId ) y (ManagerId, theCount) on y.ManagerId = x.ManagerId

И что же получилось в результате наших мучений? 10 логических чтений (1000%-ое повышение производительности при использовании этой уловки), задействована операция слияния, и, что я могу сказать, очень занятная статья для блога.
Действуют обычные в этих случаях отказы от ответственности применения описанных в статье методов. Не пробуйте эту уловку на "боевых" системах. Не полагайтесь на эти не документированные возможности. Это не решение Ваших проблем. Не требуйте от меня помощи в настройке ваших баз данных, если эта уловка не устранит все ваши проблемы. И, пока я не забыл, не тратьте впустую время, читая этот блог, если начальство требует от Вас сделать отчёт "вчера"!

[В начало]

Перевод: Александра Гладченко  2007г.

Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013