Соединения при помощи вложенных циклов

Теоретический материал

Один из более принципиальных качеств опции запроса – измерение либо количественная оценка производительности. При измерении производительности необходимо знать, что по сути определять, т.е. какую систему характеристик использовать. В 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, употребляет одну таблицу в качестве наружной (на графическом плане она является верхней), а 2-ой в качестве внутренней (нижней). 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 может поддерживать слияние «многие ко Соединения при помощи вложенных циклов многим». В данном случае, при каждом соединении 2-ух строк необходимо сохранять копию каждой строчки второго входного потока. Это позволяет, при следующем обнаружении в первом входном потоке дубликатов строк, воспроизвести сохраненные строчки. С другой стороны, если будет ясно, что последующая строчка первого входного потока не является дубликатом, от сохраненных строк Соединения при помощи вложенных циклов можно отрешиться. Такие строчки сохраняются во временной таблице базы tempdb. Размер дискового места, который для этого нужен, находится в зависимости от числа дубликатов во 2-м входном потоке.

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. Подготовьте материал для включения в отчетную презентацию по курсу Базы данных: особый курс.


soglasnie-zvuki-russkogo-yazika.html
soglasno-dzh-m-kejnsu-bezrabotica-nosit-harakter.html
soglasno-garantijnim-obyazatelstvam-na-avtomobil-na-nego-ustanovlena-garantiya-3-goda.html