قاتلین ایندکس در SQL Server [بخش دوم]

مقدمه

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

نکته مهم: تمامی کوئری‌های این مجموعه مقالات، بر روی دیتابیس AdventureWorks2016 و در محیط SQL Server Management Studio 2016 و 2017 اجرا شده است.

قاتل شماره 2 – عملیات Concatenation

در بسیاری از موارد شاید برای‌تان پیش آمده باشد که بخواهید در بخش WHERE از کوئری‌تان مقادیر مختلفی را با یکدیگر پیوند داده و جستجو را بر اساس آن‌ها انجام دهید؛ در حقیقت با این کار شما می‌خواهید از عملیات Concatenation استفاده کنید.

فرض کنید در جدول Person از دیتابیس AdventureWorks2016 می‌خواهید عملیات جستجو برای فردی با مشخصات Gustavo Achong را انجام دهید. برای نوشتن چنین کوئری‌ای می‌بایست در بخش WHERE، فیلدهای FirstName و LastName را به‌همراه یک کاراکتر فضای خالی با یکدیگر پیوند داد

[sql]

USE AdventureWorks2016
GO
SET STATISTICS IO ON;
SELECT
BusinessEntityID, FirstName, LastName
FROM Person.Person
WHERE FirstName + ‘ ‘ + LastName = ‘Gustavo Achong’;

[/sql]

اگر به Plan اجرایی این کوئری نگاهی بیاندازید متوجه می‌شوید که عملیات Index Seek انجام نشده است و برای واکشی همین یک رکورد، تمامی رکوردهای جدول Person خوانده شده است!

یک برنامه‌نویس یا مدیر دیتابیس که با قابلیت‌های ایندکس‌ها آشنا است، در این‌جا تصمیم خواهد گرفت تا بر روی این دو فیلد، ایندکس مناسبی را تهیه کند. کوئری زیر این کار را انجام داده و یک NONCLUSTERED INDEX را برای این دو فیلد ایجاد خواهد کرد.

[sql]

CREATE INDEX IX_PersonContact_FirstNameLastName ON Person.Person (FirstName, LastName)
GO

[/sql]

مجددا کوئری زیر را اجرا می‌کنیم.

[sql]

SELECT
BusinessEntityID, FirstName, LastName
FROM Person.Person
WHERE FirstName + ‘ ‘ + LastName = ‘Gustavo Achong’;

[/sql]

انتظارمان این بود که پس از اجرای کوئری، ایندکس تعریف‌شده بر روی این دو فیلد موجب افزایش کارآیی عملکرد کوئری شود اما با نگاهی به آمار و اطلاعات I/O از بخش Messages و Plan اجرایی کوئری متوجه خواهیم شد که چنین امری تحقق نیافته است!

شاید با کمی تامل در کوئری بالا به این نتیجه برسیم که با حذف کاراکتر فضای خالی از پیوند میان فیلدهای FirstName و LastName مشکل برطرف خواهد شد؛ پس کوئری را به شکل زیر اصلاح می‌کنیم.

[sql]

SELECT
BusinessEntityID, FirstName, LastName
FROM Person.Person
WHERE FirstName + LastName = ‘GustavoAchong’;
[/sql]

علیرغم انتظارات‌مان باز هم تعداد Page های خوانده شده برابر با 99 خواهد بود و در Plan اجرایی کوئری نیز خبری از عملیات Index Seek نیست؛ به‌عبارت دیگر عملکرد این دو کوئری با یکدیگر یکسان خواهد بود.

اگر فکر می‌کنید راه‌کار مناسب، استفاده از تابع CONCAT است باید به شما بگویم که سخت در اشتباه هستید. اگر دو کوئری زیر را با یکدیگر اجرا کنید خواهید دید که به لحاظ تعداد Pageهای خوانده‌شده، وضعیت مشابهی دارند اما Plan اجرایی کوئری‌ای که در آن از تابع CONCAT استفاده شده است دارای Cost بالاتری است! این معضل در قسمت بعدی این مجموعه مقالات مورد بررسی قرار خواهد گرفت.

[sql]

SELECT
BusinessEntityID, FirstName, LastName
FROM Person.Person
WHERE FirstName + LastName = ‘GustavoAchong’;

SELECT
BusinessEntityID, FirstName, LastName
FROM Person.Person
WHERE CONCAT(FirstName , LastName) = ‘GustavoAchong’;
[/sql]

به‌نظر می‌رسد که باید در این‌‌جا از عملیات پیوندِ میان فیلدها صرف‌نظر کرده و به‌دنبال راه‌کار مناسبی باشیم تا از قابلیت‌های ایندکسِ موجود بر روی این دو فیلد نهایت استفاده را برده باشیم. در کوئری زیر به‌جای استفاده از عملگرهای + و یا تابع CONCAT از عملگر منطقی AND استفاده خواهیم کرد.

[sql]

SELECT SELECT  BusinessEntityID, FirstName,LastName
FROM Person.Person WHERE FirstName + LastName = ‘GustavoAchong’;

SELECT  BusinessEntityID, FirstName,LastName
FROM Person.Person WHERE FirstName = ‘Gustavo’ AND LastName = ‘Achong’;

[/sql]

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

اجرای هم‌زمان دو کوئری زیر و مقایسه نتایج آمار و اطلاعات I/O و Cost هر یک از آن‌ها نیز خالی از لطف نیست.

[sql]

SELECT BusinessEntityID, FirstName, LastNameFROM Person.Person
WHERE FirstName + LastName = ‘GustavoAchong’;

SELECT BusinessEntityID, FirstName, LastNameFROM Person.Person
WHERE FirstName = ‘Gustavo’ AND LastName = ‘Achong’; >

[/sql]

در قسمت بعدی با یکی دیگر از قاتلین ایندکس‌ها آشنا خواهید شد.

[download id=”28609″]

شاید این موضوعات هم برات جالب باشه:

آموزش Column Store Index در SQL Server قاتلین ایندکس‌ها در SQL Server [بخش پنجم] بررسی عملی سه روش پردازش داده‌های آفلاین‌ در اسپارک [بخش اول] دستور Select Distinct در SQL