مقدمه
در قسمت اول دیدید که چگونه با استفادهی نامناسب از عملگر 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]
10 دیدگاه
Mehdi
ضمن تشکر از شما، مطلب عنوان شده خیلی واضح است.
مهدی شیشه بری شیشه بری
از ارسال دیدگاهتان بینهایت سپاسگزارم.
گاهی بسیاری از افراد حرفهای با رعایت نکردن همین نکات بهظاهر ساده میتوانند زمینههای بروز عدم کارآیی دیتابیس را فراهم کنند.
قاسمی
سلام و تشکر
بسیار عالی است.
مورد مشابهی داشتم، ولی چون در ورودی کاربر، مشخص نیست اسم و فامیل کدام است این روش کاربرد نداشت. آیا راهی هست که روی ترکیب دو فیلد ایندکس گذاشته شود؟
بیصبرانه منتظر قسمتهای بعدی مقاله هستم.
مهدی شیشه بری شیشه بری
سوال شما کمی گنگ است.
فکر میکنم اگر ورودیها از طریق برنامه کاربردی، کنترل شده و بهصورت پارامتر به یک SP ارسال شوند دیگر مشکلی به وجود نیاید.
فرشید علی اکبری
سلام دوست عزیز
مرسی و تشکر از زحمتی که بابت کیفیت خوب مقاله هاتون میکشید.
موفق باشید.
حسن ضرابی
با سلام و خسته نباشید خدمت شما دوست عزیز و گرامی
از بابت قسمت دوم این مقاله بسیار ممنونم خیلی زحمت کشیدید و خیلی هم عالی بود
ممونم از شما
Mehdi
سلام و عرض ادب
Data Partition بر روی جدولی که دارای یک CCI است(یعنی ساختار به صورت Column Base است) می تواند باعث افزایش Performance شود؟ چرا ؟
با تشکر.
آرزو محمدزاده
با سلام و عرض ادب
به نقل از مهندس مهدی شیشه بری
قطعا بله. ساختار Columnstore Index علاوه بر مزیت Column Base، قابلیت فشردهسازی را هم خواهد داشت. قطعا پیادهسازی Data Partitioning هم به اجرای سریعتر و افزایش Performance کمک خواهد کرد چرا که Data Partitioning با ایجاد مرزها یا همان Boundaryها موجب خواهد شد تا تعداد عملیات I/O در حین اجرای کوئری کاهش داشته باشد.
مهدی شیشه بری شیشه بری
سلام دوست عزیز
همانطور که خودتان هم گفتید وقتی طرحی از قبل وجود نداشته باشد یا طراحی با نقص باشد همین اتفاقات خواهد افتاد.
به نظر شما آیا میتوان یک برج مسکونی را بدون طرح و نقشه ساخت یا با طرحی نصفه و نیمه شروع کرد؟
دورهای هم که به آن اشاره میکنید با عنوان کوئرینویسی است نه تحلیل دیتابیس. هدف آن دوره هم تدریس سرفصلهای کوئرینویسی بوده.
شاید در آینده نزدیک یک دوره تحلیل و طراحی دیتابیس در نیکآموز داشته باشیم منتهی باید ببینیم میزان علاقهمندی دوستان در چه حد است.
شاد و پیروز باشید
Helia
فوق العاده بود استاد
سرافراز و پیروز باشید