Корельовані підзапити

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

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

1 Зовнішній запит виконується один раз

2 Підзапит виконується по одному разу для кожного рядка зовнішнього запиту, використовуючи значення відповідних стовпців

3 Результати підзапиту інтегруються в результуючий набір даних

Якщо зовнішній запит повертає 100 рядків, то SQL Server виконає логічний еквівалент 101 запиту: один раз виконає зовнішній запит і по одному разу виконає подзапрос для кожного рядка його результату Насправді оптимізатор знайде спосіб, як виконати корельований подзапрос, фактично не виконуючи 101 запит На практиці мені доводилося бачити, коли корельовані підзапити перевершували за продуктивності інші плани запитів Якщо вони здатні вирішити стоїть перед вами завдання, не нехтуйте ними тільки з міркувань продуктивності

При розгляді корельованих підзапитів як приклад ми будемо використовувати навчальну базу даних Outer Banks Adventures При цьому ми будемо порівнювати місце розташування замовників і базових таборів У першу чергу ми скорегуємо дані таблиць за допомогою наступного пакета запитів:

USE CHA2

UPDATE dboBaseCamp SET Region =        NC           WHERE BaseCampID = 1

UPDATE dboBaseCamp SET Region =        NC           WHERE BaseCampID = 2

UPDATE dboBaseCamp SET Region =        BA           WHERE BaseCampID = 3

UPDATE dboBaseCamp SET Region =        1FL           WHERE BaseCampID = 4

UPDATE dboBaseCamp SET Region =        WV          WHERE BaseCampID = 5

UPDATE dboCustomer SET Region =          ND1         WHERE CustomerlD = 1

UPDATE dboCustomer SET Region =          NC           WHERE CustomerlD = 2

UPDATE dboCustomer SET Region =         NJ            WHERE CustomerlD = 3

UPDATE dboCustomer SET Region =         NE            WHERE CustomerlD = 4

UPDATE dboCustomer SET Region =         ND           WHERE CustomerlD = 5

UPDATE dboCustomer SET Region =         NC           WHERE CustomerlD = 6

UPDATE dboCustomer SET Region =         NC           WHERE CustomerlD = 7

UPDATE dboCustomer SET Region =         BA           WHERE CustomerlD = 8

UPDATE dboCustomer SET Region =         NC           WHERE CustomerlD = 9

UPDATE dboCustomer SET Region =         1FL1           WHERE CustomerlD = 10

На підставі місця розташування базових таборів і клієнтів сформований набір даних дозволяє створити матрицю:

SELECT DISTINCT CustomerRegion, BaseCampRegion FROM dboCustomer

JOIN dboEvent_mm_Customer

ON CustomerCustomerlD = Event_mm_CustomerCustomerlD JOIN dboEvent

ON Event_mm_CustomerEventID = EventEventID JOIN dboTour

ON EventTourlD = TourTourlD JOIN dboBaseCamp

ON TourBaseCampID = BaseCampBaseCampID WHERE CustomerRegion IS NOT NULL GROUP BY CustomerRegion, BaseCampRegion ORDER BY CustomerRegion, BaseCampRegion

Отримаємо наступний результат:

Customer BaseCamp Region Region

BA BA

BA FL

BA NC

FL FL

FL NC

FL WV

NC BA

NC FL

NC NC

NC WV

ND BA

ND FL

ND NC

NE FL

NE WV

NJ FL

NJ NC

NJ WV

Маючи в наявності ці дані, можна задати перше запитання: Хто живе в регіоні, в якому знаходиться один з базових таборів” Запит використовує корельований подзапрос для

пошуку базових таборів, які знаходяться в регіоні замовника Цей подзапрос виконується для кожного рядка таблиці Customer, використовуючи іменований діапазон зовнішнього запиту (С) для звертання до нього Якщо існує деякий базовий табір, відповідний цьому рядку, то умова EXISTS стає істинним і рядок додається в результуючий набір даних:

SELECT СFirstName, СLastName, СRegion FROM dboCustomer AS З WHERE EXISTS (SELECT * FROM dboBaseCamp AS В WHERE BRegion = CRegion)

ORDER BY LastName, FirstName

Той же запит, написаний за допомогою обєднань, вимагає наявності предиката DISTINCT уникнення дублювання рядків Однак він може звертатися до стовпців кожної з таблиць, на які має посилання, чого іноді не може зробити корельований подзапрос, що знаходиться у пропозиції WHERE EXISTS:

SELECT DISTINCT СFirstName, СLastName, СRegion FROM Customer З JOIN dboBaseCamp В

ON CRegion = BRegion ORDER BY LastName, FirstName

Отримаємо наступний результат:

FirstName LastName    Region

Jane                              Doe                BA

Francis                         Franklin         FL

Melissa Anderson        NC

Lauren                          Davis             NC

Wilson                          Davis             NC

John                             Frank             NC

Можна сформулювати більш складне питання: Хто замовляв тур у своєму регіоні” Відповідь знаходиться в таблиці Event_mm_Customer – роздільної (або обєднуючою) таблиці між таблицями Event і Customer, що служить для зберігання логічних відносин багато до багатьох між клієнтами і подіями (багато клієнтів можуть брати участь в одному заході, а один клієнт може відвідувати кілька заходів) Таблицю Event_ mm_Customer можна сприймати як аналог квитка, придбаного одним замовником на один захід

Зовнішній запит логічно запускається для кожного рядка таблиці Event_mm_Customer, щоб визначити, чи дасть операція EXISTS небудь результат з корельованого підзапиту Підзапит здійснює фільтрацію за поточним ідентифікатором події (Event ID) та регіону клієнта (Reg ion ID), взятому з зовнішнього запиту

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

USE СНА2

SELECT DISTINCT СFirstName, СLastName, СRegion AS Home FROM dboCustomer З JOIN dboEvent_mm_Customer E ON CCustomerlD = ECustomerlD WHERE CRegion IS NOT NULL

AND EXISTS (SELECT *

FROM dboEvent JOIN dboTour ON EventTourlD = TourTourlD JOIN dboBaseCamp ON TourBaseCampID = BaseCampBaseCampID WHERE BaseCampRegion = CRegion AND EventEventID = EEventID)

Буде отримано наступний результат:

FirstName LastName Home

Francis     Franklin              FL

Jane                Doe              BA

John             Frank              NC

Lauren          Davis              NC

Melissa  Anderson              NC

Той же запит можна написати і з використанням обєднань Незважаючи на те що його легше читати, його виконання зайняло на моєму компютері 131 мілісекунду, в порівнянні з 80 мілісекундами часу виконання попереднього прикладу, що використовує корельовані підзапити

SELECT Distinct СFirstName, СLastName, СRegion AS Home,

TourTourName, BaseCampRegion FROM dboCustomer З JOIN dboEvent_mm_Customer

ON CCustomerlD = Event_mm_CustomerCustomerlD JOIN dboEvent

ON Event_mm_CustomerEventID = EventEventID JOIN dboTour

ON EventTourlD = TourTourlD JOIN dboBaseCamp

ON TourBaseCampID = BaseCampBaseCampID AND CRegion = BaseCampRegion AND CRegion IS NOT NULL ORDER BY CLastName

Цей запит має перевагу: він включає стовпці з таблиці Tour, не вимагаючи їх явного повернення підзапитом У той же час в результатах цього запиту клієнти Lauren Davis і John Frank згадуються двічі – по одному разу для кожного туру:

FirstName LastName Home TourName                                                     Region

Melissa             Anderson          NC                Outer Banks Lighthouses         NC

Lauren                     Davis          NC                             Appalachian Trail         NC

Lauren                     Davis          NC                Outer Banks Lighthouses         NC

Jane                           Doe          BA                                  Bahamas Dive         BA

John                        Frank          NC                             Appalachian Trail         NC

John                        Frank          NC                 Outer Banks Lighthouses         NC

Francis                Franklin          FL                                    Amazon Trek         FL

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

Джерело: Нільсен, Пол 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>

*

*