Проектування розподілених запитів

Після того як підключення до віддаленого джерела даних встановлено, SQL Server може звертатися до зовнішніх даних за допомогою запитів У табл 152 представлені чотири основних доступних синтаксичних методу, які відрізняються методами обробки і налаштування

Таблиця 152 Матриця методів розподілених запитів

Налаштування

Місце виконання запиту

підключення

Локальний екземпляр SQL Server Зовнішнє джерело даних

Звязаний сервер

Чотирьохкомпонентну імя чотирьохкомпонентну імя openQuery ()

Підключення, визначене в запиті

OpenDataSource() OpenRowSet()

Розподілені запити і Management Studio

Утиліта Management Studio не підтримує графічні методи ініціалізації розподілених запитів Не існує методів перетягування підключеного сервера або видаленої таблиці в конструктор запитів Однак розподілений запит можна ввести вручну на панелі SQL (рис 155), а потім виконати його

Puc 155 Розподілений запит можна виконати в Management Studio, ввівши його вручну на панелі SQL ([XPS] FamilydboPerson)

Розподілені подання

Подання – це збережені інструкції SQL Хоча я не рекомендую засновувати програми, клієнт / сервер на уявленнях, вони можуть виявитися корисними для поточних запитів Оскільки велика частина користувачів (У тому числі і розробники) не знайома з різними методами виконання розподілених запитів, приміщення такого в подання може виявитися вдалим вирішенням проблеми

Локальні розподілені запити

Хоча термін локальний розподілений запит звучить дивно, все не так вже й складно Це запит, який збирає зовнішні дані в SQL Server, а потім виконує запит на локальному сервері Так як обробка таких запитів виконується на локальному сервері, в них використовується синтаксис Т-SQL, і тому їх іноді називають локальними запитами T-SQL

Використання четирьохкомпонентного імені

Якщо дані знаходяться на іншому екземплярі SQL Server, то повний синтаксис четирьохкомпонентного імені наступний:

Сервер База_данних Схема Імя_обекта

Чотирьохкомпонентну імя може використовуватися в будь-яких запитах витягу або модифікації даних На моєму компютері існує другий примірник SQL Server з імям [XPS \ Yukon] Імя власника обєкта є обовязковим, якщо обіг здійснюється до зовнішнього сервера

Наступний запит витягує таблицю Person з екземпляра SQL2:

SELECT LastName, FirstName

FROM [XPS\Yukon]Familydboperson

Результат запиту наступний:

LastName FirstName

Halloway Kelly Halloway James

При виконанні інструкцій INSERT, UPDATE і DELETE в якості розподілених запитів для імені таблиці можна використовувати або чотирьохкомпонентну форму, або функцію розподіленого запиту В якості прикладу наведемо наступний код, який можна взяти з файлу CHA2_Convert sql і який заповнює навчальну базу даних СНА2 У цьому прикладі як джерело даних для інструкції INSERT використано чотирьохкомпонентну імя таблиці Цей запит витягує назви базових таборів з електронної таблиці і вставляє їх в SQL Server:

INSERT BaseCamp(Name)

SELECT DISTINCT [Base Camp]

FROM CHAl_Schedule..[Base_Camp]

WHERE [Base Camp] IS NOT NULL

Якщо ви вже виконували сценарій CHA2_Convert sql і заповнили свою ко-Рада пию бази СНА2, ще раз запустіть сценарій CHA2_Create sql, щоб почати

роботу з порожньою бази даних

У

А ось ще один приклад використання четирьохкомпонентного імені для розподіленого запиту У ньому оновлюється база даних Family, що знаходиться на іншому екземплярі SQL Server:

UPDATE [Noli\SQL2]FamilydboPerson

SET LastName = Wilson

WHERE PersonID = 1

Використання функції OpenDataSource ()

Використання функції OpenDataSource () нічим не відрізняється від використання четирьохкомпонентного імені для доступу до повязаного сервера, за винятком того, що функція визначає підключення у своєму тілі, а не використовує зумовлений повязаний сервер У той час як визначення звязку в програмі пропускає визначення самого сервера, якщо місцезнаходження звязку змінюється, це торкнеться всіх запитів, що використовують функцію OpenDataSource () До того ж функція OpenDataSource () не приймає змінні як аргументи

Функція OpenDataSource () замінює імя сервера в чотирьохкомпонентну імені і може використовуватися в будь-якій інструкції DML

Синтаксис функції OpenDataSource () виглядає досить простим:

OpenDataSource {провайдер, строка_ініціалізаціі)

Однак перше враження оманливе Строка_ініціалізаціі являє собою символьну рядок з декількома параметрами, розділеними крапками з комами (точний список параметрів залежить від конкретного джерела даних) Потенційно в рядку ініціалізації вказується джерело даних, його місце розташування, додаткові параметри, час очікування підключення, ідентифікатор користувача, його пароль і каталог У рядку ініціалізації повинні бути вказані всі необхідні параметри підключення до джерела даних, у тому числі контекст безпеки Окремі параметри в рядку ініціалізації не потрібно брати в лапки Найпоширенішою помилкою, поміченою при реалізації функції OpenDataSource (), є плутанина між запитом і точками з комою

Якщо функція OpenDataSource () підключається до іншого сервера за допомогою Windows, то необхідна аутентифікація з підтримкою Kerberos

Ось відносно простий приклад використання функції OpenDataSource () як механізму доступу до таблиці в іншому екземплярі SQL Server:

SELECT FirstName, Gender

FROM OPENDATASOURCE(

1SQLOLEDB1,

‘Data Source=NOLI\SQL2User ID=JoePassword=j

)FamilydboPerson

Результат виконання запиту:

FirstName      Gender

Adam       M

Alexia      F

У наступному прикладі розподіленого запиту, що використовує функцію OpenDataSource (), ми посилаємося на базу даних Cape Hatteras Adventures Оскільки файл Access містить всього одну базу даних і звернення до таблиць не вимагає вказівки власника, ці частини в чотирьохкомпонентну імені можна опустити:

SELECT ContactFirstName, ContactLastName FROM OPENDATASOURCE(

‘MicrosoftJetOLEDB40,

‘Data Source =

С: \ SQLServerBible \ CHAl_Customersmdb

)..Customers

Результат виконання запиту:

ContactFirstName ContactLastName

Neal                 Garrison

Melissa            Anderson

Gary                Quill

Для ілюстрації використання функції OpenUpdateSource () в запиті UPDATE ми оновимо всі рядки в робочій книзі Excel CHAl_Schedulexls Іменований діапазон був визначений заздалегідь: Tours = Sheetl $ Е $ 5 : $ Е $ 24 1 Тепер він буде використаний у запиті SQL як таблиці в джерелі даних Замість того щоб оновлювати окремо кожну комірку робочого аркуша, цей запит виконує інструкцію UPDATE, що зачіпає всі рядки, в яких назвою туру є Gauley River Rafting, і оновлює стовпець Base Camp значенням Ashville

Розподілений запит SQL Server для доступу до механізму Jet, який відкриває робочий лист Excel, буде використовувати постачальника OLE DB Функції OpenDataSource () ми передаємо тільки імя сервера в чотирикомпонентної формі при цьому, як і у випадку з Access, імя бази даних і власника опускаємо:

UPDATE OpenDataSource(

1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp MicrosoftJetOLEDB40,

1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Data Source=C:\SQLServerBible\CHAl_Schedulexls

User ID=AdminPassword=Extended properties=Excel 50

)..Tour

SET [Base Camp] = Ashville1

WHERE Tour = Gauley River Rafting;

На рис 156 показаний план виконання розподіленого запиту UPDATE Він починається праворуч з блоку Remote Scan, який повертає всі 19 рядків іменованого діапазону Excel Після цього дані обробляються в SQL Server Логічна операція Remote Update зводиться до того, що розподілений запит поновлення насправді змінює значення всього в двох рядках

Рис 156 План виконання розподіленого запиту оновлення, що використовує функцію OpenDataSource ()

Щоб завершити приклад, наступний запит зчитує той же робочий аркуш Excel і перевіряє, чи дійсно мало місце оновлення І знову функція OpenDataSource (), єдина в розподіленому запиті, вказує на зовнішній сервер:

SELECT *

FROM OpenDataSource(

‘MicrosoftJetOLEDB40,

‘Data Source=C:\SQLServerBible\CHAl_Schedulexls

User ID=AdminPassword=Extended properties=Excel 50

)..Tour

WHERE Tour = Gauley River Rafting;

Результат виконання запиту буде наступним:

Base Camp   Tour

Ashville      Gauley River Rafting

Ashville      Gauley River Rafting

Наскрізні розподілені запити

Наскрізні запити виконуються на зовнішньому джерелі даних, після чого результат передається на SQL Server Основна причина використання таких запитів полягає в скороченні обсягу даних, переданих з сервера (зовнішнього джерела даних) і клієнта (SQL Server) Замість передачі мільйона рядків на SQL Server, щоб використовувати тільки двадцять пять з них, може виявитися вигідніше витягти тільки ці рядки із зовнішнього джерела даних

Водночас наскрізні запити повинні використовувати синтаксис зовнішнього сервера Якщо джерелом даних є база даних Oracle, то в наскрізному запиті повинен використовуватися мова PL / SQL якщо база даних Access – то Access SQL

У разі наскрізного запиту, модифікуючий дані, сам тип видалених даних визначає, чи буде оновлення виконуватися локально або віддалено

■ Якщо оновлюються дані на іншому екземплярі SQL Server, то операція буде виконуватися саме на ньому

■ Якщо дані оновлюються на сервері, відмінному від SQL Server, то постачальник визначає, де саме будуть оброблятися дані Часто передаються запити просто відбирають коректні рядка Ці рядки передаються на SQL Server, оновлюються на ньому, а потім повертаються віддаленого джерела даних для оновлення

Існують дві форми локальних розподілених запитів: одна для повязаних серверів і одна для зовнішніх джерел даних, що визначаються в запиті Також існують дві форми наскрізних розподілених запитів В одному випадку функція OpenQuery () використовує вже підключений сервер у другому – функція OpenRowSet () визначає звязок безпосередньо у запиті

Використання четирьохкомпонентного імені

Якщо розподілений запит здійснює доступ до іншого екземпляра SQL Server, чотирьохкомпонентну імя називають гібридним методом розподілених запитів Залежно від пропозицій FROM і WHERE, SQL Server намагатиметься передати якомога більшу частину запиту віддаленого сервера, щоб збільшити продуктивність

При створенні складних розподілених запитів, що використовують чотирьохкомпонентну імя, досить складно передбачити заздалегідь, яка частина запиту буде передана Мені зустрічалися сервери, які, залежно від пропозиції WHERE, або передавали весь запит, або кожна таблиця передавала свій запит, або запит передавала тільки одна таблиця

З чотирьох представлених методів розподілених запитів кращими є використання четирьохкомпонентного імені та функції OpenQuery () відповідно Обидва методи використовують адміністративну настройку підключення до віддаленого сервера, що робить запит більш надійним, якщо конфігурація сервера змінюється

Вибір між цими двома методами залежить від загального обсягу даних, їх извлекаемого кількості та продуктивності сервера Рекомендую вам протестувати обидва методи і порівняти їх плани виконання, після чого вибрати той, який найкраще проявляє себе у вашій ситуації Якщо обидва методи показують рівну продуктивність, то використовуйте метод четирьохкомпонентного імені, щоб сервер мав можливість автоматично оптимізувати розподілене запит

Функція OpenQuery ()

Серед переданих запитів функція OpenQuery () задіює ресурси вже підключеного сервера, тому з нею простіше звертатися Вона також сприймає зміни в конфігурації серверів без зміни коду

Функція OpenQuery () використовується в мові SQL DML в якості таблиці Вона приймає два аргументи: імя повязують сервера і сам переданий запит У наступному прикладі функція OpenQuery () використовується для отримання даних з робочої книги Excel CHAl_Schedule:

SELECT *

FROM OPENQUERY(CHAl_Schedule,

‘SELECT * FROM Tour WHERE Tour = &quotGauley River Rafting&quot’)

Результат виконання запиту:

Tour                      Base Camp

Gauley River Rafting Ashville Gauley River Rafting Ashville

Як показано на рис 157, переданий запит, що використовує функцію OpenQuery (), практично не потребує обробки сервером – йому повертаються рівно два рядки Пропозиція WHERE обробляється механізмом Jet при отриманні даних з робочого листа Excel

У наступному прикладі функція OpenQuery () дає вказівку механізму Jet, щоб той витягнув тільки два рядки, що вимагають оновлення Реальна інструкція UPDATE виконується на сервері, а результат повертається зовнішнього джерела даних В результаті переданий запит виконує в інструкції UPDATE тільки частина функції SELECT:

UPDATE OPENQUERY(CHAl_Schedule,

‘SELECT * FROM Tour WHERE Tour = &quotGauley River Rafting&quot’)

SET [Base Camp] = Ashville

WHERE Tour = Gauley River Rafting;

Функція OpenRowSet ()

Ця функція є двійником функції OpenDataSet () Обидві вимагають, щоб в розподіленому запиті був повністю визначений віддалений джерело даних Функція OpenRowSet () має додатковий аргумент, визначальний переданий запит:

SELECT ContactFirstName, ContactLastName

FROM OPENROWSET (MicrosoftJetOLEDB40, C:\SQLServerBible\CHAl_Customersmdb; Admin;”,

‘SELECT * FROM Customers WHERE CustomerlD = 1)

Рис 157 Розподілений запит, що використовує функцію OpenQuery (), повертає тільки два рядки, відібрані пропозицією WHERE

Результат запиту наступний:

ContactFirstName ContactLastName

Tom                Mercer

Щоб виконати оновлення за допомогою функції OpenRowSet (), вставте її на місце модифікується таблиці У наступному прикладі ми змінимо прізвище замовника в базі даних Access Пропозиція WHERE інструкції UPDATE обробляється передавати частину функції OpenRowSet ():

UPDATE OPENROWSET (MicrosoftJetOLEDB40,

1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp С: \ SQLServerBible \ CHAl_Customersmdb1 Admin

‘SELECT * FROM Customers WHERE CustomerlD = 1)

SET ContactLastName = Wilson;

Операції масового заповнення підтримуються функцією OpenRowSet (),

Новинка ^ і це істотно підвищує їх продуктивність

2005

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

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


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

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

Ваш отзыв

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

*

*