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

مقدمه

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

قاتل شماره 4 – توابع تک مقدار یا اسکالر (Scalar Functions)
در این قسمت می‌خواهم درخصوص عوارض استفاده از توابع تک‌مقدار یا Scalar Functionها در هنگام نوشتن کوئری‌ها و در بخش WHERE با شما صحبت کنم!
همان‌طور که می‌دانید مایکروسافت توابع مختلفی را به‌شکل Built-in در SQL Server ارائه کرده است. این توابع در قالب دسته‌‎‌بندی‌های مختلفی، قابلیت کار با انواع‌داده از قبیل رشته‌ها، اعداد، تاریخ، زمان و … را فراهم می‌کنند. ما می‌توانیم با استفاده از این‌گونه توابع علاوه‌بر صرفه‌جویی در زمان، به افزایش کارآیی کوئری‌ها نیز کمک شایانی کرده باشیم. عملکرد این توابع به‌گونه‌ای است که با تغییر در شکل اصلی مقادیر فیلدها، مقادیر مناسب‌تری را برای نوشتن کوئری‌هایِ مختلف فراهم خواهد کرد.

این کار حتی از طریق توابعی که توسط کاربران و با عنوان User Define Function شناخته می‌شود نیز امکان‌پذیر است اما باید توجه داشت که چنین تغییراتی می‌تواند موجب خنثی شدن عملکرد ایندکس‌های موجود بر روی مقادیر فیلدهای جداول شود چرا که استفاده از این‌گونه توابع، شکل اصلی مقادیر فیلدها را دست‌خوش تغییر می‌کند. بدیهی است که این مقادیر، هیچ‌گونه سنخیتی با مقادیر ایندکس‌گذاری شده در حین عملیات ایندکس‌گذاری ندارند. به‌عبارت ساده‌تر هیچ‌گونه آماری از میزان فراوانی آن‌ها در دسترس نبوده تا بر اساس آن‌ها بهینه‌سازِ SQL Server (Query Optimizer) اقدام به ایجاد یک Plan بهینه از آن‌ها داشته باشد.

برای آن‌که تاثیر این‌گونه از توابع را بر روی روند اجرایی کوئری‌ها ببینید به اسکریپت‌های زیر توجه کنید:

[sql]
USE AdventureWorks2014
GO
SET STATISTICS IO ON

— Query 1
SELECT
BusinessEntityID, FirstName, LastName
FROM Person.Person
WHERE FirstName = ‘Gustavo’;
GO

— Query 2
SELECT
BusinessEntityID, FirstName, LastName
FROM Person.Person
WHERE RTRIM(FirstName) = ‘Gustavo’;
GO
[/sql]

هر دو کوئری بالا قرار است اطلاعات مربوط به رکوردی را نمایش دهند که مقدار فیلد FirstName آن برابر با Gustavo است.
پس از اجرای هم‌زمان هر دو کوئری و با مراجعه به آمار و اطلاعات I/O خواهید دید، کوئری دوم که در بخش WHERE از تابع RTRIM استفاده کرده است، نسبت به کوئری اول تعداد Page بیشتری را خوانده است!

این اختلاف بسیار فاحش، در مقایسه‌ی Plan اجرایی و میزان استفاده از منابع نیز به‌وضوح قابل مشاهده است

واقعیت این است که برای رفع این معضل نمی‌توان یک راه‌کار عمومی ارائه کرد. شاید توجه به این جمله کلیدی، چندان دور از واقعیت نباشد: “پیشگیری بهتر از درمان است!”

ارائه یک راه‌حل مناسب، کاملا متناسب با پارامترهایی هم‌چون مدل کسب‌و‌کار و قواعد حاکم بر آن، مدل طراحی دیتابیس، ساختار رکوردهای موجود در جداول و … است.
به‌عنوان مثال فرض کنید قرار است تمامی سفارشاتِ مرتبط با سال 2012 و ماه 12 را از جدول SalesOrderHeader در خروجی نمایش دهیم. برای انجام این کار می‌توان به دو روش اقدام کرد. به اسکریپت‌های زیر توجه کنید:

[sql]
USE AdventureWorks2014
GO
CREATE INDEX IX_SalesSalesOrderHeader_OrderDate ON Sales.SalesOrderHeader(OrderDate);
GO

SET STATISTICS IO ON;

— Query 1
SELECT
SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE MONTH(OrderDate) = 12
AND YEAR(OrderDate) = 2012;
GO

— Query 2
SELECT
SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN ‘20121201’ AND ‘20121231’;
GO
[/sql]

از آن‌جا که بر روی فیلد OrderDate ایندکسی مناسبی وجود ندارد، ابتدا بر روی آن ایندکسی از نوع Non-Clustered ایجاد می‌کنیم. در ادامه خواهید دید که در اولین کوئری از توابع YEAR و MONTH برای استخراج مقادیر سال و ماه استفاده شده است اما در کوئری دوم صرفا با استفاده از عملگر BETWEEN و تعیین یک بازه زمانی، این کار انجام شده است.
پس از اجرای هم‌زمان هر دو کوئری و مراجعه به آمار و اطلاعات I/O و Plan اجرایی خواهید دید که عدم استفاده از دو تابع YEAR و MONTH تا چه حدی می‌تواند به افزایش عملکرد کوئری و استفاده کمتر از منابع، کمک کند.

بنابراین همواره این نکته را به‌خاطر داشته باشید که هنگام استفاده از Scalar Functionها در بخش WHERE اگر تحت هر شرایطی مقادیر ستون‌ها توسط توابع دست‌خوش تغییرات شوند آن‌گاه به‌احتمال بسیار زیاد، ایندکس‌های موجود بر روی ستون‌ها در هنگام اجرای کوئری، توسط بهینه‌ساز مورد استفاده قرار نخواهند گرفت.

در قسمت بعد، بخش پایانی این مجموعه مقالات ارائه خواهد شد.

[download id=”29302″]

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

بررسی عملی سه روش پردازش داده‌های آفلاین‌ در اسپارک [بخش اول] قسمت ششم میکروسرویس: مدیریت داده‌ها در میکروسرویس بررسی عملی سه روش پردازش داده‌های آفلاین‌ در اسپارک [بخش دوم] TDE چیست