اصلاح آرگومان جستجو در کوئری

سلام دوستان

امروز می خواهم راجع به مشکل بخصوصی که به indexing در SQL Server مربوط می شود صحبت کنم.

تعریف مشکل

نمونه کوئری زیر را در نظر بگیرید، مثل این کوئری را صدها بار در SQL Server دیده اید:

[sql] — Results in an Index Scan
SELECT*FROM Sales.SalesOrderHeader
WHEREYEAR(OrderDate)= 2005 ANDMONTH(OrderDate)= 7
GO
[/sql]

با این کوئری ساده ما اطلاعات فروش را راجع به ماه خاصی از یک سال خاص را درخواست می کنیم.خیلی پیچیده نیست. اما متاسفانه این کوئری خیلی خوب عمل نمی کند. حتی با وجود یک Nonclustered Index روی ستون OrderDate. وقتی به پلن اجرا نگاه می کنید، می توانید ببینید که Query Optimizer از Nonclustered Index که روی OrderDate تعریف شده استفاده کرده اما متاسفانه SQL Serverیک اسکن کامل به جای اینکه یک عمل Seek موثر از ایندکس انجام میدهد.

در حقیقت این یک مشکل برای SQL Server محسوب نمی شود، این روشی است که دیتابیس های رابطه ای کار می کنند و فکر می کنند! به محض اینکه یک عبارت (فراخوانی توابع یا محاسبات) روی یک ستون دارای ایندکس (که Search Argument هم نامیده می شود) اعمال کنید، موتور دیتابیس باید آن ایندکس را Scan کند، به جای اینکه یک عمل Seek انجام دهد.

راه حل

برای اینکه عمل اسکن ایندکس به Seek تغییر کند، باید کوئری را طوری بنویسیم که دیگر تابع DATEPARTفراخوانی نشود:

[sql]
— Results in an Index Seek
SELECT*FROM Sales.SalesOrderHeader
WHERE OrderDate >=’20050701’AND OrderDate <‘20050801’
GO
[/sql]

و همانطوری که در کوئری بازنویسی شده می بینید، این کوئری نتایج مشابهی برمی گرداند، اما ما فقط فراخوانی تابع DATEPART را حذف کرده ایم. وقتی به پلن اجرا نگاه می کنید، می بینید که SQL Server یک عمل Seek انجام می دهد- در این کوئری عمل Seekاصطلاحاً Partial Range Scanنامیده می شود: SQL Server مقدار اول را جستجو می کند و عمل اسکن را تا زمانی که به آخرین مقدار درخواستی در محدوده نرسیده ادامه می دهد. اگر مجبور هستید در متن ستون های ایندکس دار فراخوانی کنید، شما باید مطمئن شوید که این فراخوانی به تابع در سمت راست ستون شما در کوئری اجرا می شوند.

اجازه دهید به این مثال نگاهی بیندازیم. اجازه دهید به کوئری زیر نگاهی بیندازیم. کوئری زیر ستون دارای ایندکس CreditCardIDرا به دیتا تایپ CHAR(4)تبدیل می کند:

[sql] — Results in an Index Scan
SELECT*FROM Sales.SalesOrderHeader
WHERECAST(CreditCardID ASCHAR(4))=’1347′
GO
[/sql]

وقتی نگاهی دقیق تر به پلن اجرا دارید، می توانید ببینید SQL Server ایندکس Nonclustered را به تمامی اسکن کرده است. اگر شما این تبدیل را در سمت راست ستون ایندکس دار در کوئری اجرا کنید، می توانید دوباره می توانید فراخوانی روی ستون ایندکس دار را حذف کنید و SQL Server خواهد توانست عمل Seek انجام دهد:

[sql] — Results in an Index Seek
SELECT*FROM Sales.SalesOrderHeader
WHERE CreditCardID =CAST(‘1347’ASINT)
GO
[/sql]

خلاصه

همان طور که در این پست دیدید، بسیار حائز اهمیت است که هیچ تابعی مستقیم یا غیر مستقیم روی ستون های دارای ایندکس فراخوانی نکنید. در غیر اینصورت SQL Server باید ایندکس را Scan کند، به جای اینکه یک عمل Seek موثر انجام دهد.

منبع: آموزش برنامه نویسی نیک آموز

هیچوقت به داشتن سرکارگر عادت نکن

ژانویه 2016، تورج عزیزی

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

تورج عزیزی

مشاوره، تحقیقات و انجام پروژه در زمینه SQL Server Performance Tuning

15 دیدگاه

میلاد فیروزی
دی ۲۶, ۱۳۹۴ در ۱۰:۳۷
بسیار جالب بود جناب عزیزی

پس نتیجه می گیریم به جای اینکه SQL را وادار کنیم تا اطلاعات یافته شده را به فرمت دلخواه ما CAST کند ما بیاییم فرمت اطلاعات ورودی را به فرمت ذخیره شده CAST کنیم

بسیار عالی

پاسخ
محمد خاندردی
دی ۲۶, ۱۳۹۴ در ۱۱:۱۷
بسیار عالی و موثر توضیح دادید … با تشکر

پاسخ
محمدحسین عبدالهی
دی ۲۷, ۱۳۹۴ در ۰۰:۲۰
بسیار عالی
متشکرم
فقط اگر میشد بعد از بندی که اشاره کردین به Partial Range Scan تصویر نتیجه رو هم نمایش میدادین به نظرم بهتر میشد.

پاسخ
سعید شیرزادیان
دی ۲۷, ۱۳۹۴ در ۰۰:۳۲
سلام

مقاله خیلی کاربردی و مفید بود

پاسخ
محمدحسین عبدالهی
دی ۲۷, ۱۳۹۴ در ۰۰:۴۸
آقای تورج عزیزی مجدد تشکر می کنم بابت مقاله خوبتون

من دو تا کوئری شما رو در AdventureWorks2014 تست کردم در ابتدا هیچ تفاوتی مشاهده نشد.
بعد برای جدول SalesOrderHeader یک ایندکس گذاشتم و تفاوت اساسی مشاهده شد! (93% به 7%)

به این صورت نتیجه گیری کردم :

در شرط جستجو ، زمانی که تبدیل نوع یک ستون – ایندکس گزاری شده – در سمت راست باشد اس کیو ال سرور از Index Seek برای نمایش نتیجه استفاده می کند. ( نکته : فقط برای ستون ایندکس گذاری شده این موضوع تأثیر گذار هست)

می خواستم ببینم درست هست این نتیجه گیری؟

پاسخ
1. مسعود طاهری
  دی ۲۷, ۱۳۹۴ در ۰۷:۴۳
  سلام
  
  در مقاله به همین موضوعی که شما فرمودید اشاره شده و نتیجه گیری شما درست است
  
  به مثال های زیر هم توجه کنید
  
  ad: Select … WHERE isNull(FullName,’Ed Jones’) = ‘Ed Jones’
  
  Fixed: Select … WHERE ((FullName = ‘Ed Jones’) OR (FullName IS NULL))
  
  Bad: Select … WHERE SUBSTRING(DealerName,4) = ‘Ford’
  
  Fixed: Select … WHERE DealerName Like ‘Ford%’
  
  Bad: Select … WHERE DateDiff(mm,OrderDate,GetDate()) >= 30
  
  Fixed: Select … WHERE OrderDate < DateAdd(mm,-30,GetDate())
  
  ضمنا مفهوم SARGable predicate را در نظر داشته باشید
  
  http://www.sqlusa.com/bestpractices/sargable/
  
  پاسخ
  1. محمدحسین عبدالهی
    دی ۲۷, ۱۳۹۴ در ۰۹:۲۳
    تشکر استاد طاهری
تورج عزیزی
دی ۲۷, ۱۳۹۴ در ۰۹:۲۲
سلام محمد حسین جان عزیز،

شما اگر تابعی روی یک ستون ایندکس دار در یک شرط اعمال کنید، ساختار B-Tree از حالت خودش خارج میشه یعنی نمیشه مطمئن شد که سمت چپ درخت حتماً مقادیر کمتری نسبت به سمت راست درخت داره…. وبنابراین تمام نودهای درخت باید بررسی بشن.

پاسخ
1. محمدحسین عبدالهی
  دی ۲۷, ۱۳۹۴ در ۱۰:۰۲
  سلام
  تشکر آقای عزیزی
  توضیح خلاصه و مفیدی بود
  
  پاسخ
فرشید علی اکبری
دی ۲۷, ۱۳۹۴ در ۱۴:۱۱
سلام

بسیار عالی؛ مطلب خوبی بود.

پاسخ
مهدی ربانی ذبیحی
دی ۲۷, ۱۳۹۴ در ۱۵:۳۴
سلام بسیار عالی و کاربردی بود ممنون

پاسخ
فرزین
دی ۲۸, ۱۳۹۴ در ۲۰:۳۴
بسیار عالی

پاسخ
ساقی
بهمن ۳, ۱۳۹۴ در ۱۰:۲۷
متشکریم.نکته خیلی عالی بود

پاسخ
رقيه حجازی
بهمن ۳, ۱۳۹۴ در ۱۶:۲۲
تشکر

به نکته ریز و جالبی اشاره کردید

پاسخ
احمد رضاپور
بهمن ۴, ۱۳۹۴ در ۱۵:۵۸
بسیار خوب بود؛ با تشکر..

پاسخ

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

تورج عزیزی

15 دیدگاه

میلاد فیروزی

محمد خاندردی

محمدحسین عبدالهی

سعید شیرزادیان

محمدحسین عبدالهی

مسعود طاهری

محمدحسین عبدالهی

تورج عزیزی

محمدحسین عبدالهی

فرشید علی اکبری

مهدی ربانی ذبیحی

فرزین

ساقی

رقيه حجازی

احمد رضاپور

ارسال یک نظر لغو پاسخ

دوره‌های در حال ثبت نام و اجرا

خدمات حرفه‌ای نیک آموز

استخدام برنامه نویس

پُرفروش‌ترین محصولات

اینماد سایت