حلقههای تودرتو
حتی اگر پرس وجوی شما بیش از 2 جدول را پیوند دهد، سرویس دهنده SQL عمل پیوند را از طریق پیوند تنها دو ورودی در یك زمان اجرا میكند و هر پیوند در یك پرس و جو ممكن است از استراتژی پیوند متفاوتی استفاده كند. آسانترین نوع پیوند – و نوعی از پیوند كه اكثر افراد هنگام عملیات پیوند بدان فكر میكنند – پیوند حلقه تودرتوست میتوانید تصور كنید كه سرویس دهنده SQL روی دو ورودی عمل میكند حتی اگر آنها آرایههایی در یك زبان پیشرفته مانند C یا Basic باشند. سرویس دهنده SQL هر سطر یك ورودی را با تمام سطرهای ورودی دیگر مقایسه میكند تا تطابق بین سطرها را بیابد.
پرس و جوی 1 سعی دریافتن سطرهایی دارد كه با ستون EmployeeID تطبیق داشته باشد. بنابراین با استراتژی پیوند حلقه تودرتو، سرویس دهنده SQL باید تمام مقادیر EmployeeID در یك جدول را با تمام مقادیر EmployeeID در جدول دیگر مقایسه كند.
بدترین قسمت سناریو برای یك پیوند حلقه تودرتو زمانی است كه هیچ شاخصی نتواند به سرویس دهنده SQL دریافتن سطرهای منطبق در بین ورودیها و همچنین یافتن سطرهایی كه در هر شرط WHERE صدق میكند، كمك نماید. در ین حالت، ورودیها كل سطرهای جدولها هستند. بهینه ساز پرس و جو جدولی را بعنوان جدول خارجی انتخاب میكند و در ابتدا به سطرهای آن دستیابی مییابد. بیایید فرض كنیم كه جدول خارجی دارای P1 صفحه و R1 سطر باشد. دومین جدول كه جدول داخلی است P2 صفحه دارد. سرویس دهنده SQL باید تمام صفحات را از جدول خارجی بخواند؛ و برای هر سطر تعریف شده در هر صفحه باید تمام صفحات را از جدول داخلی بخواند. برای یافتن تعداد صفحاتی كه سرویس دهنده SQL برای خواندن و ارائه نتیجه نیاز دارد، میتوانید از فرمول زیر استفاده كنید :
P1 + R1 * P2
حتی اگر جدولها نسبتاً كوچك باشند، عدد حاصله از صفحات خوانده شده به سرعت بزرگ میشود. در مورد یك جدول خارجی با تنها 200 صفحه و 4000 سطر (برای مثال 20 سطر برای هر صفحه) و یك جدول داخلی با 100 صفحه، نتیجه رقمی كاملاً بزرگ است. جدولهایی با 100 یا 200 صفحه جدولهایی نیستند كه بطور غیرمعمول بزرگ باشند، اما برای پردازش پیوند در صورتی كه جدولها شاخصهای مفیدی نداشته باشند، سرویس دهنده SQL نیاز به دستیابی به بیش از 400,000 صفحه خواهد داشت. شاخصها میتوانند در بهبود عملكرد یك پیوند حلقه تودرتو به طرق مختلف نقش داشته باشند. بزرگترین حسن این شاخصها اغلب زمانی است كه شما یك شاخص كلاستری روی ستون پیوند یكی از جدولها داشته باشید. وجود یك شاخص كلاستری روی ستون پیوند غالباً مشخص میكند كه سرویس دهنده SQL چه جدولی را بعنوان جدول داخلی انتخاب میكند. اگر جدول داخلی دارای شاخص كلاستری باشد، سرویس دهنده SQL نیاز به جستجو در میان كل سطرهای آن جدول را ندارد. شاخص كلاستری، سرویس دهنده SQL را مستقیماً به سوی سطرهایی در جدول داخلی هدایت میكند كه دارای مقدار ستون پیوند بوده كه سطرهای جاری در جدول خارجی را تطبیق میدهد. بنابراین در آن فرمول، به جای عبارت R1 ´ P2 كه نشان میدهد سرویس دهنده SQL به تمام P2 صفحه دستیابی پیدا میكند، میتوانید P2 را با دستیابی 2 یا 3 صفحهای جایگزین كنید بسته به اینكه شاخص كلاستری چند Level دارد. بنابراین در مورد مثالی با 200 صفحه و 400 سطر در جدول خارجی و 100 صفحه در جدول داخلی نتیجه 3*4000+200 یا 200،12 صفحه خوانده شده است – یك پیشرفت بزرگ بالای 400,000 صفحه میباشد.
هنوز هم آن 4000 سطر در محاسبه نتیجه را بزرگتر از حد انتظار خواهد كرد. در این حالت، تمامی 4000 سطر در جدول خارجی بخشی از نتیجه هستند كه موجب 4000 بار رجوع به جدول داخلی میشود. یك راه دیگر برای كاهش تعداد صفحات بدست آمده كاهش اندازه ورودیهای خارجی است. علاوه بر كنترل شاخص كلاستری روی ستون پیوند، ابتدا بهینه ساز سعی میكند جدولها را با ورودیهای كوچكتر پیوند دهد. در پرس و جوی 1، جدول كارمندان دارای یك شاخص كلاستری روی ستون پیوند یعنی EmployeeID هستند اما این جدول نیز بطور نمایشی كوچكتر از جدول سفارشات است. جدول كارمندان تنها 9 سطر دارد و جدول سفارشات 830 سطر. در پرس و جوی 1، اگر بهینه ساز یك پیوند حلقه تودرتو را انتخاب كند، از جدول كوچكتر كارمندان به عنوان ورودی خارجی استفاده میكند بگونهای كه تنها 9 بار به جدول سفارشات رجوع خواهد داشت.
اگر شما دارای شرط WHERE باشید كه جدول خارجی را شامل میشود، تعداد سطرهای تعریف شده پایین میآید و سرویس دهنده SQL كمتر نیاز به مراجعه به جدول داخلی را خواهد داشت. اگر پرس و جوی 1 را طوری تغییر دهید كه شامل یك شرط WHERE در جدول سفارشات باشد، همانگونه كه در پرس و جوی 3 نشان داده شده، طرح پرس و جو تغییر میكند.
: Query 3
SELECT LastName, FirstName, OrderID, OrderDate
FROM Employees e JOIN Orders o
ON e.EmployeeID = o.EmployeeID
WHERE OrderDate < "1996-12-01"
حالا، تنها 121 سطر در جدول سفارشات بخشی از نتیجه هستند، آن جدول كوچكتر كه با شاخص كلاستری روی ستون پیوند جدول كارمندان تركیب شده این مفهوم را میرساند كه بهینه ساز حالا جدول كارمندان را به عنوان جدول داخلی انتخاب میكند. سرویس دهنده SQL از پیوند حلقه تودرتو استفاده خواهد كرد چرا كه شاخص كلاستری باعث میشود سرویس دهنده SQL سریعاً سطرهای منطبق شده را در جدول داخلی بیابد.
FIGURE 1: Query plan for Query 3
|..Nested Loops(Inner Join, OUTER REFERENCES:([o].[EmployeeID]))
|..Clustered Index Scan(OBJECT:([northwind].[dbo].[Orders].[PK_Orders]
AS [o]),
WHERE:([o].[OrderDate] < "Dec 1 1996 12:00AM"))
|..Clusterd Index
Seek(OBJECT:([northwind].[dbo].[Employees].[PK_Employees] AS [e]),
SEEK:([e].[EmployeeID]=[o].[EmployeeID]) ORDERED FORWARD)
شكل 1 طرح پرس و جو را در مورد پرس و جوی 3 نشان میدهد. اولین خط این طرح نوع پیوند (حلقه تودرتو) را نشان میدهد و مشخص میكند كه جدول خارجی ستون EmployeeID را ارجاع خواهد داد. اسكن شاخص كلاستری در جدول خارجی شبیه اسكن یك جدول است زیرا هیچیك از شاخصهای موجود نمیتواند دستیابی به جدول خارجی را سرعت ببخشد. شرط WHERE در ستون OrdrerDate تعداد سطرهای برگردانده شده و تعداد دفعاتی كه سرویس دهنده SQL باید به جدول داخلی دستیابی داشته باشد تا تعیین كند كدامیك مقدار OrderDate قابل قبولی دارند. در نهایت، طرح پرس و جو نشان میدهد كه سرویس دهنده SQL از یك شاخص كلاستری برای جستجوی جدول داخلی استفاده میكند زیرا این شاخص روی ستونی است كه سرویس دهنده SQL برای یافتن سطرهای منطبق استفاده میكند.
چنانچه قبلاً ذكر شد شاخص در ستون OrderDate چیز خوبی است اما عملكرد پرس و جو را تقریباً به یك شاخص كلاستری روی ستون پیوند بهبود نخواهد بخشید. یك شاخص مفید در پارامتر جستجو در جدول خارجی بدین معناست كه سرویس دهنده SQL نباید به تمام صفحات جدول خارجی رجوع نماید، بنابراین، مقدار P1 كاهش مییابد. باتوجه به اینكه مقدار P1 نسبت به مقدار دومین عبارت، P2 R1، كوچكتر است، بنابراین كاهش مقدار P1 فقط موجب بهبودی كمتر عملكرد میگردد. شاخص جدول خارجی تعداد دفعاتی كه سرویس دهنده SQL باید به جدول داخلی رجوع كند را كاهش نمیدهد زیرا سرویس دهنده SQL هنوز باید بازای هر سطر تعریف شده در جدول خارجی به جدول داخلی رجوع كند. شما میتوانید انتخاب بهینهساز از پیوند حلقه تودرتو را اینگونه تعمیم دهید: در صورتی كه یكی از ورودیهای پیوند بسیار كوچكتر از دیگری و ورودی بزرگتر دارای یك شاخص كلاستری روی ستون پیوند باشد، بهینه ساز اغلب پیوند حلقه تودرتو را برمیگزیند.