ترتیب اجرای دستورات کوئری در SQL SERVER - مقدمه

یکی از آموزش های جذاب و البته پرکاربرد در زمینه کوئری نویسی و افزایش سرعت و کارایی، ترتیب اجرای دستورات کوئری در SQL SERVER است. اینکه بدانیم پس از تحویل دستورات T-SQL به انجین sql server، آن ها با چه ترتیبی اجرا می شوند؟ یا سؤالاتی از قبیل:

     _ آیا دستورات به همان ترتیبی که ما می نویسیم اجرا خواهند شد؟ 

     _ در یک دستور select ساده، ابتدا فیلدهای مورد نظر ما از جدول انتخاب می شوند و بعد شرط روی رکوردها اعمال می شود؟

      _ در عملیات جوین، جداول با همان ترتیبی که در کوئری می نویسیم join زده می شوند؟

      _ شاید هم این سؤال چالشی که اگر شرط های کوئری هم در قسمت ON و هم در قسمت where باشند، کدامیک زودتر اعمال خواهند شد؟

     _ آیا ممکن است Optimizer با هدف بهینه شدن کوئری، آن را بازنویسی کند؟!

     _ در کوئری های پیچیده و یا تو در تو، ترتیب اجرای دستورات کوئری چگونه مشخص می شود؟ و ...

 در آموزش ترتیب اجرای دستورات کوئری در SQL SERVER، ابتدا با انواع ترتیب های اجرایی یک کوئری آشنا می شویم و در ادامه با استفاده از مثال هایی عملی، این ترتیب ها و نکات پیرامون آن ها را بررسی می کنیم.

---

ترتیب اجرای دستورات کوئری در SQL SERVER - انواع ترتیب های اجرایی

 در این قسمت ابتدا به معرفی اجمالی انواع ترتیب های اجرایی می پردازیم و سپس با استفاده از یک مثال دنیای واقعی با این مفاهیم بهتر آشنا خواهیم شد و در ادامه مجدداً بحث را با مثال های عملی در sql server ادامه خواهیم داد.

1. ترتیب نوشتاری(Typed Order):

در واقع همان ترتیبی است که دستورات یک کوئری را می نویسیم. مثلاً در یک کوئری جهت انتخاب رکوردهایی خاص از یک جدول، ابتدا نام فیلدهای مورد نظر را مقابل دستور select می نویسیم و سپس نام جدولی که باید اطلاعات از آن استخراج شود و نهایتاً هم شرط/شروط کوئری نوشته می شوند.

2. ترتیب پردازش منطقی کوئری(Logical Query Processing):

یک مفهوم پایه بویژه برای Developerها(مبتدی تا پیشرفته) محسوب می شود و در آن،ترتیب اجرای دستورات کوئری بر اساس قوانین مدل رابطه ای مشخص می شود. 

نکته: ترتیب پردازش منطقی کوئری، مفهومی است که توسط itzig ben-gan ابداع شده و در هیچ منبع رسمی ذکر نشده است.

نکته: در این ترتیب، به مسائل کارایی(performance) و یا موارد مرتبط با بخش فیزیکی(چیدمان رکوردها، نحوه ذخیره سازی و ...) توجهی نداریم.

3. ترتیب پردازش فیزیکی کوئری(physical Query Processing):

ترتیب پردازش فیزیکی توسط Optimizer مشخص می شود. معیار اصلی Optimizer در تعیین ترتیب اجرای دستورات کوئری هزینه می باشد. به عبارتی دیگر Optimizer ترتیب های مختلفی جهت اجرای یک کوئری در نظر می گیرد و سپس از بین آن ها، ترتیبی که کمترین هزینه اجرایی(مصرف منابع) را داشته باشد انتخاب می کند.

4. ترتیب اجرای واقعی کوئری:

این ترتیب در واقع مربوط به زمانی است که دستورات کوئری به صورت واقعی توسط Storage Engine اجرا می شوند.

نکته: در اکثر مواقع ترتیب اجرای واقعی کوئری با ترتیب پردازش فیزیکی آن یکسان است.

تا اینجا یک آشنایی ابتدایی با انواع ترتیب اجرای دستورات کوئری پیدا کردیم. در ادامه با یک مثال دنیای واقعی، مفهوم این ترتیب ها(به غیر از ترتیب نوشتاری) را بطور کامل درک خواهیم کرد و مجدداً بحث را در sql server ادامه می دهیم.

فرض کنید شما قصد مسافرت داشته باشید. در ابتدا بز اساس آنچه در مورد مسافرت عُرف است مبدأ و مقصد را مشخص می کنیم. دقت کنید در این مرحله به مواردی مانند: مسافت، زمان، هزینه و ... توجهی نداریم و مثلاً می گوییم قصد داریم از اصفهان به مشهد برویم(در ترتیب پردازش منطقی نیز بر اساس قوانین مدل رابطه ای تصمیم گیری می کنیم و به سایر موارد مانند کارایی توجهی نداریم).

بعد از تعیین مبدأ و مقصد، نوبت به انتخاب مسیر می رسد و معمولاً در این مرحله با کسب اطلاعات از منابعی مانند: سایت ها(گوگل مپ - آب و هوا شناسی)، دوستان و ... مناسب ترین مسیر از بین مسیرهای ممکن را برای مسافرت انتخاب می کنیم. جهت انتخاب مناسب ترین مسیر، ممکن است هر فرد معیارهای متفاوتی داشته باشد به عنوان مثال: کمترین مسافت، سرسبزی و طراوت مسیر، پر ترافیک نبودن و ... (در ترتیب پردازش فیزیکی بر اساس اطلاعات بدست آمده از ایندکس ها، Statistics و... از بین روش های ممکن جهت اجرای دستورات کوئری، کم هزینه ترین آن ها انتخاب می شود).

بعد از انتخاب مسیر، منابع لازم برای مسافرت را آماده میکنیم و حرکت از مبدأ و مطابق با مسیر تعیین شده به سمت مقصد شروع می شود(ترتیب اجرای واقعی کوئری نیز معمولاً مانند ترتیب اجرای منطقی تعیین شده توسط Optimizer است).  همانطور که در یک مسافرت ممکن است به دلایلی مانند بارندگی، مسدود شدن راه و ... تغییراتی در مسیر از پیش تعیین شده خودمان بدهیم. در اجرای واقعی یک کوئری نیز ممکن است ترتیب فیزیکی تعیین شده توسط Optimizer رعایت نشود. مثلاً ممکن است امکان اجرای موازی یک کوئزی فراهم شود و یا اینکه تغییراتی در Statistics جدول/جداول موجود در کوئری ایجاد شده باشد و ...

 اگر بخواهبم به طور خلاصه مروری بر مطالب ذکر شده تا این قسمت داشته باشیم به این نحو است که در ابتدا دستورات یک کوئری را می نویسیم و سپس بر اساس قوانین مدل رابطه ای یک ترتیب برای آن در نظر می گیریم. در مرحله بعد این کوئری به Optimizer تحویل داده می شود که با توجه معیار کمترین هزینه اجرایی، یک ترتیب برای اجرای کوئری ما تعیین می کند و آن را جهت اجرا به Storage engine تحویل داده، نهایتاً کوئری ما اجرا و نتیجه برگردانده خواهد شد. در آموزش اس کیو ال یا سی کو ال اشاره مختصری بر ترتیب پردازش منطقی داشتیم. با توجه به اینکه در طراحی یک کوئری، همواره ترتیب پردازش منطقی مورد نظر است قصد داریم این مورد را با جزئیات بیشتری بررسی کنیم و به دو ترتیب پردازش فیزیکی و واقعی در قسمت بعدی خواهیم پرداخت.

همانطور که قبلاً اشاره شد مفهوم ترتیب پردازش منطقی کوئری توسط itzig ben-gan ابداع شده است و در آن ترتیب اجرای دستورات کوئری بر اساس قوانین مدل رابطه ای تعیین می شود. در شکل زیر فلوچارت مربوط به این ترتیب را مشاهده می کنید:

همانطور که در تصویر بالا مشخص است، در پردازش منطقی یک کوئری، ابتدا دستور From اجرا می شود و در ادامه بررسی می شود که آیا جدول دارای عملگر(join- pivot - apply) هست یا نه. شرط where، گروهبندی و ... نیز از دستورات بعدی در کوئری هستند که اجرا می شوند. جهت مشاهده این فلوچارت به صورت واضحتر و با توضیحات بیشتر، پوستر ترتیب پردازش منطقی کوئری تهیه شده توسط itzig ben-gan را دانلود کنید. اما در تصویر زیر به طور خلاصه ترتیب پردازش منطقی کوئری با شماره گذاری تعیین شده است:

به عبارتی دیگر، ترتیب اجرای دستورات کوئری بر اساس پردازش منطقی مطابق شکل زیر است:

 

---

ترتیب اجرای دستورات کوئری در SQL SERVER - ارائه چند مثال

 تا این قسمت از آموزش، با مفهوم ترتیب اجرای دستورات کوئری و انواع آن آشنا شدیم. در ادامه قصد داریم با ارائه مثال هایی در sql server فواید آشنایی با این ترتیب ها در طراحی کوئری بهینه، جلوگیری از بروز خطا و تولید خروجی اشتباه در اجرای کوئری را با هم ببینیم. در ابتدا مثال را با یک کوئری ساده شروع و به تدریج به سمت کوئری های پیچیده تر می رویم. در هر مثال ارائه شده، به نکات کاربردی آن دقت ویژه ای داشته باشید. کوئری زیر را جهت استخراج مجموع فروشی که فردی با فامیل "بندامیر" در ماه های مختلف داشته طراحی کرده ایم:

SELECT  [season] ,sum([income]) as total
  FROM [dbo].[orders]
     where seller=N'بندامیر'
        group by [season]
	    order by total desc

در ترتیب نوشتاری می گوییم هر ماه را به همراه مجموع فروش آن از جدول tblEmployees  نمایش بده که فامیل فروشنده آن "بندامیر" است و گروهبندی بر اساس فیلد season باشد و نهایتاً خروجی بر اساس مجموع فروش هر ماه به صورت نزولی نمایش داده شود.

اما بر اساس ترتیب پردازش منطقی، در ابتدا سراغ جدول tblEmployees می رویم(دستور from) سپس از بین فروشنده های موجود در جدول آن هایی را استخراج می کنیم که فامیل آن ها "بندامیر" است(شرط where) در ادامه رکوردهای استخراج شده را بر اساس ماه(فیلد season) گروهبندی کرده و به دستور select می رویم. در دستور select ابتدا sum را برای هر گروه محاسبه می کنیم(ارزیابی expression) و برای هر رکورد تنها فیلدهای season و total انتخاب می شوند(select list). در پایان نیز فیلد season به همراه مقدار محاسبه شده مجموع فروش به صورت نزولی(order by) بر حسب مقدار مجموع فروش(total) نمایش داده می شود.

جهت مشاهده ترتیب پردازش فیزیکی، باید پلن تخمینی یا Estimated Execution Plan را مشاهده کنیم(جهت آشنایی با نحوه فعال کردن و خواندن Execution Plan، آموزش نحوه خواندن Execution Plan را مطالعه کنید):

 

با توجه به پلن اجرایی بالا می بینیم که در ابتدا جدول tblEmployees اسکن شده و در ادامه یک عملیات مرتب سازی(sort) روی ستون season انجام شده است. دقت کنید این عملیات مرتب سازی پیش نیاز انجام تابع تجمی sum است. در مرحله سوم هم آیکن aggregate نشان دهنده انجام گروهبندی بر اساس فیلد season و محاسبه مجموع فروش ها(sum) است. دقت کنید در این مرحله گروهبندی انجام شده و سراغ ارزیابی عبارت در دستور select رفته ایم. (مرحله چهارم نیز توسط sql server جهت نمایش مقدار NULL، در صورت عدم وجود مقدار در یک گروه، اضافه شده است) مرتب سازی نزولی رکوردهای خروجی بر اساس مجموع فروش نیز در مرحله پنجم انجام می شود(sort) و نهایتاً  نمایش نتیجه در خروجی.

نکته: در پردازش منطقی به ساختار فیزیکی(چیدمان رکوردها، وجود ایندکس و ...) و کارایی توجهی نداریم اما در پردازش فیزیکی، Optimizer این فاکتورها را در نظر می گیرد.

مثلاً در ترتیب منطقی کوئری بالا، تنها می گوییم ابتدا رکوردهای جدول tblEmployees خوانده شود اما در پردازش فیزیکی به دلیل وجود ایندکس در جدول، عملیات خواندن رکوردها جهت افزایش کارایی با استفاده از ایندکس موجود انجام می شود.

نکته: در زمان تبدیل ترتیب پردازش منطقی به ترتیب پردازش فیزیکی، اگر sql server اطمینان یابد که با تغییر دادن ترتیب اجرای دستورات کوئری، تغییری در خروجی تولید شده ایجاد نمی شود، در جهت افزایش کارایی و کاهش هزینه، این تغییر را در پردازش فیزیکی کوئری اعمال می کند.

با توجه به نکته بالا، نتیجه می گیریم که الزاماً همیشه ترتیب پردازش فیزیکی با پردازش منطقی یکسان نیست. (در ادامه مثالی در این زمینه ارائه خواهد شد)

جهت مشاهده ترتیب پردازش واقعی، باید پلن واقعی یا Actual Execution Plan را مشاهده کنیم:

 

از مقایسه دو یلن تخمینی و واقعی، متوجه خواهیم شد که در این مثال، ترتیب پردازش فیزیکی و واقعی یکسان هستند.(اکثر مواقع به همین صورت است).

 کوئری زیر را جهت استخراج محل اشتغال افرادی با فامیل "بندامیر" طراحی کرده ایم:

SELECT   Department.WorkPlace,Department.NationalCode as code
   FROM  Employees INNER JOIN  Department 
       ON Employees.NationalCode = Department.NationalCode
		 where  famil=N'بندامیر'
		  order by code

در ترتیب پردازش منطقی، جهت انجام جوین بین جداول، با توجه به فلوچارت ارائه شده توسط  itzig ben-gan، در ابتدا بین دو جدول، ضرب کارتزین(cartesian product) انجام می شود و سپس شرط موجود در عبارت ON بر روی رکوردها اعمال می شود:

پس ترتیب پردازش منطقی به شکل زیر خواهد شد:

 و اما ترتیب پردازش فیزیکی و بررسی نکاتی جالب در آن(جهت آشنایی با جوین آموزش join چیست از مجموعه چیستان را مطالعه کنید):

 با توجه به Execution Plan بالا نکات زیر مشهود است:

1.  Optimizer در ابتدا سراغ جدول Employees رفته و شرط where را بر روی رکوردها اعمال کرده است.(تنها رکوردهایی با مقدار "بندامیر" در فیلد famil استخراج شود) در حالیکه بر اساس ترتیب پردازش منطقی، اعمال شرط where بعد از جوین دو جدول انجام می شود. اما دقت کنید اعمال شرط where قبل از انجام جوین، به دلیل اینکه تعداد رکوردهای جدول Employees جهت عملیات بعدی کاهش پیدا می کند، منجر به افزایش کارایی خواهد شد.

2. در مرحله دوم، عملیات مرتب سازی(sort) انجام شده است! در حالیکه بر اساس ترتیب پردازش منطقی، مرتب سازی(Order By) جزو عملیات پایانی محسوب می شود. اما انجام مرتب سازی در این مرحله، هوشمند بودن optimizer را می رساند. بدون هیچ گونه تغییری در نتیجه خروجی، سرغت انجام عملیات افزایش می یابد.

3. بعد از فیلتر کردن(where) رکوردهای جدول Employees  و مرتب سازی نتیجه، تازه نوبت به جوین دو جدول بر اساس شرط موجود در عبارت ON می رسد. دقت کنید در اینجا جهت انجام عملیات جوین از ضرب کارتزین استفاده نشده است چرا که کارتزین از عملیات بسیار پرهزینه در جوین محسوب می شود و Optimizer تا حد ممکن از رخ دادن این عملیات جلوگیری می کند.

پس در این مثال دیدیم که ترتیب پردازش فیزیکی کوئری می تواند متفاوت با ترتیب پردازش منطقی آن باشد به شرطی که منجر به تولید نتیجه متفاوتی نشود. این تغییرات توسط Optimizer و با هدف افزایش کارایی صورت می گیرد. در این مثال نیز ترتیب اجرای واقعی با ترتیب پردازش فیزیکی یکسان است.

---

ترتیب اجرای دستورات کوئری در SQL SERVER - جمع بندی

قبل از جمع بندی توضیحات ارائه شده پیرامون ترتیب اجرای دستورات کوئری، به منظور مروری بر مطالب، یک سؤال مطرح می کنیم. به نظر شما اگر کوئری مثال اول را به این صورت تغییر دهیم که مجموع فروش غیر از تیر ماه را نمایش دهد. خروجی چه خواهد بود؟

SELECT  [season] as months ,sum([income]) as total
  FROM [dbo].[orders]
     where seller=N'بندامیر' and months Not like N'تیر'
        group by [season]
	    order by total desc

با اجرای کوئری بالا، خطای زیر نمایش داده می شود:

ستونی با نام "months" وجود ندارد! شاید با مشاهده این خطا تعجب کنید چرا که در قسمت select، نام مستعار "months" جهت نمایش در خروجی تعریف شده است. اما دلیل بروز این خطا:

بر اساس ترتیب پردازش منطقی، در ابتدا سراغ جدول orders می رویم و سپس در شرط where می گوییم رکوردهایی را انتخاب کن که فروشنده آنها برابر با "بندامیر" باشد(بندامیر = seller) و ماه فروش محصولات نیز "تیر" نباشد('تیر' months not like). دقت کنید months در دستور select به عنوان نام مستعار(alias) تعریف شده است در حالیکه در این مرحله ما هنوز سراغ دستور select نرفته ایم و months شناخته شده نیست.

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

آشنایی با ترتیب اجرای دستورات کوئری، در افزایش سرعت و کارایی کوئری و نیز جلوگیری از ایجاد خطا در زمان اجرای کوئری بسیار حائز اهمیت است. در مجموع هر چه میزان آشنایی شما با زبان T-SQL و قواعد و قوانین آن افزایش یابد به همان میزان در طراحی کوئری و سرعت اجرای برنامه هایتان تأثیر گذار خواهد بود.

• قبلی
• بعدی