قاتلین ایندکس‌ها در SQL Server [بخش پنجم]

مقدمه:

در قسمت‌های قبلی (بخش اول، دوم، سوم و چهارم) مشاهده کردید که چگونه با انتخاب استراتژی نامناسب در نوشتن کوئری‌ها می‌توان زمینه‌های لازم برای عدم استفاده از ایندکس‌های موجود را فراهم کرد. در بخش پایانی به سراغ یکی دیگر از قاتلین ایندکس‌ها خواهیم رفت. توجه داشته باشید که موارد ذکر‌شده در این مجموعه مقاله، تنها بخشی از معضلاتی هستند که می‌توانند موجب عدم استفاده از قابلیت‌های ایندکس‌ها در حین اجرای کوئری‌ها شوند.
نکته مهم: تمامی کوئری‌های این مجموعه مقالات، بر روی دیتابیس AdventureWorks2016 و در محیط SQL Server Management Studio 2016 و 2017 اجرا شده است.

قاتل شماره 5 – (Data Conversion)

یکی از مواردی‌که می‌تواند تاثیری منفی در عملکرد ایندکس‌ها داشته باشد زمانی است که نوع‌داده یا Data Type ستون‌ها در حین عملیات‌های JOIN و یا در بخش WHERE مورد تغییر واقع شود. در این شرایط SQL Server مجبور است تا با تبدیل انواع‌داده، انطباق و تشابه میان نوع‌داده‌ی ستون‌های شرکت‌کننده در کوئری‌ها را برقرار سازد.
اگر چنین تبدیلاتی به‌صورت صریح (Explicit) در متن کوئری قید نشده باشد آن‌گاه SQL Server مجبور است تا چنین تبدیلاتی را در پس‌زمینه‌ی فرایند اجرای کوئری و به‌صورت ضمنی (Implicit) انجام دهد. این تبدیلات (منظور، انواع تبدیلات مجاز در SQL Server است) بر اساس اولویت انواع‌داده انجام خواهد شد. شما می‌توانید این موضوع را در لینک زیر دنبال کنید:

https://technet.microsoft.com/en-us/library/ms190309(v=sql.110).aspx

اما این عملیات چگونه تاثیری منفی در روند اجرای یک کوئری خواهد داشت؟ پاسخ این سوال دقیقا همانند اتفاقی است که در بخش چهارم این مجموعه مقاله مورد بررسی قرار گرفت. به‌عنوان مثال اگر قرار باشد ستونی را که دارای نوع‌داده VARCHAR بوده و از قبل بر روی آن ایندکسی پیاده‌سازی شده، در حین اجرای کوئری به نوع NVARCHAR تبدیل کنیم آن‌گاه در هنگام ساخت Plan کوئری به‌هیچ عنوان اطلاعات و آمار مربوط به ایندکسِ مقادیر آن ستون، در تصمیم‌گیری و تعیین میزان فراوانی و موقعیت مقادیر آن ستون، مفید واقع نخواهد شد.
برای نشان دادن این موضوع، سناریو زیر را برای‌تان تشریح خواهم کرد:
ابتدا به‌کمک دستور Make Table Query، برخی از ستون‌های جدول Person را در جدولی با عنوان PersonPerson کپی کنید.

USE AdventureWorks2014
GO

SELECT
BusinessEntityID
,CAST(FirstName as varchar(50)) as FirstName
,CAST(MiddleName as varchar(50)) as MiddleName
,CAST(LastName as varchar(50)) as LastName
INTO PersonPerson
FROM Person.Person;
GO

سپس در جدول PersonPerson یک CLUSTEREDINDEX بر روی فیلد BusinessEntityID و یک Non-CLUSTEREDINDEX بر روی فیلد FirstName تعریف کنید.

[sql]

CREATE CLUSTERED INDEX IX_PersonPerson_ContactID ON PersonPerson (BusinessEntityID);
CREATE INDEX IX_PersonContact_FirstName ON PersonPerson(FirstName);
GO

[/sql]

اکنون کوئری‌های زیر را اجرا کنید و ببینید که چگونه عدم تطابق نوع‌داده یک فیلد می‌تواند اثرات منفی خود را بر روی کارآیی و نحوه استفاده از ایندکس‌ها نشان دهد.

[sql]

SET STATISTICS IO ON;

— Query1
DECLARE @FirstName NVARCHAR(100)
SET @FirstName = ‘Gail’;
SELECT
FirstName, LastName FROM PersonPerson
WHERE FirstName = @FirstName
OPTION (RECOMPILE);
GO

— Query2
DECLARE @FirstName VARCHAR(100)
SET @FirstName = ‘Gail’;
SELECT
FirstName, LastName FROM PersonPerson
WHERE FirstName = @FirstName
OPTION (RECOMPILE);
GO

[/sql]

در کوئری اول، متغیری از نوع‌داده NVARCHAR تعریف شده است. از طرفی نوع‌داده فیلد FirstName در جدول PersonPerson برابر با VARCHAR است. بنابراین این دو نوع‌داده همانند یکدیگر نبوده و می‌بایست این فیلد در حین پردازش کوئری از VARCHAR به NVARCHAR تبدیل شود. (این اتفاق با توجه به قواعدی انجام می‌شود که لینک آن را در ابتدای این مقاله برای‌تان گذاشته‌ام. در حقیقت اولویت نوع‌داده NVARCHAR بالاتر بوده؛ بنابراین در بخش WHERE تبدیل ضمنی صورت گرفته و فیلد FirstName به NVARCHAR تغییر می‌یابد) شما می‌توانید این موضوع را در Plan اجرایی این کوئری مشاهده کنید که چگونه برای بازیابی رکوردها، عملیات تبدیلِ نوع‌داده به‌صورت ضمنی انجام شده است.

مجددا به Plan اجرایی کوئری اول نگاه کنید. در بخش SELECT این Plan، هشداری با این مضمون که تبدیل نوع‌داده از VARCHAR به NVARCHAR می‌تواند بر روی انتخاب اجرای کوئری از طریق عملیات Index seek تاثیرگذار باشد، به‌چشم می‌خورد.

اما در کوئری دوم، انطباق نوع‌داده میان متغیر تعریف‌شده و فیلد FirstName از جدول PersonPerson برقرار است و همین موضوع باعث می‌شود تا در هنگام ساخت Plan کوئری، از ایندکسِ تعریف‌شده (منظور IX_PersonContact_FirstName) بر روی فیلد FirstName استفاده شود.

در شکل زیر، مقایسه تعداد Pageهای خوانده‌شده توسط این دو کوئری را مشاهده می‌کنید. کوئری اول صرفا به دلیل عدم تطابق میان نوع‌داده فیلد FirstName و متغیر تعریف‌شده، نتوانسته است از قابلیت‌های ایندکس استفاده کند و در نتیجه برای اجرای کوئری، تعداد Pageهای بیشتری را خوانده است.

پی‌نوشت: این مجموعه مقاله، برداشت آزادی بود از فصل دوازدهم کتاب Expert Performance Indexing in SQL Server که انتشارات Apress آن را ارائه کرده است. پیشنهاد می‌کنم حتما این کتاب جذاب و فوق‌العاده را مطالعه کنید.
امیدوارم این مجموعه مقاله مورد استفاده شما قرار گرفته شده باشد. به‌زودی با مجموعه مقالات دیگری در حوزه ایندکس‌ها در خدمت‌تان خواهم بود.