Процесор запитів Microsoft SQL Server, MS SQL Server, Бази даних, статті

Про деякі стратегіях оптимізатора при побудові складних, паралельних і розподілених планів

А. Шуленін, Журнал "СУБД"

Введення

Напевно, не буде великим перебільшенням сказати, що процесор запитів (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.1 Nested Loop

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



set showplan_text on

go

select * from authors a inner join titleauthor ta on a.au_id = ta.au_id

where a.au_lname like “R%”

go

set showplan_text off

go

План виглядає наступним чином:



StmtText

————————

|-Nested Loops(Inner Join)

|-Bookmark Lookup(Bmk1000 IN pubs..authors AS a)

|–Filter(like(a.au_lname, “R%”))

|—Index Seek(pubs..authors. aunmind AS a,

SEEK: (a.au_lname> = "QЯ" AND a.au_lname < "s") ORDERED) | - Clustered Index Seek (pubs.. Titleauthor.UPKCL_taind AS ta, SEEK: (ta.au_id = a.au_id) ORDERED)Ліст.1.1.1

Вкладений цикл був практично єдиною стратегією реалізації зв'язків в попередніх версіях продукту ([9]). Як випливає з назви стратегії, побудова зв'язку здійснюється за наступним алгоритмом: для кожного запису таблиці authors пробігти таблицю titleauthor, вибравши з неї всі записи, які можна зіставити поточного запису таблиці authors. У цієї термінології таблиця, відповідна внутрішньому циклу (titleauthor), називається внутрішньою, відповідна зовнішнього циклу (authors) – зовнішній. Алгоритм можна охарактеризувати як метод грубої сили, оскільки за відсутності відповідних індексів внутрішня таблиця повинна проглядатися стільки разів, скільки записів у зовнішній. Якщо, як у нашому прикладі, які підходять індекси існують, nested loop об'єднується з пошуком по індексу (index lookup). Вартість зв'язку обчислюється як кількість сторінок у зовнішній таблиці + кількість записів, що задовольняють можливого умові фільтрації у зовнішній таблиці * кількість сторінок, які необхідно прочитати за один пошук у внутрішній таблиці. Бажаючі дізнатися, як вважається кількість сторінок при пошуку запису, що задовольняє певній умові, якщо ця умова є 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! / (nk)! – Число розміщень з n по k. При k <<n ця сума буде істотно менше, ніж n!. Зокрема, для n = 32, k = 4 на 28 порядків. Включення прапора трасування 345 дає оптимізатору SQL Server 6.5 вказівку розглядати по 6 таблиць одночасно, що призводить до збільшення часу компіляції, але підвищує точність вибору оптимального порядку. Зазвичай це використовувалося для виконання TPC-D запитів.

Розглянемо наступний запит:



select a.au_fname, a.au_lname, t.title, p.pub_name, s.qty, st.stor_name

from authors a inner join titleauthor ta on a.au_id = ta.au_id

inner join titleauthor t on ta.title_id=t.title_id

inner join publishers p on t. pub_id=p.pub_id

inner join sales s on s.title_id=t.title_id

inner join stores st on s.stor_id=st.stor_id

StmtText

————————

|-Nested Loops(Inner Join)

|–Nested Loops(Inner Join)

|—Bookmark Lookup(BOOKMARK: ([Bmk1004]),

OBJECT:([pubs].[dbo]. [sales] AS [s]) WITH PREFETCH)

—–Nested Loops(Inner Join)

——Nested Loops(Inner Join)

——-Nested Loops(Inner Join)

——– Index ScaBJECT: ([pubs]. [Dbo]. [Authors]. [Aunmind] AS [a]))

——– Index Seek (OBJECT: ([pubs]. [Dbo]. [Titleauthor]. [Auidind] AS [ta]),

SEEK:([ta].[au_id]=[a]. [au_id]) ORDERED)

——- Clustered Index Seek (OBJECT: ([pubs]. [Dbo]. [Titles]. [UPKCL_titleidind] AS [t]),

SEEK:([t]. [title_id]=[ta].[title_id]) ORDERED)

—— Index Seek (OBJECT: ([pubs]. [Dbo]. [Sales]. [Titleidind] AS [s]),

SEEK:([s].[title_id]=[t]. [title_id]) ORDERED)

—- Clustered Index Seek (OBJECT: ([pubs]. [Dbo]. [Publishers]. [UPKCL_pubind] AS [p]),

SEEK:([p]. [pub_id]=[t].[pub_id]) ORDERED)

—Clustered Index Seek(OBJECT: ([pubs].[dbo].[stores].

[UPK_storieid] AS [st]), SEEK: ([st]. [Stor_id] = [s]. [Stor_id]) ORDERED)

Ліст.1.1.2

Ми бачимо, що порядок побудови зв'язків, обраний оптимізатором, має вигляд: 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. Розглянемо приклад:



select m.lastname, m.firstname, ch.charge_dt, ch.charge_amt

from member m, charge ch where m.member_no=ch.member_no

order by ch.member_no


Таблиця member складається з 10000 записів, charge – з 100000. План виконання запиту:



StmtText

———————————-

|-Merge Join (Inner Join, MANY-TO-MANY MERGE: ch.member_no) = (m.member_no)

RESIDUAL:(ch.member_no=m. member_no))

|-Sort(ORDER BY: (ch.member_no asc))

| |-Clustered Index Scan (Credit.. Charge.IX_charge AS ch)

|-Clustered Index Scan (Credit.. Member.IX_member AS m, ORDERED)

Ліст.1.2.1

Зверніть увагу на те, що таблиці member і charge мають кластерні індекси. У цьому полягає особливість merge join. Її дуже вигідно використовувати, коли всі входи (зв'язуються таблиці в запиті) фізично розсортовані по атрибутам зв'язку. Нехай нам потрібно зав'язати відношенням "багато-до-багатьох" таблиці Т1 по атрибуту fld1 і Т2 по атрибуту fld2. Псевдокод алгоритму merge join при побудові відносини виглядає приблизно так.



while not T1.eof and not T2.eof do begin

attr=T1.CurrentRecord.fld1;

while T2.CurrentRecord.fld2 <Attr do T2.MoveToNextRecord (); t1 = T1.CurrentRecord.RowID (); while T2.CurrentRecord.fld2 = attr while T1.CurrentRecord.fld1 = attr do begin <пара T1.CurrentRecord * T2.CurrentRecord задовольняє умові зв'язку; додати її в результат>;

T1.MoveToNextRecord();

end while

T1.MoveTo(t1);

T2.MoveToNextRecord();

end while

end while


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

1.3 Hash Join

Продовжимо роботу з нашою базою. Зробимо індекси за таблицями member і charge некластерного і відправимо запит:



select m.lastname, m.firstname, ch.charge_dt, ch.charge_amt,

ca.category_desc

from member m, charge ch, category ca

where m.member_no = ch.member_no and ch.category_no = ca.category_no

SQL Server виконує його за таким планом:

StmtText

———————————-

|-Hash Match(Inner Join, HASH:(ca.category_no)=

(ch.category_no))

|-Table Scan(Credit..

category AS ca)

|-Hash Match (Inner Join, HASH: (m.member_no) = (ch.member_no))

|-Table

Scan(Credit..member AS m)

|-Table

Scan(Credit..charge AS ch)

Ліст.1.3.1

Хід міркувань процесора запитів виглядає приблизно так. Так, входи здорові, це погано, 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.



select m.lastname, m.firstname, ch.charge_dt, ch.charge_amt, p.payment_dt,

p.payment_amt

from member m, charge ch, payme p

where m.member_no = ch.member_no and ch.member_no = p.member_no

План виконання запиту:



StmtText

———————————-

|-Hash Match Root (Inner Join, HASH: (p.member_no) = (ch.member_no))

|-Hash Match Team (Inner Join, HASH: (m.member_no) = (p.member_no))

| |-Clustered Index Scan (credit.. Member.PK_member AS m)

| |-Clustered Index Scan (credit.. Payment.PK_payment AS p)

|-Table Scan(credit..charge AS ch)

Ліст.1.4.1

Тут 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:

select * from authors a inner hash join titleauthor ta on a.au_id = ta.au_id where a.au_lname like "R%"

то в плані побачимо, що замість 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:



select au_lname from authors group by au_lname option (hash group)

StmtText

———————————-

|-Hash Match (Aggregate, HASH: (authors.au_lname) RESIDUAL: (authors.au_lname = authors. Au_lname))

|-Index Scan(pubs..authors.aunmind)

Ліст.1.5.1

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



Dim adoCnn As ADODB.Connection

Set adoCnn = CreateObject(“ADODB.Connection”)

adoCnn.Provider = “SQLOLEDB”

adoCnn.Properties ("Data Source") = "./020920111200005468.html"

adoCnn.Properties(“User ID”).Value = “sa

“adoCnn.Properties(“Password”). Value = “”

adoCnn.Properties(“Initial Catalog”) = “pubs”

adoCnn.Open

Dim i As Integer, n As Integer

n = 256

For i = 1 To n

adoCnn.Execute ("select * into authors" & CStr (i) & "from authors")

Next

If adoCnn.State <> adStateOpen Then Exit Sub

Dim cStmt(2) As String

cStmt(0) = “select a1.au_lname”

cStmt(1) = “from authors1 a1”

cStmt(2) = “where ”

For i = 2 To n

cStmt(0) = cStmt(0) & “, a” & CStr(i) & “.au_lname”

cStmt (1) = cStmt (1) & ", authors" & CStr (i) & "a" & CStr (i)

cStmt (2) = cStmt (2) & "a1.au_id = a" & CStr (i) & ". au_id and"

Next

cStmt(2) = Left(cStmt(2), Len(cStmt(2)) – 5)

Debug.Print Len (cStmt (0) & "" & cStmt (1) & "" & cStmt (2))

Dim adoRS As ADODB.Recordset

Debug.Print cStmt(0) & ” ” & cStmt(1) & ” ” & cStmt(2)

adoCnn.CommandTimeout = 3600

Dim t As Date

t = Now()

Set adoRS = adoCnn.Execute (cStmt (0) & "" & cStmt (1) & "" & cStmt (2))

Debug.Print Format(Now() – t, “hh:mm:ss”)

Debug.Print adoRS.GetString(adClipString, -1, , , “”)

For i = 1 To n

adoCnn.Execute (“drop table authors” & CStr(i))

Next

adoCnn.Close

У настільної (точніше, "наколінний") конфігурації з 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, ще раз підкреслимо, що всі операції над бітовими індексами, також здійсненні для звичайних індексів. Розглянемо наступні два запити разом з їх планами виконання:



select * from sales_fact_1997 where product_id between 300 and 302

and customer_id between 1000 and 1002

———————————-

|-Bookmark Lookup (BOOKMARK: ([Bmk1000]), OBJECT: ([FoodMart]. [Dbo]. [Sales_fact_1997]))

|-Hash Match (Inner Join, HASH: ([Bmk1000 ])=([ Bmk1000]), RESIDUAL: ([Bmk1000] = [Bmk1000]))

|-Index Seek(OBJECT:

([FoodMart]. [Dbo]. [Sales_fact_1997]. [IX_sales_fact_1997_2]), SEEK: ([sales_fact_1997].

[customer_id] BETWEEN 1000 AND 1002) ORDERED)

|-Index Seek(OBJECT:

([FoodMart]. [Dbo]. [Sales_fact_1997]. [IX_sales_fact_1997]),

SEEK: ([sales_fact_1997]. [Product_id] BETWEEN 300 AND 302) ORDERED)

і

select * from sales_fact_1997 where product_id between 300

and 302 or customer_id between 1000 and 1002

———————————-

|-Bookmark Lookup(BOOKMARK:([Bmk1000]),

OBJECT: ([FoodMart]. [Dbo]. [Sales_fact_1997]) WITH PREFETCH)

|-Hash Match(Union)

|-Index Seek(OBJECT:

([FoodMart]. [Dbo]. [Sales_fact_1997]. [IX_sales_fact_1997]),

SEEK: ([sales_fact_1997]. [Product_id] BETWEEN 300 AND 302) ORDERED)

|-Index Seek(OBJECT:

([FoodMart]. [Dbo]. [Sales_fact_1997]. [IX_sales_fact_1997_2]),

SEEK: ([sales_fact_1997]. [Customer_id] BETWEEN 1000 AND 1002) ORDERED)

Ліст.1.6.1

На жаль, розповідь про роботу з індексами в 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 побудує його динамічно.

Повернемося проте до аналітичних запитам. Розглянемо приклад:



select f.unit_sales, s.store_name from sales_fact_1997 f, store s, store s1

where f.store_id = s.store_id and f.store_id = s1.store_id and

s.store_city = "Seattle" and s1.store_type = "Supermarket".

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



———————————-

|-Hash Match (Inner Join, HASH: (s1.store_id) = (f.store_id) RESIDUAL: (f.store_id = s1.store_id))

|-Nested Loops(Inner Join)

| |-Clustered Index Scan(FoodMart..store.PK_store AS s,

WHERE:(s.store_city=”Seattle”)ORDERED)

| |-Clustered Index Seek (FoodMart.. Store.PK_store AS s1, SEEK: (s1.store_id = s.store_id),

WHERE:(s1.store_type=”Supermarket”) ORDERED)

|-Table Scan(FoodMart..sales_fact_1997 As f)

Розглянемо інший запит:



select f.unit_sales, s.store_name from sales_fact_1997 f, store s, time_by_day t

where f.store_id = s.store_id and f.time_id = t.time_id and

s.store_city = “Seattle” and t.the_month = “May”

Ліст.1.6.2

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



———————————-

|-Hash Match (Inner Join, HASH: (t.time_id) = (f.time_id) RESIDUAL: (f.time_id = t.time_id))

|-Clustered Index Scan

(FoodMart.. Time_by_day. PK_time_by_day AS t, WHERE: (t.the_month = "May"))

|-Hash Match (Inner Join, HASH: (s.store_id) = (f.store_id) RESIDUAL: (f.store_id = s.store_id))

|-Clustered Index Scan

(FoodMart.. Store.PK_store AS s, WHERE: (s.store_city = "Seattle"))

|-Table Scan(FoodMart..sales_fact_1997 AS f)

Ліст.1.6.3

Наведемо ще один приклад оптимізації при роботі зі сховищами. Нехай наше сховище містить кілька таблиць фактів, кожна з яких зберігає дані за певний період часу, скажімо, за рік. Ми будуємо віртуальний куб у вигляді подання (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) і т.д.

2. Внутрізапросний паралелізм

На відміну від межзапросного паралелізму ([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) виконується за таким планом:



|-Parallelism(Gather Streams)

|-Hash Match(Union)

|-Parallelism(Repartition Streams, PARTITIONCOLUMNS:

(Sales_fact_1997.product_id, sales_fact_1997.time_id, sales_fact_1997.customer_id,

sales_fact_1997.promotion_id, sales_fact_1997.store_id, sales_fact_1997.store_sales,

sales_fact_1997.store_

| |-Table Scan(FoodMart..sales_fact_1997)

|-Sort (DISTINCT ORDER BY: (sales_fact_1998.product_id asc, sales_fact_1998.time_id asc,

sales_fact_1998.customer_id asc, sales_fact_1998.promotion_id asc,

sales_fact_1998.store_id asc, sales_fact_1998.store_sales asc,

sales_fact_1998.store_cos

|-Parallelism (Repartition Streams, PARTITIONCOLUMNS: (sales_fact_1998.product_id,

sales_fact_1998.time_id, sales_fact_1998.customer_id, sales_fact_1998.promotion_id,

sales_fact_1998.store_id, sales_fact_1998.store_sales, sales_fact_1998.s

|-Table Scan(FoodMart..sales_fact_1998)

Ліст.2.1

Паралельне виконання запиту досягається введенням в план спеціальних операторів паралелізму, до яких відносяться 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. Розподілені та гетерогенні запити

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 і ін Цей список продовжує поповнюватися (див., наприклад, www.microsoft.com/data/oledb/products/product.htm). Багато хто з них разом з прикладами їх використання поширюються разом з Data Access SDK 2.0 (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, що знаходиться в mssql7 \ install. Цей скрипт, очевидно, не потрібно запускати, якщо ви плануєте здійснювати зв'язок з попередніми версіями через 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 "./020920111200005468.html", @ srvproduct = "SQL Server"

Параметрами процедури служать: @ server – ім'я, під яким джерело буде значитися в списку прілінкованних серверів і через яке ми будемо до нього звертатися (N перед ім'ям означає формат Unicode); @ srvproduct – Назва продукту: наприклад, якщо це SQL Server, то @ srvproduct = "SQL Server"; якщо Oracle, то "Oracle". Для Jet і ODBC це порожній рядок. Вказівка ​​@ srvproduct в явному вигляді позбавляє нас в даному випадку від необхідності введення інших параметрів. Аналогічні дії могли б бути виконані, наприклад, так:



sp_addlinkedserver @ server = "MySQL65", @ srvproduct = "", @ provider = "SQLOLEDB",

@datasrc=”http://easy-code.com.ua/img/020920111200005468.html”, @catalog=”pubs”

До інших параметрів належать: @ 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 = "./020920111200005468.html", ми могли б передати його через параметр @ 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 може бути прілінкован як



sp_addlinkedserver @server=”MySQL65ODBC”,

@ Srvproduct = "", @ provider = "MSDASQL", @ datasrc = "./020920111200005469.html"

або те ж саме без створення DSN:

sp_addlinkedserver @server=”MySQL65ODBC”,

@ Srvproduct = "", @ provider = "MSDASQL", @ provstr = "driver = {SQL Server};

server=alexeysh_lapt;uid=sa;pwd=;

database=pubs”

У загальному випадку в @ provstr обмовляється специфічна для провайдера інформація, унікально ідентифікує джерело даних. Параметр @ catalog Відповідне властивості 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.



[publishers.txt]

ColNameHeader=False

Format=FixedLength

MaxScanRows=25

CharacterSet=OEM

Col1=PUB_ID Char Width 4

Col2=PUB_NAME Char Width 40

Col3=CITY Char Width 20

Col4=STATE Char Width 2

Col5=COUNTRY Char Width 30


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



if exists (select srvname from master.dbo.sysservers where srvname = "MyExcel97")

exec sp_dropserver @ server = "MyExcel97", @ droplogins = "droplogins"

go

exec sp_addlinkedserver @ server = "MyExcel97", @ srvproduct = "", @ provider = "MSDASQL.1",

@ Provstr = "Driver = {Microsoft Excel Driver (*. xls )};",catalog =" d: \ temp \ HetQueries \ titles.xls "


(Вказівка ​​@ catalog в явному вигляді, як ми пам'ятаємо, не є обов'язковим і може бути передано у властивостях провайдера:

@ Provstr = "Driver = {Microsoft Excel Driver (*. xls)}; DBQ = D: \ TEMP \ HetQueries \ titles.XLS;"),

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



exec sp_addlinkedserver

@server=”MyExcel97″,

@ Srvproduct = "Jet 4.0", @ provider = "Microsoft.Jet.OLEDB.4.0",

@ Datasrc = "d: \ temp \ HetQueries \ Titles.xls", @ location = NULL, @ provstr = "Excel 5.0;"

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



exec sp_addlinkedsrvlogin "MyExcel97", false, sa, "Admin", NULL

З текстовим файлом і таблицею FoxPro з'єднаємося з допомогою провайдера для ODBC:



exec sp_addlinkedserver

@server=”MyText”, @srvproduct=””, @provider=”MSDASQL.1″,

@provstr=”Driver={Microsoft Text Driver (*.txt; *.csv)};

DefaultDir=D:\TEMP\HetQueries”

exec sp_addlinkedserver

@server=”MyVFP6″, @srvproduct=””, @provider=”MSDASQL.1″,

@provstr=”Driver={Microsoft Visual FoxPro Driver};

UID =; PWD =; SourceDB = D: \ TEMP \ HetQueries \ fox_sales.dbc; SourceType = DBC; Exclusive = No;

BackgroundFetch=Yes;Collate=Machine;”


а з базою даних в Access – через провайдера для Jet:



exec sp_addlinkedserver

@server=”MyAccess97″,

@ Srvproduct = "Access 97", @ provider = "Microsoft.Jet.OLEDB.4.0",

@datasrc=”d:\temp\HetQueries\Access_stores.mdb”

У разі ODBC перед нами скрізь приклад з'єднання без створення DSN, коли вся необхідна інформація (назва драйвера, місце розташування файлу) тут же передається провайдеру. Якщо вид рядка @ provstr для будь-якого ODBC-драйвера заздалегідь невідомий, його легко з'ясувати експериментально, тимчасово створивши відповідний DSN і подивившись, які властивості передаються провайдеру при DSN-з'єднанні. Припустимо, ми не знаємо, як повинна виглядати @ provstr для ODBC-драйвера для Excel. Створимо на таблицю Excel DSN на ім'я ааа. Наступний код



Dim cnn As New ADODB.Connection

cnn.Provider = “MSDASQL”

cnn.Open (“DSN=aaa”)

Debug.Print cnn.ConnectionString

cnn.Close

дасть нам рядок з'єднання



Provider = MSDASQL.1; Connect Timeout = 15; Extended Properties = "DSN = aaa;

DBQ = D: \ TEMP \ HetQueries \ titles.XLS; DefaultDir = D: \ TEMP \ HetQueries;

DriverId = 790; FIL = excel 5.0; MaxBufferSize = 512; PageTimeout = 5; "; Locale Identifier = 1049

У ній слід звернути увагу на 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: \ temp \ HetQueries \ titles] .. [titles], якщо ж через провайдера для Jet, то його може мати вигляд MyExcel97 … [ titles]. На ім'я прілінкованного сервера SQL Server на основі інформації, прописаної в системні таблиці за його (прілінкованного сервера) створення, ідентифікує провайдера і відсилає йому залишилися три частини імені. Цих відомостей провайдеру має бути достатньо, щоб однозначно визначити об'єкт в джерелі.

Запити до прілінкованним серверів можуть бути двох типів: з використанням імені з 4-х частин, або наскрізні (passthrough). Як приклад з іменами з 4-х частин перетворимо по ситуації запит на Ліст.1.1.2



select a.au_fname, a.au_lname, t.title, p.pub_name, s.qty, st.stor_name from authors a

inner join MySQL65.pubs.dbo.titleauthor ta on a.au_id = ta.au_id

inner join MyExcel97 … [titles] t on ta.title_id = t.title_id

inner join MyText. [D: \ TEMP \ HetQueries] .. [publishers.txt] p on t.pub_id = p.pub_id

inner join MyVFP6. [fox_sales] .. [sales] s on s.title_id = t.title_id

inner join MyAccess97 … [stores] st on s.stor_id = st.stor_id

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

Наскрізні запити створюються за допомогою функції OpenQuery (). Першим аргументом цієї функції виступає ім'я прілінкованного сервера, другим – власне текст запиту: select * from OpenQuery (Monarch, "select FileName from scope ("" c: \ Program Files "")"). Запит всередині OpenQuery () не перевіряється і не аналізується SQL Server "ом, а безпосередньо передається прілінкованному джерела так, як є. Відсутність передобробки дозволяє заощадити час, але вимагає акуратності при складанні запиту. Приклад: запити



select * from OpenQuery (MyExcel97, "select * from titles where type =" "business" "") і

select * from OpenQuery (MyExcel97, "select * from titles") where type = "business"

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

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



select * from OpenRowset ("MSDASQL", "Driver = {Microsoft Excel Driver (*. xls)};

DBQ = D: \ TEMP \ HetQueries \ titles.XLS; "," select * from titles where type = "" business "" ")


При спільній роботі з даними, що відносяться як до 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 зможе точніше оцінити розподілений запит і оптимізувати його виконання. У плані запиту



select st.stor_name, st.city, s.ord_num, s.qty from stores st,

MyOracle .. klm.sales s where s.stor_id = st.stor_id and s.qty> 50

—————-

|-Merge Join(Inner Join,

MANY-TO-MANY MERGE:(s.stor_id)= (Expr1002)

ESIDUAL:(s.stor_id=Convert(st.stor_id)))

|-Remote Query(SELECT `s`. `Ord_num` AS Col1007, `s`. `Qty` AS Col1008, `s`. `Stor_id`

AS Col1006 FROM `sales` s WHERE `s`. `Qty`> (50) ORDER BY `s`. `Stor_id` ASC)

|-Sort(ORDER BY: (Expr1002 asc))

|-Compute Scalar(Expr1002=Convert(st.stor_id))

|-Clustered Index Scan (pubs.. Stores.UPK_storeid AS st)

виділеним шрифтом зображений підзапит, який насправді 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, в кожен запис якої покладемо окремий абзац цієї статті. Це можна зробити за допомогою макросу:



Sub Macro1()

Dim cnn As Object

Set cnn = CreateObject(“ADODB. Connection”)

Dim rst As Object

Set rst = CreateObject(“ADODB.Recordset”)

With cnn

. Open "Provider = SQLOLEDB; Data Source = alexeysh_desk; User ID = sa; Password =;

Initial Catalog=pubs”

. Execute "CREATE TABLE MySQLPaper (id int IDENTITY (1, 1) CONSTRAINT

[PK_MySQLPaper] PRIMARY KEY NONCLUSTERED (id) ON [PRIMARY], pgh ntext, ts timestamp)

ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]”

For Each pgh In ActiveDocument.Paragraphs

.Execute “insert into mysqlpaper (ts) values (default)”

rst.Open "select pgh from MySQLPaper where id = @ @ identity", cnn, 1, 3, -1

rst.Fields(0) = pgh

rst.Update

rst.Close

Next

.Close

End With

End Sub

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


 sp_configure “Language neutral full-text”, 1 

go

reconfigure

go

Робимо поточну базу даних доступною для повнотекстового пошуку:
exec sp_fulltext_database @action=”enable”

Створюємо новий повнотекстовий каталог SQLPaper по заданому шляху:
exec sp_fulltext_catalog @ ftcat = "SQLPaper", @ action = "create", @ path = "d: \ mssql7b3_dat \ FTData"

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

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



exec sp_fulltext_column @ tabname = "MySQLPaper", @ colname = "pgh", @ action = "add"

Реєструємо таблицю в каталозі повнотекстового пошуку файлової системи:
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

Олексій Шуленін, Microsoft, системний інженер, тел. 967-85-85

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*