Теоретический материал
Один из наиболее важных аспектов настройки запроса – измерение или количественная оценка производительности. При измерении производительности нужно знать, что на самом деле измерять, т.е. какую систему показателей применять. В SQL Server есть три основных учитываемых показателя: затраты на запрос или стоимость запроса (query cost), число считываний страницы (page reads) и время выполнения запроса (query execution time).
Стоимость запроса
Стоимость выполнения запроса – это внутренняя характеристика, применяемая в SQL Server и учитывающая ресурсы ЦП и ввода/вывода, потребляемые запросом. Теоретически, чем меньше стоимость запроса, тем выше его производительность. На стоимость не влияют такие проблемы, как конфликт ресурсов или ожидание снятия блокировок. В большинстве случаев стоимость запроса – хорошая мера производительности, но если в запросе применяются особые элементы, например, скалярные пользовательские функции или программы общеязыковой среды выполнения (Common Language Runtime, CLR), затраты на них не учитываются, что делает стоимость запроса ниже реальной. Поэтому стоимость выполнения запроса называют ориентировочной или приблизительной стоимостью запроса (estimated query cost).
Считывания страниц
Количество считываний страниц представляет количество 8-килобайтовых страниц данных, к которым обращался механизм запоминания SQL Server во время выполнения запроса. Извлечь эту характеристику можно с помощью команды SET STATISTICS IO ON. Эта команда приводит к выводу на вкладке окна запроса Message сведений, подобных приведенным далее:
Table 'Buyer'. Scan count 0, logical reads 526, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Bill'. Scan count 1, logical reads 713, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Общее количество считанных страниц в этом примере равно 526 + 713, т.е. сумме значений, обозначенных как logical reads. Логические считывания - это количество считанных из памяти страниц. Логические считывания представляют количества страниц данных, прочитанных из любого индекса таблицы Bill. Другие показатели сообщают о том, сколько логических считываний произведено с жесткого диска (physical и read-ahead read), количество выполненных сканирований индекса или кучи в ответ на запрос и количество считываний, потребовавшихся для извлечения данных типа Large Object (LOB). Данные типа LOB хранятся вне строки с типами данных varchar(max), nvarchar(max), varbinary(max), text, ntext, image и XML. Показатель считывания страниц не учитывает ресурсы ЦП, потребляемые во время выполнения запроса. Поэтому количество считываний страниц - обычно не столь подходящая мера производительности, как стоимость запроса. У этих показателей существует та же проблема, что и у стоимости запроса, связанная со скалярными пользовательскими функциями и подпрограммами на CLR и заключающаяся в том, что считывания, вызываемые этими объектами, не включается в вывод параметра STATISTICS IO.
Время выполнения запроса
Время выполнения запроса – самая изменчивая характеристика. На нее влияют блокировки и конфликты ресурсов на сервере. С учетом этого особенно важно всегда включать в сравнение производительностей время выполнения запроса, потому что эта мера может помочь обнаружить проблемы, пропущенные другими показателями производительности. Если выполнить команду SET STATISTICS TIME ON, SQL Server вернет время выполнения всех запросов в миллисекундах.
Алгоритмы соединения
Оптимизатор запросов SQL Server обычно автоматически выбирает наилучший план выполнения запроса. Поэтому подсказки рекомендуется использовать только опытным пользователям и администраторам базы данных в случае крайней необходимости.
Без явного указания аргумента (LOOP | HASH | MERGE) оптимизатор выбирает, на его взгляд, самый оптимальный план. Но мы всегда можем повлиять на него, если явно укажем подсказку [3].
Соединения при помощи вложенных циклов
Соединение LOOP JOIN, называемое также nested iteration, использует одну таблицу в качестве внешней (на графическом плане она является верхней), а второй в качестве внутренней (нижней). LOOP JOIN построчно сравнивает внешнюю таблицу с внутренней. В цикле для каждой внешней строки производится сканирование внутренней таблицы и выводятся совпадающие строки.
В простейшем случае во время поиска целиком сканируется таблица или индекс (naive nested loops join). Если при поиске используется индекс, то такой поиск называется index nested loops join. Если индекс создается в качестве части плана запроса (и уничтожается после завершения запроса), то он называется temporary index nested loops join. Оптимизатор сам выбирает один из этих поисков.
LOOP JOIN является особенно эффективным в случае, когда внешняя таблица сравнительно невелика, а внутренняя гораздо больше и для неё существуют индексы. Планы соединений с вложенными циклами являются надежными, так как не требуют много памяти и не требуют места на диске. В большинстве запросов соединения с вложенными циклами будут наиболее эффективными и обеспечат предсказуемое поведение в случае изменения размеров соединяемых таблиц.
Пример запроса использующего алгоритм соединения вложенных циклов представлен далее:
SELECT B.BillID,
BU.Name AS BuyerName,
E.Name AS EmployeeName
FROM Bill AS B JOIN Employee AS E ON B.EmployeeID = E.EmployeeID
LEFT LOOP JOIN Buyer AS BU ON BU.BuyerID = B.BuyerID
Соединения слиянием
MERGE JOIN требует сортировки обоих наборов входных данных по столбцам слияния, которые определены предложениями равенства (ON) предиката соединения. Если мы имеем предикат соединения «B.EmployeeID = E.EmployeeID», то таблица B должна быть отсортирована по B.EmployeeID, а таблица E должна быть сортирована по E.EmployeeID.
Так как каждый набор входных данных сортируется, оператор Merge Join получает строку из каждого набора входных данных и сравнивает их. Например, для операций INNER JOIN строки возвращаются в том случае, если они равны. Если они не равны, строка с меньшим значением не учитывается, и из этого набора входных данных берется другая строка. Этот процесс повторяется, пока не будет выполнена обработка всех строк.
MERGE JOIN может поддерживать слияние «многие ко многим». В этом случае, при каждом соединении двух строк нужно сохранять копию каждой строки второго входного потока. Это позволяет, при последующем обнаружении в первом входном потоке дубликатов строк, воспроизвести сохраненные строки. С другой стороны, если будет ясно, что следующая строка первого входного потока не является дубликатом, от сохраненных строк можно отказаться. Такие строки сохраняются во временной таблице базы tempdb. Размер дискового пространства, который для этого необходим, зависит от числа дубликатов во втором входном потоке.
MERGE JOIN «один ко многим» всегда будет эффективнее слияния «многие ко многим», поскольку для него не требуется временная таблица. Для того чтобы задействовать слиянием «один ко многим», оптимизатор должен иметь возможность определить, что один из входных потоков состоит из уникальных строк. Как правило, это означает, что у такого входного потока существует уникальный индекс или в плане запроса присутствует явным образом оператор (например, сортировка при DISTINCT или группировка), который гарантирует, что строки на входе будут уникальны.
MERGE JOIN — очень быстрая операция, но она может оказаться ресурсоемкой, если требуется выполнение операций сортировки. Однако на больших объёмах при наличии индексов и предварительной сортировке, соединение слиянием является самым быстрым из доступных алгоритмов соединения [4].
Пример запроса использующего алгоритм соединения слиянием:
SELECT B.BillID,
BU.Name AS BuyerName,
E.Name AS EmployeeName
FROM Bill AS B JOIN Employee AS E ON B.EmployeeID = E.EmployeeID
LEFT MERGE JOIN Buyer AS BU ON BU.BuyerID = B.BuyerID
Соединение хешированием
HASH JOIN – более эффективен при работе с большими наборами данных и даже тогда, когда таблицы не отсортированы по столбцам, по которым производится соединение. HASH JOIN распараллеливается и масштабируется лучше любого другого соединения и сильно выигрывает при большой производительности информационных хранилищ. Всегда адекватно оценивайте возможности распараллеливания, так как довольно часто издержки на поддержку процедуры распараллеливания соизмеримы со временем, которое экономится при параллельном выполнении операций.
Соединение происходит с использованием хеширования, вычисляя хеш записей из меньшей таблицы (Build-таблица) и вставляя их в хеш-таблицу, затем обрабатывается большая таблица (Probe-таблица) по одной записи, сканируя хеш-таблицу для поиска совпадений [4].
Пример запроса использующего алгоритм соединения слиянием:
SELECT B.BillID,
BU.Name AS BuyerName,
E.Name AS EmployeeName
FROM Bill AS B JOIN Employee AS E ON B.EmployeeID = E.EmployeeID
LEFT HASH JOIN Buyer AS BU ON BU.BuyerID = B.BuyerID
Следует учитывать, что, не смотря на высокую производительность соединения слиянием и соединения хэшированием, они очень чувствительны к доступным аппаратным ресурсам и размерам соединяемых таблиц. В этом смысле наиболее предсказуемое и надежное поведение демонстрирует соединение с использованием вложенных циклов.
Содержание работы
1. Запросы, созданные в пунктах 1,2 предыдущей лабораторной работы переделайте таким образом, чтобы для соединения таблиц использовался алгоритмы соединения с использованием вложенных циклов, соединения хешированием и соединения слиянием.
2. Оцениваем время выполнения каждого из полученных запросов.
3. Полученные запросы сохраните в представлениях, добавляя к имени исходного представления имя используемого алгоритма.
4. Подготовьте материал для включения в отчетную презентацию по курсу Базы данных: специальный курс.