خانه SQL Server قاتلین ایندکسها در SQL Server [بخش چهارم] SQL Server نوشته شده توسط: مهدی شیشه بری ۱۳ مهر ۱۳۹۶ زمان مطالعه: 10 دقیقه ۵ (۱) مقدمه در قسمتهای اول، دوم و سوم این مجموعه مقالات دیدید که چگونه با نوشتن یک کوئری نامناسب در هنگام جستجوی مقادیر مختلف از جداول، ناخواسته زمینههای لازم برای عدم استفاده از ایندکسهای موجود را فراهیم میکنیم! نکته مهم: تمامی کوئریهای این مجموعه مقالات، بر روی دیتابیس AdventureWorks2016 و در محیط SQL Server Management Studio 2016 و ۲۰۱۷ اجرا شده است. قاتل شماره ۴ – توابع تک مقدار یا اسکالر (Scalar Functions) در این قسمت میخواهم درخصوص عوارض استفاده از توابع تکمقدار یا Scalar Functionها در هنگام نوشتن کوئریها و در بخش WHERE با شما صحبت کنم! همانطور که میدانید مایکروسافت توابع مختلفی را بهشکل Built-in در SQL Server ارائه کرده است. این توابع در قالب دستهبندیهای مختلفی، قابلیت کار با انواعداده از قبیل رشتهها، اعداد، تاریخ، زمان و … را فراهم میکنند. ما میتوانیم با استفاده از اینگونه توابع علاوهبر صرفهجویی در زمان، به افزایش کارآیی کوئریها نیز کمک شایانی کرده باشیم. عملکرد این توابع بهگونهای است که با تغییر در شکل اصلی مقادیر فیلدها، مقادیر مناسبتری را برای نوشتن کوئریهایِ مختلف فراهم خواهد کرد. این کار حتی از طریق توابعی که توسط کاربران و با عنوان User Define Function شناخته میشود نیز امکانپذیر است اما باید توجه داشت که چنین تغییراتی میتواند موجب خنثی شدن عملکرد ایندکسهای موجود بر روی مقادیر فیلدهای جداول شود چرا که استفاده از اینگونه توابع، شکل اصلی مقادیر فیلدها را دستخوش تغییر میکند. بدیهی است که این مقادیر، هیچگونه سنخیتی با مقادیر ایندکسگذاری شده در حین عملیات ایندکسگذاری ندارند. بهعبارت سادهتر هیچگونه آماری از میزان فراوانی آنها در دسترس نبوده تا بر اساس آنها بهینهسازِ SQL Server (Query Optimizer) اقدام به ایجاد یک Plan بهینه از آنها داشته باشد. برای آنکه تاثیر اینگونه از توابع را بر روی روند اجرایی کوئریها ببینید به اسکریپتهای زیر توجه کنید: 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 هر دو کوئری بالا قرار است اطلاعات مربوط به رکوردی را نمایش دهند که مقدار فیلد FirstName آن برابر با Gustavo است. پس از اجرای همزمان هر دو کوئری و با مراجعه به آمار و اطلاعات I/O خواهید دید، کوئری دوم که در بخش WHERE از تابع RTRIM استفاده کرده است، نسبت به کوئری اول تعداد Page بیشتری را خوانده است! این اختلاف بسیار فاحش، در مقایسهی Plan اجرایی و میزان استفاده از منابع نیز بهوضوح قابل مشاهده استواقعیت این است که برای رفع این معضل نمیتوان یک راهکار عمومی ارائه کرد. شاید توجه به این جمله کلیدی، چندان دور از واقعیت نباشد: “پیشگیری بهتر از درمان است!” ارائه یک راهحل مناسب، کاملا متناسب با پارامترهایی همچون مدل کسبوکار و قواعد حاکم بر آن، مدل طراحی دیتابیس، ساختار رکوردهای موجود در جداول و … است. بهعنوان مثال فرض کنید قرار است تمامی سفارشاتِ مرتبط با سال ۲۰۱۲ و ماه ۱۲ را از جدول SalesOrderHeader در خروجی نمایش دهیم. برای انجام این کار میتوان به دو روش اقدام کرد. به اسکریپتهای زیر توجه کنید: 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 از آنجا که بر روی فیلد OrderDate ایندکسی مناسبی وجود ندارد، ابتدا بر روی آن ایندکسی از نوع Non-Clustered ایجاد میکنیم. در ادامه خواهید دید که در اولین کوئری از توابع YEAR و MONTH برای استخراج مقادیر سال و ماه استفاده شده است اما در کوئری دوم صرفا با استفاده از عملگر BETWEEN و تعیین یک بازه زمانی، این کار انجام شده است. پس از اجرای همزمان هر دو کوئری و مراجعه به آمار و اطلاعات I/O و Plan اجرایی خواهید دید که عدم استفاده از دو تابع YEAR و MONTH تا چه حدی میتواند به افزایش عملکرد کوئری و استفاده کمتر از منابع، کمک کند. بنابراین همواره این نکته را بهخاطر داشته باشید که هنگام استفاده از Scalar Functionها در بخش WHERE اگر تحت هر شرایطی مقادیر ستونها توسط توابع دستخوش تغییرات شوند آنگاه بهاحتمال بسیار زیاد، ایندکسهای موجود بر روی ستونها در هنگام اجرای کوئری، توسط بهینهساز مورد استفاده قرار نخواهند گرفت. در قسمت بعد، بخش پایانی این مجموعه مقالات ارائه خواهد شد. چه رتبه ای میدهید؟ میانگین ۵ / ۵. از مجموع ۱ اولین نفر باش برچسب ها # create index sql server# Index در SQL Server# آموزش SQL Server# افزایش سرعت در Sql Server# انواع ایندکس در پایگاه داده# مفهوم Index در SQL Server معرفی نویسنده معرفی محصول مهدی شیشه بری دوره آموزش کوئری نویسی در SQL Server 1.000.000 تومان مقالات مرتبط ۰۳ خرداد مهندسی نرم افزار چالش های مدرن سازی معماری نرم افزار و راهکارهای آن علیرضا ارومند ۲۴ اردیبهشت مهندسی نرم افزار مدرن سازی معماری نرم افزار علیرضا ارومند ۱۹ اردیبهشت هوش تجاری بهبود عملکرد Tabular Model در SQL Server با سه تکنیک کاربردی مسعود طاهری ۱۰ اردیبهشت SQL Server استفاده از Credential و Proxy در SQL Server Agent حسن سلیمانی دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ حسن ضرابی ۰۳ / ۰۸ / ۹۶ - ۰۴:۱۶ با سلام و خسته نباشید خدمت جناب آقای مهندس مهدی شیشه بری جناب آقای مهندس از این مقاله بسیار عالی و مفید بسیار ممنونم با تشکر از شما پاسخ به دیدگاه حسن ضرابی ۰۳ / ۰۸ / ۹۶ - ۰۴:۱۶ با سلام و خسته نباشید خدمت جناب آقای مهندس مهدی شیشه بری جناب آقای مهندس از این مقاله بسیار عالی و مفید بسیار ممنونم با تشکر از شما پاسخ به دیدگاه خانم محمدزاده ۲۰ / ۰۷ / ۹۶ - ۰۵:۰۹ سلام و سپاس از همراهی شما دوست عزیز پاسخ به دیدگاه خانم محمدزاده ۲۰ / ۰۷ / ۹۶ - ۰۵:۰۹ سلام و سپاس از همراهی شما دوست عزیز پاسخ به دیدگاه