Наверное, не будет большим преувеличением сказать, что процессор запросов (query processor) является стержневым элементом архитектуры серверов баз данных. Его эффективность в значительной мере определяет популярность продукта на рынке СУБД. Проектирование процессора запросов Microsoft SQL Server 7.0 ставило своей основной задачей обеспечение функциональности, быстродействия и надежности при работе с базами данных масштаба крупной корпорации. Несмотря на то, что предыдущим версиям Microsoft SQL Server уже принадлежали многочисленные рекорды по удельной (из расчета на один узел) OLTP-производительности и по критерию "цена / производительность", процессор запросов в версии 7.0 был подвергнут существенной переработке исходя из особенностей корпоративных систем, таких как наличие унаследованных источников данных, большое число пользователей, значительные объемы информации и преобладание сложных запросов по ее обработке. Эта специфика получила свое отражение в архитектуре и логике работы процессора запросов и подсистемы управления блокировками.

Таинство превращения множественно-ориентированного стиля языка SQL в процедурный план выполнения начинается с разбора (parsing) запроса, за которым следуют нормализация (normalization), предобработка (preprocessing), компиляция (compilation) и, собственно, выполнение. Важнейшей задачей компиляции является не просто генерация программного псевдокода (p-code), а построение оптимального (наискорейшего) способа выполнения запроса. Для этого к процессу обработки подключается оптимизатор, который выполняет анализ запроса на основе имеющихся индексов, распределения данных в проиндексированных колонках, количества данных в таблицах, операторов и величин в условиях where, join, union, group by, order by и т.д. В заключение оптимизатор принимает решение о режиме обновления (deferred или direct) и уровне блокировки. Изменения в процессоре запросов Microsoft SQL Server 7.0 коснулись практически каждого из перечисленных этапов оптимизации. Не претендуя на исчерпывающий рассказ о каждом из них, в рамках настоящей статьи мы коснемся лишь некоторых новых черт, посвященных этапу построения связей, а также стратегиям внутризапросного параллелизма и универсального доступа к данным.

Автор выражает искреннюю признательность Гётцу Графэ (Goetz Graefe), Microsoft, чьи идеи, консультации и советы оказали неоценимую помощь в ходе работы над данной статьей.

1.1 Nested Loop

Из теории, а точнее, практики реляционных баз данных мы знаем, что существуют различные способы реализации связей (joins): nested loop, index lookup, hash lookup, merge и т.д. Давайте посмотрим, как строятся связи между таблицами в SQL Server. Вложенный цикл (nested loop, в выдачах плана SQL Server 6.х - nested iteration) является самым простым из перечисленных алгоритмов:

План выглядит следующим образом:

Вложенный цикл был практически единственной стратегией реализации связей в предыдущих версиях продукта ([9]). Как следует из названия стратегии, построение связи осуществляется по следующему алгоритму: для каждой записи таблицы authors пробежать таблицу titleauthor, выбрав из нее все записи, которые можно сопоставить текущей записи таблицы authors. В этой терминологии таблица, соответствующая внутреннему циклу (titleauthor), называется внутренней, соответствующая внешнему циклу (authors)- внешней. Алгоритм можно охарактеризовать как метод грубой силы, поскольку при отсутствии подходящих индексов внутренняя таблица должна просматриваться столько раз, сколько записей во внешней. Если, как в нашем примере, подходящие индексы существуют, nested loop объединяется с поиском по индексу (index lookup). Cтоимость связи вычисляется как количество страниц во внешней таблице + количество записей, удовлетворяющих возможному условию фильтрации во внешней таблице * количество страниц, которые необходимо прочитать за один поиск во внутренней таблице. Желающие узнать, как считается количество страниц при поиске записи, удовлетворяющей определенному условию, если это условие является SARGом (индексный поиск), могут обратиться к [8]. Если же индекса для выражения условия подобрать не удается, это будет просто общее количество страниц во внутренней таблице. SQL Server 6.х полагал, что независимо от действительного местоположения на начало выполнения запроса внутренняя таблица во время первой итерации должна считываться с диска, а при последующих - из кэша данных (буферного кэша), если его свободный размер позволяет ее туда засунуть.

Легко видеть, что здесь стоимость связи зависит от порядка связывания. Рассмотрим два возможных порядка связывания: titleauthor -> author и authors -> titleauthor. Пусть имеем индексы по authors.au_lname и titleauthor.au_id. Индекс по authors.au_id предположим отсутствующим. Отработка связывания по первому порядку будет происходить следующим образом: 1) взять запись из titleauthor; 2) сканированием таблицы authors найти в ней все подходящие записи (у которых author.au_id=titleauthor.au_id для текущей записи в titleauthor) и взять только те из них, у кого au_lname like "R%". Второй порядок будет выполняться так: 1) взять запись из author; 2) если она не удовлетворяет условию фильтрации authors.au_lname like "R%", перейти к следующей; 3) найти с использованием индекса по titleauthor.au_id все подходящие записи с таким же au_id в titleauthor. Очевидно, что второй порядок будет стоить существенно дешевле. Таким образом, интуитивно понятно, что на роль оптимальных претендуют порядки, где внешняя таблица имеет условие фильтрации, внутренняя таблица меньше по размеру (чтобы с большей вероятностью поместиться в кэше) и имеет индекс по внешнему ключу. Последнее условие, на самом деле, носит нестрогий характер, потому что если такого индекса нет, SQL Server 6.х мог применять так называемую стратегию реформатирования (reformatting strategy). Эта небольшая прелюдия к основному алгоритму заключается в копировании содержимого внутренней таблицы в tempdb и построении на временную таблицу кластерного индекса по полям, участвующим в join, после чего она связывается с внешней. Наличие кластерного индекса существенно снижает время поиска подходящей записи во внутренней таблице. Если выигрыш, который за счет этого достигается, окупает копирование таблицы и создание индекса, выбиралась стратегия реформатирования. SQL Server 6.5 можно заставить использовать ее вообще всегда независимо от выигрыша или проигрыша, если поднять флаг трассировки 318. В запросе может происходить связывание большого числа таблиц. Как в этом случае определить оптимальный порядок? Простой перебор дает n! возможных комбинаций. Если предположить, что одна комбинация оценивается за 10-6 с, то определение оптимального порядка среди 16 таблиц заняло бы, как легко видеть, порядка 230 дней. В связи с этим используется приближенная оценка. Процессор запросов SQL Server 6.х производил упорядоченные выборки из n связываемых таблиц по 4 и определят среди них оптимальную. Внешняя таблица этой выборки считается самой внешней в окончательном порядке и из дальнейшего перебора исключается. Данная процедура повторяется для оставшихся n-1 таблиц, затем n-2 и т.д. Всего число комбинаций, которое при таком подходе необходимо перебрать оптимизатору, составляет An4 + An-14 + ... + A44, где Ank=n!/(n-k)! - число размещений из n по k. При k<<n эта сумма будет существенно меньше, чем n!. В частности, для n=32, k=4 на 28 порядков. Включение флага трассировки 345 дает оптимизатору SQL Server 6.5 указание рассматривать по 6 таблиц одновременно, что приводит к увеличению времени компиляции, но повышает точность выбора оптимального порядка. Обычно это использовалось для выполнения TPC-D запросов.

Рассмотрим следующий запрос:

Мы видим, что порядок построения связей, выбранный оптимизатором, имеет вид: authors-> titleauthor-> titles-> sales-> publishers-> stores, т.е. таблицы publishers и sales поменялись местами. Применение команды SET FORCEPLAN ON обяжет оптимизатор строить связи между таблицами ровно в том порядке, в каком они были перечислены в операторе SELECT. Кстати, заодно стоит обратить внимание, что аргумент на шаге Bookmark Lookup после присоединения таблицы sales помечен как with prefetch, что означает, что поиск закладок идет с использованием опережающего чтения (read ahead).

1.2 Merge Join

В SQL Server 6.5 существовала еще одна разновидность связывания, которую можно условно охарактеризовать как псевдо-merge join. Рассмотрим случай, когда нам необходимо связать таблицы T1, T2 и Т3, причем для только для связи между Т1 join Т2 существует подходящий индекс. Тогда по этому индексу таблицы Т1 и Т2 связываются во временную рабочую таблицу W, проиндексированную для быстрой связи с Т3. Флаг 343 включал принудительное применение этой стратегии, флаг 342, наоборот, предотвращал ее применение когда бы то ни было. Физически реализация связи все равно осуществлялась через indexed nested loop, поэтому по отношению к 6.5 мы употребили термин "псевдо-merge".

В процессоре запросов SQL Server 7.0 реализована полноценная стратегия merge join. Рассмотрим пример:

Таблица member состоит из 10000 записей, charge - из 100000. План выполнения запроса:

Обратите внимание на то, что таблицы member и charge имеют кластерные индексы. В этом состоит особенность merge join. Ее очень выгодно использовать, когда все входы (связываемые таблицы в запросе) физически отсортированы по атрибутам связи. Пусть нам нужно завязать отношением "многие-ко-многим" таблицы Т1 по атрибуту fld1 и Т2 по атрибуту fld2. Псевдокод алгоритма merge join при построении отношения выглядит примерно так.

Как следует из этого псевдокода, преимущество условия предварительной сортировки таблиц состоит в том, что при построении связи нам приходится просматривать один раз таблицу Т2 и почти один раз Т1 (почти - за счет возвратов при разрешении связи "многие-ко-многим", количество возвратов зависит от селективности Т1.fld1). Если Т1 связана с Т2 отношением "один-ко-многим", то просмотр Т1 и Т2 выполняется строго по одному разу. Таким образом, необходимое число итераций в этом случае будет не n1*n2, как в случае nested loop без индексов (n1, n2 - количество записей в таблицах), а n1+n2, что, очевидно, приятней. Т.е. записи в таблицах как бы складываются друг с другом, из-за чего эта стратегия и называется merge (слияние). Из сказанного следует, что оптимизатор будет выбирать ее в ситуациях, когда входы (inputs) отсортированы по атрибутам связи, например, по полям fld1 и fld2 существуют кластерные индексы, и этот порядок должен быть сохранен на выходе ([6]).

Продолжим работу с нашей базой. Сделаем индексы по таблицам member и charge некластерными и отправим запрос:

Ход рассуждений процессора запросов выглядит примерно так. Так, входы здоровые, это плохо, nested loop влетит в копеечку, надо что-нибудь похитрее. Входы неотсортированы, значит, merge join тоже не подходит, а жаль. Ага, пользователя, по-видимому, устраивает, если записи на выходе будут возвращены в их физическом порядке, во всяком случае ничего обратного он мне не сказал. В результате, как мы видим, процессор запросов избирает стратегию под названием hash join. Коротко поясним ее принципы на примере связывания двух таблиц. Если одна из них, скажем, Т1, не превышает размер памяти, отводимой под данную операцию, то для нее в памяти строится хэш-таблица. Отсюда, Т1 будет называться build input. При этом над атрибутом связи А (полями, участвующими в join) каждого кортежа (записи) ti1 отношения (таблицы) Т1 выполняется некоторая хэш-функция h (ti1.А) и результат от нее вместе с указателем на данную запись кладется в хэш-таблицу H. Атрибут А в этом случае называется хэш-ключом (hash key), а результат применения хэш-функции h (ti1.А) - хэш-значением (hash value). Хэш-значение должно занимать меньше места, чем хэш-ключ. Основные требования к хэш-преобразованию - быстрота, равномерность распределения результатов, малое число коллизий. Пусть число слотов в хэш-таблице равно N. Примеры хэш-функций: 1) h(x) = x mod N (лучше, если N - простое число); 2) h(x) = q средних битов от x2, где N = 2q; 3) h(x) = p1(x) xor ... xor pm(x), где N=2q, а pi(x) - i-я группа по q битов из х, выстроенная в обратном порядке. В качестве бытовых примеров хэширования обычно приводится записная книжка. Достаточно открыть записную книжку на нужной букве и пробежать цепочку коллизий (фамилий, начинающихся с одной и той же буквы), пока не наткнемся на искомое.

Коллизия (hash collision, или hash clash) происходит, когда хэш-функция дает одно и то же значение для нескольких разных записей, т.е. когда мы обнаруживаем, что слот, соответствующий h(tj1.A), куда мы хотели положить указатель на tj1, уже занят под одну из предыдущих записей ti1, потому что h(ti1.A)= h(tj1.A), где А - атрибут связи. Один из выходов - растить из этого слота цепочку, в которую увязывать указатели на все такие записи. Такая схема разрешения коллизий носит название chaining (варианты: separate chaining, coalesced chaining). Другой выход - поискать свободный слот (linear probing, double hashing) и т.д. Схема разрешения коллизий способна оказать даже большее влияние на производительность, чем вид хэш-функции. Для ускорения поиска мы можем сгруппировать по тому или иному принципу слоты хэш-таблицы. Такие группы слотов называются hash buckets (переводится как "хэш-корзины", также в последнее время часто встречается неформальное "букеты"). В качестве принципа группировки можно выбрать другое хэш-преобразование g, например, взятие остатка от деления результата первой хэш-функции h на количество букетов, которое в этом случае стоит положить равным простому числу для улучшения свойств g. Именно по такому принципу ([11]) осуществлялся доступ к страницам памяти в кэше данных SQL Server 6.х (настройка hash buckets в серверной конфигурации). Итак, в общем случае структура хэш-таблицы представляет собой связный список букетов, состоящих из слотов с хэш-ключами. Если в качестве алгоритма разрешения коллизий выбран separate chaining, то из каждого слота еще могут тянуться цепочки коллизий. Иногда группировка слотов не производится, в этом случае букетом считается каждый слот с относящимися к нему коллизиями.

После того, как хэш-таблица Н построена, то же самое преобразование h применяется ко второй таблице Т2 (probe input). Для каждой записи ti2 вычисляется h(ti2.B), где В - атрибут связи в Т2. Для h(ti2.B) определяется соответствующий букет в Н, содержимое которого сканируется и сопоставляется с ti2. Все подходящие пары ti1 * ti2добавляются в результат. Аналогично выполняются запросы с предикатами DISTINCT и GROUP BY. В первом случае хэш-функция применяется ко всем полям в SELECT, во втором - только к тем, которые относятся к условию группировки. Стоит заметить, что в предыдущих версиях SQL Server выполнял GROUP BY сортировкой, поэтому результат приходил заведомо упорядоченный по полям, входящим в GROUP BY. В 7.0 результат возвращается в порядке, определенном хэш-функцией, поэтому если есть необходимость его отсортировать, то наряду с GROUP BY следует явно поставить ORDER BY. Сходным образом происходит вычисление агрегатов. Алгоритм для hash aggregation выглядит так: 1) взять запись из таблицы на входе; 2) вычислить хэш-величину и определить подходящий букет; 3) если эта хэш-величина содержится в букете, добавить значение агрегатного поля данной записи к текущему значению агрегата; 4) в противном случае добавить в букет новую запись с вычисленной хэш-величиной.

Рассмотренная ситуация описывает простейшую разновидность hash join, известную как in-memory hash. Если ни одна из таблиц, участвующих в операции связывания, целиком не помещается в памяти, SQL Server 7.0 применяет алгоритм grace join (названный так по имени исследовательского проекта GRACE [4]). Он заключается в разбиении (partioning) таблиц Т1 и Т2 на фрагменты меньшего размера (fan-outs) на основе все того же хэш-преобразования. Для каждого из входов (Т1, Т2) фрагменту назначается так называемый файл переполнения (overflow file). В отличие от коллизии, переполнение в хэш-алгоритмах обозначает ситуацию, когда место в букете исчерпано. Коллизии могут приводить к возникновению переполнения. Если probing-разрешение коллизий, как мы упоминали, пытается переназначить слоты хэш-таблицы, то, например, при сhaining-разрешении первая же коллизия вызовет переполнение. Файлы переполнения хранятся на диске. В случае chaining-разрешения каждый файл удобно рассматривать как объединение цепочек коллизий для всех букетов фрагмента по данному входу. Букет может принадлежать только одному фрагменту. Считается, что размер одного фрагмента достаточно мал, чтобы уместиться в памяти, отведенной под запрос, за вычетом входных / выходных буферов (I/O buffers) и служебных объектов управления. В противном случае к фрагменту применяется повторное разбиение (recursive partitioning). Затем фрагменты попарно связываются друг с другом, как самостоятельные таблицы. Окончательный результат Т1 join T2 строится как T11 join T21 union ... union T1n join T2n. Очевидно, что T1i join T2i не пересекается с T1j join T2j (i<>j), так как если кортеж t1 * t2 удовлетворяет условию связи, то h(t1)=h(t2) и они попадают в одну пару. Возникает резонный вопрос: что происходит, если по прошествии нескольких уровней вглубь рекурсии разбиения нам все равно не удалось добиться размера фрагмента, приемлемого для in-memory hash или хотя бы hybrid hash (cм.ниже). Например, некий зловредный фрагмент обладает нулевой селективностью по атрибуту (т.е. содержит одинаковые ключи). Понятно, что его можно хэшировать до посинения, меньше он от этого все равно не станет. В этом случае оптимизатор выбирает альтернативные стратегии (bail out), в частности, уже рассмотренные нами sort- или loop-based join, которые применяются только по отношению к данному фрагменту, а не ко входам целиком. Возникает другой резонный вопрос: вместо того, чтобы тратить время на бесполезные в данном случае разбиения, нельзя ли как-то предсказать эту неприятную ситуацию заранее? Очевидно, что можно. Для этого на каждом шаге рекурсии следует собирать статистику распределения хэш-значений для последующего уровня погружения, что позволит нам вовремя остановиться. Эта методика называется histogram-guided partitioning ([6]). Кстати, набранная статистика может потом пригодиться, например, для обращения ролей (см.ниже).

Если build input ненамного превышает размер доступной памяти, SQL Server 7.0 использует hybrid hash join. Представим себе, что, обрабатывая build input, по алгоритму grace join, мы тем не менее добросовестно запихиваем в хэш-таблицу все, что относится к первому букету. Все остальные букеты скидываются на диск (spilled buckets). Вот наконец в буфере входа показалась страница от probe input. Хэшируем каждую принадлежащую ей запись и, если она относится к первому букету, быстро ищем для нее сопоставления на основе хэш-значений build input и посылаем их в output buffer. Если запись попадает в другой букет, ссылка на нее записывается в probe-файл переполнения, соответствующий фрагменту, которому принадлежит этот букет. Таким образом, часть хэш-таблицы обрабатывается подобно in-memory hash, а остальное - как в случае grace, чем и объясняется название hybrid join. Чтобы сэкономить на операциях ввода / вывода, можно попытаться исключить не участвующие в связи кортежи на возможно более ранних стадиях ее сборки. Для этого в SQL Server 7.0 применяется алгоритм фильтрации на основе битового вектора (bit vector filtering). При выполнении операций hash join SQL Server строит хэш-таблицу в виде довольно большого массива (порядка тысяч или десятков тысяч слотов в зависимости от объема доступной памяти). К каждому слоту привязан букет. Элементы букета содержат хэш-значение и указатель на запись. Они объединяются в структуры по 64 байта, которые составляют связный список, корнем которого является слот массива. Если во время построения hybrid join букет был положен на диск, то вместо указателя на букет слот хранит специальный битовый массив (вектор), образующийся по следующему принципу. К домену атрибута связи DA применяется некоторое преобразование I, которое отображает его на совокупность целых чисел. Создадим битовый массив с диапазоном [0, MI], включающим I(DA). Все элементы с индексами из I(DA) поднимем в 1, остальные будут нулевыми. Каждый букет, таким образом, будет содержать битовую карту входящих в него элементов, на основании которой, не залезая на диск, легко сказать, найдутся ли для текущей записи из probe input соответствия в данном spilled bucket. Если нет, то эта запись тут же выбрасывается или идет в output (например, для outer join), в любом случае она не пишется в probe-файл переполнения. Может статься, что хорошо отфильтрованный probe-файл переполнения T2i окажется меньше своего build'a T1i. В этом случае, как мы понимаем, выгодно поменять их местами, превратив probe input в build input и наоборот. Собственно, SQL Server 7.0 это тоже понимает и выполняет то, что называется dynamic role reversal (динамическое обращение ролей).

1.4 Hash Teams

Существенным нововведением в процессоре запросов SQL Server 7.0 является технология хэш-групп (hash teams). Рассмотрим ее более подробно. Обобщенный алгоритм обработки хэш-таблиц ([5]) удобно представить в виде вложенного цикла глубины 3. Самый внешний - по фрагментам, промежуточный - по входам и внутренний - по записям. Как правило, процедуры промежуточного цикла ориентированы на число входных параметров, не превышающее 2. Если мы привлекаем hash join для операций группировки или устранения дубликатов, получается один вход. Если с помощью hash join строится связь из n таблиц, то сначала выбираются две, которые связываются в промежуточный результат, тот, в свою очередь, связывается с третьей таблицей и т.д., образуя всякий раз по два входных параметра. Складывается впечатление, что для большинства распространенных задач достаточно иметь унарный или бинарный hash join. Тем не менее, например, в практике оптимизации запросов с операциями сортировки широко используется методика существенных порядков (interesting orders). Cмысл этого подхода очень простой: предположим, мы имеем запрос, выполняющий merge join 3-х таблиц по одному и тому же ключу. Если применять бинарный оператор, то потребуется выполнить 4 сортировки (3 исходных таблицы + 1 промежуточная). Но промежуточный результат подается на вход в уже отсортированном (после первого шага) виде, причем порядок сортировки нас устраивает, так как было оговорено, что ключ одинаков. Стало быть, можно сэкономить, отказавшись от сортировки промежуточных результатов, т.е. по сути дела, применить n-арный оператор связи. Идея хэш-групп ([5]) есть продолжение методики существенных порядков на область hash joins. Из запроса выделяются таблицы, связываемые по одному и тому же хэш-ключу. В каком бы порядке они ни обрабатывались, для них не требуется выполнять хэширование и разбиение промежуточных результатов, поскольку это делается в процессе обработки исходных таблиц, а атрибуты связи, по определению хэш-группы, одинаковы. Для реализации этой идеи модули, управляющие разбиением и сбросом букетов на диск, были выделены из индивидуальной операции хэширования и приписаны менеджеру группы (team manager). Проиллюстрируем сказанное примером. Таблицы member, charge и payment связываются по общему атрибуту member_no.

План выполнения запроса:

Здесь Hash Match Root соответствует менеджеру группы. Кроме того, из этого примера мы видим, что оптимизатор "понимает" транзитивные предикаты, т.е. что из m.member_no= ch.member_no и ch.member_no = p.member_no следует m.member_no = p.member_no. Поскольку при выполнении запроса ему оказалось выгоднее сначала связать таблицы member и payment, то он воспользовался свойством транзитивности.

Итак, в процессоре запросов SQL Server 7.0 интегрированы практически все современные технологии построения связей. Решение о том, какая из них будет выбрана в каждом конкретном случае, принимается оптимизатором, ибо не существует, как мы могли убедиться, универсальной стратегии, выигрышной всюду. В зависимости от обстоятельств любой из перечисленных алгоритмов может оказаться оптимальнее своих альтернатив. Например, для таблиц небольшого размера будет, скорее всего, использоваться вложенный цикл, так как оказывается дешевле просканировать каждый из входов, чем отводить память под хэш-таблицу. Кроме того, вложенный цикл - единственный приемлемый алгоритм для разрешения связи типа "больше-меньше", так как и merge, и hash join могут использоваться, когда предикат связи включает хотя бы один подходящий оператор равенства. В принципе, это не такое уж серьезное ограничение, поскольку одно из основных назначений join - реализация связей типа "первичный ключ - внешний ключ", где равенство всегда присутствует. Если входы достаточно велики и отсортированы по атрибуту связи (либо есть возможность быстро выполнить эту сортировку, либо результаты сортировки потом для чего-нибудь пригодятся и т.д.), наиболее оптимальным выбором представляется sort merge, в противном случае - вероятно, hash match. Широкий спектр доступных стратегий позволяет оптимизатору иметь наиболее эффективные инструменты построения связи для самых различных ситуаций. Еще одним преимуществом выступает возможность динамической адаптации (dynamic destaging). Очевидно, что оценка параметров итератора (например, количество записей на выходе) дается с той или иной степенью погрешности ([13]). В сложных планах с большим числом промежуточных этапов ошибка может накапливаться. Например, исходя из условий оценки оптимизатор считал возможным применить in-memory hash, но реальный размер build input не позволяет это сделать. При этом будут последовательно испробованы hybrid join, grace join или recursive hash join. Таким образом, в результате динамической адаптации плана ошибка оценки способна вызвать в худшем случае плавное снижение производительности (graceful degradation) в зависимости от величины расхождения.

1.5 Подсказки для Join

Внедрение перечисленных стратегий привело к пополнению подсказок оптимизатора ([2]). Инструкции с ключевыми словами LOOP, MERGE, HASH позволяют принудительно указать методику построения связи. Например, если мы слегка модифицируем запрос на Лист.1:

то в плане увидим, что вместо nested loop связь будет построена с помощью hash match. Если вместо hash поставить merge, то оптимизатор предложит выбрать из authors все записи, удовлетворяющие условию фильтрации, отсортировать их по au_id (по au_id существует индекс, но он некластерный) и связать с titleauthor, которая имеет кластерный индекс по (au_id, title_id). Среди запросных хинтов к теме данной главы относятся HASH | ORDER GROUP - оговаривает условия выполнения группирования: хэшированием или по-старому через stream-based сортировку; MERGE | HASH | CONCAT UNION - способ построения объединения; FORCE ORDER - действия, аналогичные упоминавшейся выше команде SET FORCEORDER, но только для данного запроса. Пример: по умолчанию оптимизатор намерен выполнять select au_lname from authors group by au_lname сканированием таблицы authors, сортировкой выходного потока и последующим агрегированием. Применение опции hash group:

заставляет оптимизатор сформировать хэш-таблицу и сгруппировать записи по совпадающим хэш-значениям. Поэтому в первом случае мы получим результат, упорядоченный по au_lname, а во втором - в порядке следования хэш-значений. Еще одно изменение в синтаксисе Transact-SQL касается количества таблиц в запросе, которое может теперь достигать 256 (по сравнению с 16 в предыдущей версии). Читателям, желающим убедиться в этом на собственной практике, предлагается следующий модельный скрипт:

В настольной (точнее, "наколенной") конфигурации с Pentium MMX 133 MHz и 80 Mb RAM запрос был отработан за 17 мин. Помимо SQL Server 7.0, были запущены SQL Profiler, Performance Monitor и MS Word.

1.6 Оптимизация OLAP-запросов

Возможность использования большего количества таблиц в запросе наряду с уже упоминавшимися характеристиками позволяет процессору запросов эффективно выполнять OLAP-запросы. Работа со звездными схемами или "снежинками" не требует какой-либо дополнительной адаптации кода по сравнению с OLTP-запросами. Обычно считается, что для операций над множествами мы должны иметь bitmap-индексы. Нам представляется, однако, что как и для других типов неуникальных некластерных индексов, это лишь вопрос формы представления множества RIDов, ассоциированных с ключом поиска. Вitmap-индексы представляют его (множество) в виде битовой карты, тогда как B-Tree индексы явно нумеруют всех членов каждого множества. Оба представления имеют свои слабые и сильные стороны, соответственно, существуют ситуации, в которых каждое может проявить себя наилучшим образом. Так, при работе с MOLAP-хранилищами в Microsoft OLAP Services for SQL Server широко применяются bitmap-индексы. Однако, говоря о ROLAP и SQL Server, еще раз подчеркнем, что все операции над битовыми индексами, также выполнимы для обычных индексов. Рассмотрим следующие два запроса вместе с их планами выполнения:

К сожалению, рассказ о работе с индексами в SQL Server 7.0 выведет нас далеко за рамки отведенного объема статьи, поэтому мы ограничимся вышеприведенными примерами для того, чтобы проиллюстрировать, как процессор запросов привлекает стратегию hash match для выполнения теоретико-множественных операций (в данном случае, соответственно, пересечение и объединение) над традиционными индексами B-Tree структуры. Заметим только, что в запросах вида select fld1, fld2 from tbl where fld1=... and fld2=... это избавляет нас от необходимости иметь самостоятельный покрывающий индекс по (fld1, fld2), так как при наличии отдельных индексов по fld1 и по fld2 SQL Server 7.0 построит его динамически.

Вернемся тем не менее к аналитическим запросам. Рассмотрим пример:

Как правило, таблица фактов много больше, чем каждая из таблиц таблиц измерений. В данном случае Sales_Fact _1997 имеет 86837 записей, а Store всего 24. Не имеет смысла два раза связывать измерение с массивной таблицей фактов. Гораздо дешевле будет построить два промежуточных множества, одно из которых отфильтровано по ограничению на store_city, другое по store_type, сделать из них декартово произведение (все равно оно получится небольшим по сравнению с таблицей фактов) и уже его связывать с Sales_Fact_1997. Именно так поступает SQL Server:

Рассмотрим другой запрос:

Временное измерение здесь выступает в роли ограничивающего фактора, данные из него возвращать не требуется, так что нецелесообразно строить связь между Time_By_Day и Sales_Fact_1997. Декартово произведение измерений в случае semi-join также не имеет смысла. Поэтому процессор запросов выполняет hash match между измерением Store и таблицей фактов, а промежуточные результаты пересекает с Time_By_Day:

Приведем еще один пример оптимизации при работе с хранилищами. Пусть наше хранилище содержит несколько таблиц фактов, каждая из которых хранит данные за определенный период времени, допустим, за год. Мы строим виртуальный куб в виде представления (view) как объединение разбиений по годам: create view All_Years_View as select * from Year1991 union all ... union all select * from Year1998. Тогда запрос select ... from All_Years_View where year=1997 приведет к обработке не всего представления в целом, а только отдельной таблицы, содержащей данные за выбранный год (Year1997). Для этого годовые ограничения должны быть явно заданы на таблицах в виде check: check(year=1991) и т.д.

В отличие от межзапросного параллелизма ([14]), означающего одновременное выполнение разных запросов на нескольких потоках (threads) операционной системы, внутризапросный (intraquery) параллелизм был реализован в SQL Server 7.0 впервые. Внутризапросный параллелизм, как следует из его названия, есть возможность распараллеливания процесса обработки одного запроса по нескольким потокам, что позволяет эффективно использовать многопроцессорные архитектуры при обработке сложных запросов. Использование внутризапросного параллелизма не требует специального разбиения данных при их хранении, а также внесения каких-либо изменений в их структуру или текст запроса. Процессор запросов рассматривает параллелизм наряду с другими этапами стратегии построения оптимального плана. Основными критериями при принятии решения о паралельном выполнении запроса выступают количество активных пользователей, доступная память и предположительный объем данных, обрабатываемых запросом. Очевидно, что параллельное выполнение простого запроса по сравнительно небольшому числу записей невыгодно, так как потребует больше памяти, нежели последовательное. При этом приходится забирать потоки, которые в противном случае могли бы быть использованы для поддержки большего числа пользователей. Общее правило можно сформулировать так: в OLTP-системах, характеризующихся большим количеством пользователей, обстреливающих SQL Server многочисленными короткими транзакциями, он будет отдавать предпочтение последовательным планам, расходуя поточный пул (см. опцию max worker threads) на пользовательские соединения. В OLAP-приложениях, для которых, напротив, характерны массивные долгоиграющие транзакции, а число пользователей относительно невелико, процессор запросов прибегнет к параллельным планам. Например, запрос
select * from sales_fact_1997 union select * from sales_fact_1998 (количество записей в таблице, как мы помним, 86837) выполняется по следующему плану:

Параллельное выполнение запроса достигается введением в план специальных операторов параллелизма, к которым относятся Distribute (произвести разбиение данных на несколько потоков (streams)), Gather (собрать результаты обработки данных c предыдущих шагов на нескольких потоках) и Repartition (перераспределить данные по потокам). Запросы на обновление (UPDATE / INSERT / DELETE) выполняются последовательно, однако подчитка данных в них может производиться в параллельном режиме. Специфика динамических курсоров предполагает строго последовательный план выполнения. В то же время для заполнения статических и keyset-курсоров может использоваться межзапросный параллелизм.

В смешанных приложениях может возникнуть необходимость провести количественную грань между понятиями простого и сложного запроса. Это делается с помощью конфигурационного параметра cost threshold for parallelism, который характеризует пороговую стоимость запроса, начиная с которой оптимизатор начинает рассматривать возможность использования параллельного плана выполнения. Запросы, чья стоимость не превышает пороговой величины, всегда будут выполняться последовательно. Значение этой опции по умолчанию равно 5. Ее также можно модифицировать из меню Server Properties (закладка Processor) в SQL Enterprise Manager.

Ключевым понятием параллельного выполнения является степень параллелизма (Degree of Parallelism, или DOP), иными словами, количество процессоров, которые будут задействованы для одновременной обработки запроса. Отметим, что эффект внутризапросного параллелизма будет проявляться только на машинах, где для работы SQL Server отведено два и более процессоров (см. опцию affinity mask ([1])). Для настройки DOP используется конфигурационный параметр max degree of parallelism, который может принимать значения от 0 до 32: 1 запрещает внутризапросный параллелизм, 0 (по умолчанию) означает, что при построении параллельных планов процессор запросов будет использовать максимально доступное на данный момент число процессоров. Количество потоков, на которых начинается выполнение запроса в параллельном режиме запрос, остается неизменным до момента его окончания. Вместе с тем, необходимо иметь в виду, что оптимальный план может изменяться в зависимости от конфигурации и загрузки SQL Server, так что тот же самый запрос через некоторое время может выполняться с другой DOP, в частности, последовательно. Просмотр DOP осуществляется при помощи соответствующего подкласса события в SQL Profiler.

Наряду с обычными потоками SQL Server 7.0 обладает возможностью использовать волокна (fibers) Windows NT - особый вид легковесных потоков, из которых может состоять thread. Легковесность заключается в особенностях планирования (scheduling). Переключение между потоками требует перехода в режим ядра операционной системы, что само по себе является довольно дорогостоящей операцией, в то время как переключение волокон происходит в контексте приложения. SQL Server использует волокна вместо потоков, если конфигурационный параметр lightweight pooling установлен в 1.

3.1 Универсальный доступ к данным

Рассмотрим типичную СУБД с позиций способа хранения информации и набора сервисов для ее обработки и выборки. Никто не мешает нам написать СОМ-сервер, инкапсулирующий основные сервисы по доступу и обработке данных, причем не обязательно реляционных, а, например, иерархических, таких как электронная почта, служба каталогов и т.д., или вообще неструктурированных. Как внутри него эти службы будут реализованы - личное дело СОМ-сервера, поскольку никто, кроме самих данных, не знает, как их лучше всего обрабатывать. Единственным требованием к нему будет поддержка стандартных интерфейсов, своего рода обязательство объекта обеспечивать декларируемый набор методов с описанными параметрами и типами возвращаемых значений. Это необходимо, чтобы клиентское приложение с помощью одинаковых методов могло одновременно работать с разными типами данных, не требуя переделки. Такой набор стандартных интерфейсов был разработан и получил название OLE DB ([7]). Наш СОМ-сервер в этой ситуации будет называться OLE DB-провайдером. Отталкиваясь от привычных аналогий, провайдер можно уподобить драйверу ODBC. Однако необходимо иметь в виду различия на прикладном и системном уровне. Первое - технология OLE DB нацелена на обеспечение доступа к данным любой природы, а не обязательно реляционным. Во вторых, ODBC - это набор Сшных функций. Если мы пишем приложение, скажем, на VB и хотим вместо RDO (Remote Data Objects - тонкая обертка над ODBC для придания более дружественного интерфейса) использовать ODBC в чистом виде, никаких проблем нет. Сделаем declare соответствующих функций - и вперед. OLE DB, как уже упоминалось, есть совокупность интерфейсов, построенных в соответствии с СОМ, поэтому они принципиально не достижимы из Automation-языков программирования. Для того, чтобы функциональность OLE DB была доступна из ASP, Visual Basic (VBScript, VBA), Visual FoxPro и др., была написана IDispatch-обертка вокруг OLE DB, получившая название ADO (ActiveX Data Objects). В ADO 1.5 была включена служба удаленного доступа (RDS), позволяющая располагать объекты на промежуточном слое между клиентом и сервером. RDS поддерживает прикладные протоколы HTTP и DCOM, то есть в качестве сервера приложений может использоваться Internet Information Server (IIS) или Microsoft Transaction Server (MTS). Компоненты ADO 2.0, входящие в состав Visual Studio 6, дополнены возможностями асинхронной обработки событий, локального кэша на клиенте, иерархического множества записей (с помощью провайдера MSDataShape) и элементами управления для поддержки ADODB.Recordset. Еще одно отличие заключается в том, что ODBC-драйвер для источника, не поддерживающего SQL (например, текстовый файл), должен нести в себе эквивалент SQL-машины, даже если клиенту не потребуется никакой другой функциональности, кроме простого множества записей. Нулевой уровень OLE DB предполагает лишь самые общие базовые возможности. Для их расширения применяются сервисные компоненты, которые могут реализовывать, например, процессор запросов, механизм поддержки курсоров и т.д. Так, механизм курсоров принимает rowset, который нулевой уровень поставляет в строго последовательном виде и дополняет его возможностями прокрутки в обоих направлениях. Нулевой уровень и сервисные компоненты под управлением SCM (Service Control Manager) образуют первый уровень. Таким образом, архитектура OLE DB включает провайдера, потребителя и слой сервисных компонент между ними.

За довольно короткий срок различными фирмами были написаны OLE DB-провайдеры для большинства распространенных серверов баз данных, настольных СУБД, ODBC, Active Directory, Index Server и др. Этот список продолжает пополняться (см., например, http://www.microsoft.com/data/oledb/products/product.htm). Многие из них вместе с примерами их использования распространяются вместе с Data Access SDK 2.0 (http://www.microsoft.com/data/download.htm). Там же можно найти инструментарий для написания собственного OLE DB-провайдера с помощью Visual C++, Visual J++, Visual Basic и т.д. (OLE DB Simple Provider Toolkit). Все они входят в OLE DB SDK 1.5. Расширение OLE DB for OLAP используется в качестве средства доступа к многомерной информации, например, к Microsoft OLAP Services, входящих в состав SQL Server 7.0. Более того, OLE DB является "родным" интерфейсом SQL Server 7.0, т.е. тем интерфейсом, посредством которого процессор запросов общается с механизмом хранения. DB-Library поддерживается путем эмуляции через OLE DB, но развиваться, по-видимому, в дальнейшем уже не будет. Механизм хранения SQL Server 7.0 представляется для процессора запросов просто источником данных, одним из многих OLE DB-достижимых, не более того. Если мы запустим sp_linkedservers, то увидим, что наш собственный сервер рассматривается процессором запросов как еще один присоединенный сервер. На момент написания этих строк в SQL Server 7.0 поддерживались следующие внешние источники: ODBC, MS Access и ISAM, AS/400 и VSAM, Oracle 7.3 и выше , MS Index Server , OLE DB for OLAP и, собственно, OLE DB-провайдер для SQL Server 7.0 (в ранних документах, посвященных тематике универсального доступа, может значиться как Luxor). Здесь необходимо отметить, что провайдер к SQL Server 7.0 можно использовать в версиях 6.х, предварительно запустив на них скрипт instcat.sql, находящийся в mssql7install. Этот скрипт, очевидно, не требуется запускать, если вы планируете осуществлять связь с предыдущими версиями через OLE DB поверх ODBC.

3.2 Работа с внешними источниками в SQL Server 7.0

В SQL Server 7.0 существуют два основных способа работы с удаленным источником: через прилинкованный сервер (linked server) и через имя, данное по ходу дела (ad hoc name). Прилинкованный сервер есть определяемый заранее виртуальный сервер, описание которого включает в себя информацию об OLE DB-провайдере данного источника и свойства соединения, указывающие провайдеру, где находится источник. Прилинкованные сервера являются единицами администрирования внешних источников данных. Они могут определяться как in-process (исполняющиеся в процессе SQL Server) или out-of-process (как самостоятельные exe-модули). Сервер in-process работает, очевидно, быстрее, однако его целесообразно выбирать для проверенных провайдеров. Несмотря на стандартный характер большинства свойств соединения, каждый провайдер обладает возможностью приема дополнительных значимых параметров (например, для геопространственных данных). Эти параметры также прописываются в прилинкованном сервере и называются атрибутами.

Предположим, в разных доменах у нас находятся два сервера: alexeysh_desk версии 7.0 и alexeysh_lapt версии 6.5. Если между доменами не установлены доверительные отношения, то named pipes, по умолчанию являющиеся основным прикладным протоколом взаимодействия, следует заменить на, скажем, TCP/IP Sockets. Для этого с помощью Client Network Utility следует добавить alexeysh_lapt в список серверов и указать, что конкретно для него мы выбираем сетевую библиотеку TCP/IP. Соединимся с alexeysh_lapt и запустим скрипт instcat.sql, который добавит новые типы и системные таблицы в SQL Server 6.5. Теперь мы можем определить его как прилинкованный сервер по отношению к alexeysh_desk. На соединении с alexeysh_desk выполним следующий скрипт:
sp_addlinkedserver @server= N"alexeysh_lapt", @srvproduct= "SQL Server"

Параметрами процедуры служат: @server - имя, под которым источник будет значиться в списке прилинкованных серверов и через которое мы будем к нему обращаться (N перед именем означает формат Unicode); @srvproduct - название продукта: например, если это SQL Server, то @srvproduct="SQL Server"; если Oracle, то "Oracle". Для Jet и ODBC это пустая строка. Указание @srvproduct в явном виде избавляет нас в данном случае от необходимости ввода других параметров. Аналогичные действия могли бы быть выполнены, например, так:

К другим параметрам относятся: @provider - кодовое обозначение провайдера (SQLOLEDB - SQL Server, MSDASQL - ODBC, MSDAORA - Oracle, Microsoft.Jet.OLEDB.3.51, Microsoft.Jet. OLEDB.4.0 - Access, MSIDXS - Index Server и т.д.); @datasrc - имя источника данных (для SQL Server это серверное имя линкуемого сервера, для Access - имя .mdb-файла с указанием полного пути, для ODBC - DSN и т.д.). Имя источника данных может быть также задано среди передаваемых провайдеру свойств. Например, вместо @datasrc="865/alexeysh_lapt", мы могли бы передать его через параметр @provstr:
sp_addlinkedserver @server=N"MySQL65", @srvproduct="", @provider="SQLOLEDB", @provstr="server=alexeysh_lapt;", @catalog="pubs"

Точно так же в случае ODBC мы можем передать DSN или строку соединения (для так называемого DSNless source). В частности, соединение с alexeysh_lapt может идти не напрямую через провайдера для SQL Server, а через провайдера для ODBC и ODBC-драйвер для SQL Server. Рассмотрим примеры. С помощью ODBC заведем системный DSN по имени ааа для драйвера SQL Server и сервера alexeysh_lapt. Протестируем его и убедимся в работе соединения. Тогда alexeysh_lapt может быть прилинкован как

В общем случае в @provstr оговаривается специфичная для провайдера информация, уникально идентифицирующая источник данных. Параметр @catalog cоответствует свойству DBPROP_INIT_CATALOG при инициализации OLE DB-провайдера. Для SQL Server - это активная база данных. Более подробное описание параметров процедуры sp_addlinkedserver можно найти в документации по Transact-SQL.

Когда прилинкованный сервер участвует в распределенном запросе, локальный SQL Server должен залогиниться на него под какой-то авторизующей информацией, например, от имени действующего пользователя. Отображение локальных логинов на удаленные осуществляется при помощи хранимой процедуры sp_addlinkedsrvlogin. Примеры:
sp_addlinkedsrvlogin @rmtsrvname= "MySQL65", @useself="true" - все пользователи alexeysh_desk будут ходить на alexeysh_lapt под их собственными именами и паролями на alexeysh_desk, т.е. преобразование отсутствует (@useself="true") .

sp_addlinkedsrvlogin @rmtsrvname= "MySQL65", @useself="false", @locallogin=NULL, @rmtuser= "sa", @rmtpassword="" - все логины alexeysh_desk (@locallogin=NULL) будут авторизовываться на alexeysh_lapt как sa с пустым паролем. Если в @rmtpassword указать какой-нибудь левый пароль, то при попытке обратиться к MySQL65 будет выдано сообщение login failed. При аутентификации пользователей SQL Server средствами Windows NT сервер alexeysh_desk попытается прикинуться для alexeysh_lapt NTвым пользователем. Способность SQL Server 7.0 эмулировать аутентифицирующую информацию пользователей Windows NT носит название делегирования и доступна только когда и локальный, и прилинкованный сервер работают под NT 5.0.

Обратная операция (удаление отображения локального логина) выполняется с помощью sp_droplinkedsrvlogin. Sp_dropserver @server= "MySQL65", @droplogins="droplogins" удаляет сервер из списка прилинкованных с одновременным удалением отображенных на него пользователей.

Проиллюстрируем распределенные запросы на примере. В качестве объекта эксперимента выберем модельную базу данных pubs, устанавливающуюся вместе с SQL Server. Экспортируем таблицу titles в Excel. В состав SQL Server 7.0 включены службы преобразования данных (Data Transformation Services), основным назначением которых является извлечение данных из операционных источников, их очистка, унификация, проверка на непротиворечивость и промежуточное агрегирование перед погружением их в хранилище. В среде Microsoft Datawarehousing Framework, обеспечивающей жизненный цикл хранилища, DTS управляют потоками данных и метаданных между MS SQL Server и многомерным кубом OLAP Server, но в принципе они могут работать с любыми OLE DB-источниками. Основной административной единицей DTS служит пакет (package). По своей идеологии пакет DTS родственен заданию (job) SQL Agent (бывший SQL Executive). Он состоит из шагов, логика выполнения которых может ветвится в зависимости от результата работы предыдущих шагов. Шаг соответствует элементарной операции DTS, например, создание таблицы, копирование данных и т.д. В качестве шагов могут использоваться операторы SQL, ActiveX-скрипты, исполняемые файлы (.exe), насосы данных (data pumps) или какие-то пользовательские действия в соответствии с интерфейсом IDTSCustomStep. Сами пакеты хранятся в MS Repository, SQL Server или в виде persistent СОМ-объектов. Поскольку нам требуется просто перекачать данные из SQL Server в другой формат, проще всего прибегнуть к DTS Wizard (контекстное меню таблицы в SQL Enterprise Manager -> task -> Export from SQL). Аналогично, перенесем таблицу sales в предварительно подготовленную базу данных fox_sales в Visual FoxPro 6.0, а таблицу stores - в базу access_stores в MS Access 97. Что у нас еще доступно? Текстовый файл. Давайте экспортируем таблицу publishers в publishers.txt. Ниже приведен файл schema.ini для текстового драйвера ODBC.

Создадим прилинкованные сервера для каждого из перечисленных источников. Таблицу Excel можно присоединять как через OLE DB-провайдера для ODBC:

(указание @catalog в явном виде, как мы помним, не является обязательным и может быть передано в свойствах провайдера:

@provstr="Driver={Microsoft Excel Driver (*.xls)}; DBQ=D:TEMPHetQueriestitles.XLS;"),

так и через OLE DB-провайдера для Jet:

В случае прилинковки через Jet, требуется задать отображение пользователя:

С текстовым файлом и таблицей FoxPro соединимся с помощью провайдера для ODBC:

а с базой данных в Access - через провайдера для Jet:

В случае ODBC перед нами везде пример соединения без создания DSN, когда вся необходимая информация (название драйвера, местоположение файла) тут же передается провайдеру. Если вид строки @provstr для какого-либо ODBC-драйвера заранее неизвестен, его легко выяснить экспериментально, временно создав соответствующий DSN и посмотрев, какие свойства передаются провайдеру при DSN-соединении. Допустим, мы не знаем, как должна выглядеть @provstr для ODBC-драйвера для Excel. Создадим на таблицу Excel DSN по имени ааа. Cледующий код

даст нам строку соединения

В ней следует обратить внимание на Extended Properties. Вместо DSN=aaa нужно поставить Driver={...}, в фигурных скобках ставится название ODBC-драйвера точно в таком виде, как оно значится в ODBC Data Source Administrator, закладка Drivers.

Механизм разрешения имен SQL Server 7.0 поддерживает названия, состоящие из 4-х частей: <имя прилинкованного сервера>.<каталог>.<схема>. <имя объекта>, например, MySQL65.pubs.dbo. authors. Некоторые провайдеры не требуют обязательного присутствия всех частей или имеют для них значения по умолчанию, такие части могут опускаться. Например, если мы прилинковываем Excel через провайдера для ODBC, то имя листа (Sheet) titles может выглядеть так: MyExcel97.[d:tempHetQueries titles]..[titles], если же через провайдера для Jet, то его можно указать в виде MyExcel97...[titles]. По имени прилинкованного сервера SQL Server на основе информации, прописанной в системные таблицы при его (прилинкованного сервера) создании, идентифицирует провайдера и отсылает ему оставшиеся три части имени. Этих сведений провайдеру должно быть достаточно, чтобы однозначно определить объект в источнике.

Запросы к прилинкованным серверам могут быть двух типов: с использованием имени из 4-х частей, либо сквозные (passthrough). В качестве примера с именами из 4-х частей преобразуем сообразно ситуации запрос на Лист.1.1.2

Можно его выполнить и убедиться, что результат будет в точности таким же, как и у его прототипа на Лист.1.1.2, где все данные хранились на локальном сервере.

Сквозные запросы создаются при помощи функции OpenQuery(). Первым аргументом этой функции выступает имя прилинкованного сервера, вторым - собственно текст запроса: select * from OpenQuery(Monarch, "select FileName from scope(""c:Program Files"")"). Запрос внутри OpenQuery() не проверяется и не анализируется SQL Server"ом, а напрямую передается прилинкованному источнику так, как есть. Отсутствие предобработки позволяет сэкономить время, но требует аккуратности при составлении запроса. Пример: запросы

дают один и тот же результат, однако во втором запросе проверку условия фильтрации выполняет SQL Server, а в первом - OLE DB-провайдер. Первый запрос следует признать более эффективным, поскольку в этом случае пересылать приходится меньшее количество данных.

Третий вариант построения распределенного запроса (ad hoc name) позволяет вообще обойтись без прилинкованного заранее сервера. Вызов функции OpenRowset подменяет обращение к таблице. В параметрах должна быть указана информация, позволяющая установить соединение с удаленным источником, и сама таблица в виде <каталог>.<схема>.<объект>, либо запрос. Пример:

При совместной работе с данными, относящимися как к SQL Server, так и к удаленным источникам следует иметь в виду, что внешние данные всегда доступны на чтение. Поддержка операций обновления внешних данных зависит от уровня обслуживающего их провайдера. Над входящими в тот же запрос данными, принадлежащими SQL Server, возможны любые допустимые операции. Следовательно, внешние данные всегда могут быть использованы для создания представлений и статических курсоров. Обновление внешних данных через представления или keyset-курсоров определяется возможностями провайдера. Динамические курсоры и операторы DDL над внешними данными не поддерживаются. Конвертация данных осуществляется путем приведения к ближайшему соответствующему типу, определенному в стандартах OLE DB. Сортировка выполняется в соответствии с порядком, заданным на локальном SQL Server. Участие внешних данных в транзакциях зависит от того, реализованы ли в провайдер интерфейсы поддержки транзакций. Например, если провайдер поддерживает интерфейс ITransactionLocal, но не поддерживает ITransactionJoin, данные удаленного источника можно включать в локальные транзакции, но они не смогут наследовать контекст внешней транзакции и, следовательно, не будут участвовать в распределенных транзакциях ([7]). Поддержка провайдером интерфейса IDBSchemaRowset позволяет SQL Server"у получать информацию о метаданных. Для этого можно использовать системные хранимые процедуры sp_catalogs, sp_tables_ex, sp_columns_ex, sp_table_privileges, sp_column_privileges, sp_primarykeys, sp_foreignkeys, sp_indexes и др. Если провайдер предоставляет информацию об имеющихся индексах, процессор запросов SQL Server сможет точнее оценить распределенный запрос и оптимизировать его выполнение. В плане запроса

выделенным шрифтом показан подзапрос, который в действительности SQL Server отсылает на сервер Oracle. Шаг Merge Join получает от провайдера Oracle отфильтрованные по qty и отсортированные по stor_id результаты запроса. Если бы таблица sales находилась, скажем, в виде текстового файла, то в плане вместо Remote Query стояло бы сканирование всей удаленной таблицы (выполняется провайдером) с последующими шагами фильтрации и сортировки (выполняется процессором запросов SQL Server).

3.3 Полнотекстовый поиск

Одним из частных примеров применения технологии универсального доступа может служить возможность полнотекстового поиска в SQL Server 7.0. Полнотекстовый провайдер (full-text provider) представляет собой промежуточное звено, посредством которого клиентское приложение взаимодействует со службой полнотекстового поиска (Microsoft Search Service). Служба полнотекстового поиска устанавливается как комопонент SQL Server 7.0 стандартной (Standard) и коропоративной (Enterprise) редакции и функционирует как сервис Windows NT. Настольная (Windows 9x) редакция SQL Server способна использовать полнотекстовый поиск подобно клиентам SQL Server, установившим соединение с сервером стандартной или корпоративной редакции. Служба полнотекстового поиска создает каталоги и полнотекстовые индексы. Каждая запись индекса содержит указатель на запись таблицы, слова, ассоциированные с этой записью за вычетом незначащих (noisy words), информацию о поле, которому они принадлежат, и месте их нахождения в этом поле. В качестве указателя записи используется первичный ключ (primary key) или кандидат (candidate). На таблицу можно создать не более, чем один полнотекстовый индекс. Каждый индекс находится в своем каталоге. База данных может иметь несколько индексных каталогов, но каждый каталог должен соответствовать только одной базе данных. Полнотекстовые индексы не допускаются над представлениями, а также системными или временными таблицами. Возможны два способа наполнения полнотекстовых индексов - Full Population (применяется при начальном создании индекса, либо при существенном изменении содержания индексированных полей) и Incremental Population. Последний доступен для таблиц, имеющих поле timestamp. Изменение содержания индексированных полей не влечет за собой немедленной поправки полнотекстового индекса, так как последние довольно объемны и при их постоянной модификации происходила бы заметная задержка. Вместо этого можно оформить Incremental Population как задание (job), выполняющееся с некоторой периодичностью с помощью SQL Agent. Каталог является минимальной единицей обновления полнотекстовых индексов. Кроме того, в функции службы полнотекстового поиска входит обработка специальных конструктов в запросах (предикаты CONTAINS и FREETEXT), с помощью которых осуществляется поиск отдельных слов и фраз, учет расстояния между словами (NEAR), распознавание словоформ (FORMSOF) и взвешивание по значимости (ISABOUT). В качестве примера создадим таблицу с полем pgh типа text, в каждую запись которой положим отдельный абзац этой статьи. Это можно сделать с помощью макроса:

Таблица готова. Ассоциируем список незначащих слов (noisy words) с файлом noise.dat:

Делаем текущую базу данных доступной для полнотекстового поиска:
exec sp_fulltext_database @action="enable"

Создаем новый полнотекстовый каталог SQLPaper по заданному пути:
exec sp_fulltext_catalog @ftcat= "SQLPaper", @action="create", @path="d:mssql7b3_datFTData"

Создаем метаданные полнотекстового индекса и указываем, что идентификация записей в таблице MySQLPaper должна происходить по ключу PK_MySQLPaper:
exec sp_fulltext_table @tabname="MySQLPaper", @action= "create", @ftcat="SQLPaper", @keyname="PK_MySQLPaper"

Добавляем поле pgh как одно из тех, по которому будет происходить полнотекстовый поиск (индекс пока неактивен):

Регистрируем таблицу в каталоге полнотекстового поиска файловой системы:
exec sp_fulltext_table @tabname="MySQLPaper", @action="activate"

Все вышеперечисленные действия интерактивно удобнее выполнять с помощью программы-мастера полнотекстовых индексов, которая создает каталог и структуру индекса. Нам остается лишь наполнить индексы в данном каталоге:
exec sp_fulltext_catalog @ftcat="SQLPaper", @action="start_full"

После чего к таблице можно обращаться с запросами типа:
select id, pgh from MySQLPaper where contains(pgh, ""полнотекст*" near "поиск"")

Из плана выполнения следует, что процессор запросов SQL Server проверяет, определен ли на поле pgh полнотекстовый индекс и преобразует оператор SQL в последовательность действий над множеством записей. Обычно эти действия через OLE DB-провайдера передаются механизму хранения SQL Server. Однако действия, относящиеся к контекстному поиску, передаются OLE DB-провайдеру службы полнотекстового поиска. Эта служба осуществляет поиск по своим каталогам и содержащимся в них полнотекстовым индексам и возвращает набор указателей на записи, отвечающим условию поиска. Процессор запросов комбинирует его с наборами, полученными от других провайдеров, в частности, механизма хранения и строит окончательное множество результатов, которое возвращается клиентскому приложению. Каталоги и индексы, находящиеся под управлением службы полнотекстового поиска, не хранятся в базе данных SQL Server, следовательно, их не охватывают операции резервного копирования и восстановления. Подробнее об архитектуре и синтаксических конструкциях полнотекстового поиска можно узнать, обратившись к документации.

Служба полнотекстового поиска, входящая в состав SQL Server 7.0 обеспечивает поиск по строковым и текстовым полям (в том числе Unicode). Контекстный поиск по документам файловой системы или опубликованным на Web-сервере осуществляется с помощью индексных механизмов Microsoft Index Server или Microsoft Site Server и рассмотренной нами технологии гетерогенных запросов (например, через прилинкованные сервера). Для доступа к этим индексам используются OLE DB-провайдеры (соответственно, для Index Server или Site Server), так что описанная выше схема полнотекстового поиска сохраняется с точностью до провайдера. Базовые синтаксические расширения полнотекстового поиска в Transact-SQL одинаковы и поддерживаются каждым из трех упомянутых провайдеров.

1. Byrne, Jeffry L. "Microsoft SQL Server: What Database Administrators Need To Know", Prentice Hall, 1997, ISBN 0-13-495409-2. (Джеффри Л.Бирн. "Microsoft SQL Server 6.5. Руководство администратора". Лори, 1997)

2. Debetta, P. "Microsoft SQL Server 6.5 Programming Unleashed". SAMS Publishing, 1998, ISBN 0-67231-244-1

3. England, Ken. "The SQL Server 6.5 Performance Optimization and Tuning Handbook". Digital Press, ISBN 1-5558-180-3

4. Fushimi, Sh., Kitsuregawa, M., Tanaka, H. "An Overview of The System Software of A Parallel Relational Database Machine GRACE". VLDB Conf. 1986: 209-219

5. Graefe, G., Bunker, R., Cooper S. "Hash joins and hash teams in Microsoft SQL Server". VLDB Conf., 1998

6. Graefe G. "Query Evaluation Techniques for Large Databases". ACM Computing Surveys 25(2): 73-170 (1993).

7. "Microsoft OLE DB 1.1 Programmer"s Reference and Software Development Kit". Microsoft Press, 1997, ISBN 1-57231-612-8. ("Справочник по Microsoft OLE DB 1.1". Русская Редакция, 1997)

8. Rankins, R.; Solomon, D. "Microsoft SQL Server 6.5 Unleashed". SAMS Publishing, 1998, ISBN 0-672-31190-9. (Д.Соломон и др. "Microsoft SQL Server 6.5. Энциклопедия пользователя". Диасофт, 1998)

9. Schneider, Robert D. "Microsoft SQL Server: Planning and Building a High Performance Database". Prentice Hall, 1997, ISBN 0-13-266222-1. (Роберт Д.Шнайдер. "Microsoft SQL Server. Проектирование высокопроизводительных баз данных". Лори, 1997)

10. Soukup, Ron. "Inside Microsoft SQL Server 6.5". Microsoft Press, 1997, ISBN 1-57231-331-5.

11. Spenik, M., Sledge, O. "Microsoft SQL Server 6.5 DBA Survival Guide". SAMS Publishing, 1996, ISBN 0-672-30959-9

12. Vaughn, William R.. "Hitchhiker"s Guide to Visual Basic and SQL Server". Microsoft Press, 1998, ISBN 1-57231-848-1

13. Zeller, H., Gray J. "An Adaptive Hash Join Algorithm for Multiuser Environments". VLDB Conf. 1990: 186-197

14. Горев А., Макашарипов С., Владимиров Ю. "Microsoft SQL Server 6.5 для профессионалов". Изд-во "Питер", 1997, ISBN 5-88782-427-1

15. Макашарипов С. "Программирование баз данных на Visual Basic 5 в примерах". Изд-во "Питер", 1997, ISBN 5-88782-315-1