پست تنها



آشنایی با Lookup در Execution Plan

در SQL Server این امکان برای شما فراهم شده تا نقشه راه برای اجرای کوئری های خود را ببینید

به عبارتی می توانید نقشه اجرایی که SQL Server برای اجرای کوئری شما از آن استفاده می کند را مشاهده کنید. به این نقشه Execution Plan گفته میشود.

SQL Server برای اینکه بتواند یک کوئری را اجرا کند، نقشه های مختلفی ایجاد میکند (گاهی صدها یا هزاران نقشه)، از بین آنها کم هزینه ترین نقشه را برای اجرای کوئری انتخاب می کند. در این نقشه شما می توانید جزئیات هزینه های هر مسیر را مشاهده کنید؛ که با استفاده از آن می توانید مسیرهایی که باعث کندی اجرا شده و یا ممکن است بعدا سبب این کندی شوند را شناسایی و برای حل آنها اقدام کنید.

برای اینکه بتوانید Execution Plan یک کوئری را پس از اجرای آن دستور ببینید روی Include Actual Execution Plan، و برای مشاهده پلن تخمینی کوئری پیش از اجرا روی Display Estimated Execution Plan از نوار ابزار کلیک کنید.


فعال کردن نمایش Execution Plan پس از اجرای دستورات

SQL Server توسط این نقشه دید بسیار خوبی از نحوه اجرای کوئری و عواملی که موجب کندی اجرای دستورات می شود را نشان می دهد.شما با استفاده از Execution Plan خواهید توانست بخش هایی که باعث ایجاد این مشکل شده اند را شناسایی کنید، تا با رفع این عوامل بتوانید سرعت اجرا را تا چندین برابر افزایش دهید.

با استفاده از Execution Plan می توان بخش هایی که باعث کندی و یا ایجاد مشکل در کوئری شما شده اند را پیدا و رفع کنید

عوامل مختلفی  می توانند سبب کاهش سرعت شوند که با توجه به شرایط موجود، روش های گوناگونی نیز برای رفع آنها از طریق ایندکس گذاری وجود دارد که در مورد تمام آنها به همراه مثال های کاربردی مفصلا در  بسته ۵ آشامیدنی SQL Server صحبت کردیم و همانطور که می دانید یکی از عوامل کاهش دهنده سرعت اجرای کوئری ها Lookup است که با ایندکس گذاری صحیح می توانید آن را مرتفع کنید. اما پیش از رفع این مشکل بهتر است مرور کنیم که  Lookup چیست و چرا باعث کاهش سرعت می شود؟


Lookup چیست و چرا باعث کاهش سرعت می شود؟

زمانی که شما روی یک ستون ایندکس گذاری ( NonClustered ) می کنید، SQL Server در محیطی خارج از جدول مربوط به ستون، تنها داده های آن ستون را مرتب سازی می کند و آنها را به همراه آدرس دستیابی به داده های اصلی در جدول در Page های مختلف مرتبط به هم قرار می دهد.

در صورتی که جدول شما دارای Clustered Index باشد، آدرس دستیابی فیلد Cluster شده توسط ایندکس و در غیر این صورت آدرس Page و ردیف دستیابی به اطلاعات است.

تصویر زیر دید بهتری را از این مساله ایجاد می کند. دقت داشته باشید که داده ها در سطح داده همان داده های واقعی جدول شما هستند و ممکن است شامل تعداد زیادی ستون باشند. در تصویر زیر تنها دو ستون در سطح داده نشان داده شده است:




حال اگر شما در کوئری خود روی ستونی که دارای ایندکس است شرطی قرار دهید، SQL Server برای سرعت بخشیدن به جستجو، از ایندکس یعنی همان داده های مرتب شده استفاده می کند. اما اگر بخواهید از ستون های دیگری غیر از ستون موجود در شرط (که ایندکس شده اند) در دستور Select استفاده کنید، SQL Server مجبور خواهد شد به داده های اصلی جدول مراجعه کند. طی کردن همین مسیر اضافه ممکن است باعث کندی اجرای دستورات شما شود.


برای حل این مشکل بهتر است که ستون های دیگری که در دستور Select به آنها نیاز دارید را در کنار ستونی که ایندکس کرده اید (همان ستونی که در شرط از آن استفاده کرده اید) قرار دهید. این کار توسط عبارت Include امکان پذیر است. با این کار شما در کنار داده های مرتب شده در ایندکس، ستون های مورد نیاز خود را نیز خواهید داشت و SQL Server نیازی به مراجعه به داده های اصلی پیدا نخواهد کرد.

در صورتی که بانک اطلاعاتی  AdventureWorks2012 را در اختیار دارید دستورات زیر را اجرا کنید.

در اولین مرحله با استفاده از داده هایی که View ی vEmployee در اختیار ما قرار می دهد یک جدول با نام tblEmployee ایجاد می کنیم.

ساخت جدول tblEmployee با استفاده از داده های ویوی vEmployee

SELECT  [BusinessEntityID]
,[Title]
,[FirstName]
,[LastName]
,[PhoneNumber]
,[PhoneNumberType]
,[PostalCode]
,[CountryRegionName]
Into tblEmployee
FROM [AdventureWorks2012].[HumanResources].[vEmployee]


در گام بعد دو ایندکس، یکی از نوع Clustered و دیگری از نوع NonClustered روی دو ستون BusinessEntityID و PostalCodeجدول ایجاد می کنیم.
ابتدا Index ای که از نوع Clustered است را روی ستون BusinessEntityID ایجاد میکنیم:

ساخت ایندکس نوع Clustered روی ستون tblEmployee در جدول ساخته شده

CREATE CLUSTERED INDEX CIDX_BEID on tblEmployee(BusinessEntityID)

سپس Index ای که از نوع NonClustered است را روی ستون PostalCode همان جدول ایجاد میکنیم:

ساخت ایندکس نوع NonClustered روی ستون tblEmployee در جدول ساخته شده

CREATE NONCLUSTERED INDEX NCIDX_PostalCode ON tblEmployee(PostalCode)

حال دستور زیر را اجرا کنید تا بتوانید نقشه اجرای( Execution Plan ) آن را مشاهده کنید

یک دستور ساده برای مشاهده Execution Plan آن

Select PostalCode,FirstName,LastName
from tblEmployee
where PostalCode in('33000','55402')

 

پس از اجرای دستور فوق Execution Plan حاصل به صورت زیر خواهد بود :

Execution Plan بدست آمده از اجرای دستور فوق

 

برای از بین بردن Lookup کافی است ستون های FirstName و LastName   را در کنار ستون ایندکس شده یعنی ستون PostalCode قرار دهیم. برای این کار ابتدا ایندکس قبلی را حذف نموده و پس از آن ایندکس را به شکل جدیدی ایجاد کنید:

تکرار یک عمل تا زمانی که شرط از بین برود

DROP INDEX NCIDX_PostalCode ON tblEmployee

CREATE INDEX NCIDX_PostalCode  ON

tblEmployee(PostalCode) INCLUDE (FirstName ,LastName)

 

خواهید دید که Execution Plan به بهترین شکل ممکن تغییر خواهد کرد:


تغییر Execution Plan به بهترین وضعیت ممکن

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *