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

بررسی بهینه ساز بر مبنای اطلاعاتی صورت می گیرد که sql server درباره هر جدول و ایندکس ذخیره می کند. بهینه ساز،  اندازه تقریبی هر جدول وهمچنین اطلاعات مربوط به قابلیت انتخاب هر ایندکس را می داند. لذا می تواند از این اطلاعات برای تخمین تعداد عملیات ورودی/ خروجی لازم جهت بازیابی سطر های مورد نظر پرس وجو استفاده کند.

عموماً بهینه ساز ایندکس را به روش پیمایش جدول ترجیح می دهد، چرا که ایندکس ها معمولاً روش کار آمدتری را برای دستیابی به اطلاعات محسوب می شوند. با این وجود در مورد جدول کوچک، تعداد عملیات ورودی/خروجی مورد نیاز برای پیمایش یک جدول ممکن است نسبت به زمان استفاده از ایندکس کمتر باشد. در این حالت، بهینه ساز روش پیمایش جدول را بر خواهد گزید .همچنین اگر حاصل اجرای پرس وجو بازیافت سطر های بسیاری از جدول باشد. روش اخیر کارآمدتر از استفاده از ایندکس خواهد بود .

اشاره 

به منظور مشاهده تصمیمات بهینه ساز پرس وجو ، گزینه SHOW PLAN را فعال کنید این گزینه روش دستیابی( پیمایش جدول یا ایندکس)مورد استفاده برای هر جدول را نشان خواهد داد. برای فعال کردن گزینه فوق دستور زیر را به ابتدای پرس وجوی خود اضافه نمایید:

Set show plan on

نتایج چاپ شده توسط این گزینه ممکن است از نظر خوانایی تا اندازه ای مشکل باشد .

یک بار که شاخص های جدول تعریف شده باشند ، پرس و جو ها با استفاده از این شاخص ها می توانند برای آن جدول بهینه سازی گردند. به طور کلی گفتنی است ، تمام پرس و جو های نرم افزار SQL شما باید بهینه سازی شوند در نتیجه موتور بانک اطلاعات برای پیدا کردن ، مرتب کردن و متصل کردن سریع رکوردها و جداول می تواند از شاخص های موجود استفاده کند. این به این معنی است که فیلد های شاخص شده همیشه باید در دستورات SQL فرمان های(WHERE) و (ORDER BY) استفاده شوند .اگر فرمان (WHERE clause) به یک شاخص ارجاع یابد، رکورد ها به سرعت می توانند از فایل باز یابی شوند همانند مثال شاخص کتاب . اگر مجموعه نتایج مرتب گردد .فرمان(ORDER BY) به یک فیلد شاخص شده باز می گردد ، به طوری که داده ها خیلی سریعتر از آن روش مرتب می شوند .

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

برای روشن کردن این موضوع ما می توانیم هم یک پرس و جوی شاخص شده و هم شاخص نشده را اجرا نماییم :

پرس و جوی شاخص شده :

(SELECT from CALL-LOG WHERE ID>, 0000022000,TIME<9)

این دستور تمامی رکورد ها را از جدول (CALL-LOG) که تا قبل از ساعت 9 صبح فرمانی بدان اضافه می شده است و کد تماس بزرگتر از 0000022000 می باشد، برمی گرداند. زیرا شاخص اولیه بر اساس کد تماس است .لذا برنامه راه انداز ODBC می تواند سریعاً رکوردها را بازیابی کند . در اینجا گلچینی از فایل ثبت وقایع (LOG FILE) می باشد :

استراتژی بهینه سازی :

(file 1) = f:/odbc/LOCAL_CALL_Hist/data/call_log 
 Order_knum=-1 
 (Selected) Predicate: 1 constraints 
 *!: (file 1)(knum= 0, kseg= 1) ID(bracket head)

گزاره : قید ها

 *: (file 1)(knum= -1, kseg= -1) ID(no bracketing)

توجه کنید استراتژی بهینه سازی به كار رفته حاوي چندين قطعه اطلاعاتي مي باشد . اول از همه براي اينكه، نشان دهد كه براي ادامه بهينه سازي قادر است شاخصی را انتخاب نماید. ثانیا، برای اتمام بهینه سازی دو گزینه ممکن را لیست می کند، یکی بجای فیلد کد(شناسه) و یکی بجای فیلد زمان. ابتدا فیلد کد(شناسه) لیست می گردد و the knum=0, kseg= 1 که نشان می دهد که آن کلید اولیه است. خط دوم که لیست شده است درمورد فیلد زمان می باشد. هرچند که knum و kseg آن فیلد منفی یک هستند، این نشان می دهد که هیچگونه شاخصی که برپایه فیلد زمان وجود ندارد.

در مثال بالا، برنامه راه انداز ODBC برای ادامه بهینه سازی از شاخص اصلی که برپایه فیلد کد تماس می باشد استفاده کرده است. این عمل توسط ‘!’ قبل از خطش نشان داده میشود.

پرس و جوی شاخص نشده:

SELECT * from CALL_LOG where TIME<9

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

استراتژی بهینه سازی:

(file 1)= f:/odbc/LOCAL_CALL_Hist/data/call_log 
 Order_knum= -1 
 Predicate: 1 constraints 
 *:( file 1)(knum= -1, kseg= -1) ID(no bracketing)

توجه کنید که آن یک (SELECTED) یا ‘!’ برای نشان دادن شاخص انتخاب شده ندارد.

يک مثال پیچیده تر:

SELECT CALL_TRN.TECH CALL_TRN.TRN_DATE from CALL_LOG, 
 CALL_TRN where CALL_LoG ID between ‘0000020000’ and ‘0000022000’ 
 CALL_LOG.TIME<9and CALL_LOG.TECH= ‘ABC’ and CALL_LOG.ID= CALL_TRN.ID order by CALL_TRN.TECH

این پرس و جو از نمونه های قبلی بسیار پیچیده تر است هرچند که  اتصالی را بین جداول CALL_LOG و CALL_TRNایجاد می کند، دو بار به فیلد CALL_LOG.ID برمی گردد و دستوری را اجرا می نماید. در اینجا گلچین فایل ثبت وقایع وجود دارد:

استراتژی بهینه سازی:

(file 1)= f:/odbc/LOCAL_CALL_Hist/dat/call_log 
 Order_knum= -1 
 (SELECTED) Predicate: 1 constraints 
 *(file 1)(knum= -1, kseg= -1)ID(bracket head) 
 Predicate= 1 constraints 
 file 1)(knum= 0, kseg= 1)ID(bracket tail)a* 
 Predicate: 1 constraints 
 file 1)(knum= -1, kseg= -1)ID(no bracketing)a* 
 Predicate: 1 constraints 
 file 1)(knum= 0, kseg= 1)ID(partial key: knum=0)a* 
 (file 2)= f:/odbc/LOCAL_CALL_Hist/data/call_log 
 Order_knum=-11 
 (SELECTED) Predicate: 2constraints 
 *: (file 1)(knum=0, kseg= 1)ID(primary key) 
 *: (file 2)(knum= 0, kseg= 1)ID(partial key: knum=0)

فایل ثبت وقایع نشان می دهد که برنامه راه انداز ODBC بیشتر با آن پرس و جو کار می کرد برای اینکه بهترین فیلد را برای ادامه بهینه سازی معین کند. آن تقریبا هر فیلدی را که در پرس و جو ذکر شده بررسی کرده است. دو مدخل اولی برای اولین فایل به فیلد کد (شناسه) مربوط می باشد. برای این فیلد دو مدخل وجود دارد، کروشه بازو کروشه بسته. زیرا این فیلد دو مرتبه برای مقایسه استفاده گردیده است. علاوه بر این، توجه کنید به اینکه تمام مدخلها پس از بخش knum و kseg شان برای نشان دادن کلید اولیه، آن شاخص اصلی را لیست می کنند. سومین مدخل نمی توانست استفاده شود چون همانطور که در بالا ذکر گردید، شاخصی مربوط به آن فیلد وجود ندارد. چهارمین مدخل بیان می کند که می توانست انجام یک بررسی کلیدی مختصری را برپایه کلید اولیه انتخاب نماید. درعوض فایل دوم دو مدخل را لیست می نماید. اولی کلید اولیه است برای اولین جدول. دومین مدخل،(که انتخاب شد)نشان می دهد که قادر بوده یک بررسی کلیدی مختصر را بدون کلید اولیه انجام دهد. توجه کنید که این یک کمی با جدول اولی تفاوت دارد. برای جدول اول، آن مستقیما بدون شناسه (ID) کلیدی شده است، اما می گوید که آن تنها می تواند یک بررسی کلیدی مختصری را بدون شناسه (ID) برای جدول دوم انجام دهد. دلیل این کار این است که کلید اولیه بدون فیلدهای گوناگونی برای جدول دوم ایجاد میشود. درنتیجه، چون که شاخص اصلی (CALL_TRN) شامل فیلد شناسه ای (ID) بود که با دو فیلد دیگر همراه بود، برنامه راه انداز ODBC نمی توانست مستقیما بدون کلید اولیه کلید دار نماید. در عوض، آن می توانست یک بررسی کلیدی(مهم) مختصری را انجام دهد زیرا آن پرس و جو به بخش اول آن کلید اولیه برمی گشت.

بهینه سازی در پرس و جو ها يكي از مسائل اساسی در پایگاه داده توزیع شده مي‏باشد . خصوصاً زمانی كه فایلها در گروه‏هاي مختلف شبکه تکرار و ذخیره شده باشد . يك الگوريتم بهینه ساز پرس و جو در محیط پایگاه داده توزیع شده با پارامترهای مختلفی روبروست . در زمان پردازش يك پرس و جو توزیع شده كپي فایل مورد نظر انتخاب مي‏گردد . با توجه به اینکه فایل در سایتها ی متعدد تکرار شده است ،‌ انتخابهای مختلفی به وجود مي‏ آيد . پارامتر بعدي مشخص كردن شبه پيوندهاي سودمند است كه هزينه انتقال داده براي انجام پيوندها را كاهش مي‏دهند . پارامتر ديگر ترتيب پردازش عمليات مختلف است كه چگونگي انجام اين امر در هزينه پردازش پرس و جو مؤثر مي‏باشد.

در نهايت با توجه به اينكه پرس و جو مي‏تواند در سايت‏هاي مختلف پردازش گردد و نتیجه حاصل به سايت صادر كننده فرستاده مي‏شود ،‌مسئله انتخاب سايت‏هاي مناسب براي اجراي پرس و جو پيش مي‏ آيد.

در پايگاه داده توزيع شده ، مدلها و الگوريتم‏هاي متفاوتي براي بهينه سازي پرس و جو وجود دارد . در اين تحقيق يك مدل بهينه پرس وجو در محيط پايگاه داده توزيع شده ارائه شده است . در اين مدل پارامترهاي ارتباط بين پايگاه داده‏ ها و پردازش محلي در نظر گرفته شده و سپس به كمك يك الگوريتم ژنتيك بهترين استراتژي براي اجراي يك پرس و جو انتخاب مي‏گردد . مزيت اين روش نسبت به حالت‏هاي قبلي در اجراي الگوريتم ژنتيك مي‏باشد كه به جاي جستجو در كل فضاي نمونه ، فضاي محدودي از آن بررسي مي‏شود . در اين الگوريتم چهار پارامتر مؤثر در بهينه سازي پرس و جوي توزيع شده كه عبارتند از انتخاب كپي فايل‏ها ، شبه پيوندهاي سودمند ، ترتيب پيوندها و سايت‏هاي پيوند در نظر گرفته شده است.

نا دیده گرفتن بهینه سازو مجبور کردن sql server به استفاده از ایندکس به خصوص

بهینه ساز پرس وجو ، ابزار بسیار مناسبی در تشخیص کار آمد ترین روش دستیابی برای یک پرس وجو محسوب می شود با این وجود، در برخی موارد نادر،ممکن است بخواهید یا مجبور کردن sql server به استفاده از یک ایندکس بخصوص،پرس وجو را به بهترین نحو تنظیم کنید .

نکته

مزیت بهینه ساز در این است که به طرز بسیار مناسبی ، بهترین روش دستیابی را بر اساس محتویات فعلی جدول تعیین می کند حتی اگر داده های جدول با زمان تغییر کنند ، sql server بهترین ایندکس را انتخاب می کند .به همین دلیل معمولاًبهتر است دست sql server را در انتخاب بهترین روش دستیابی باز گذارید .

این عمل باذکر نام ایندکس مورد نظر در شرط from از یک عبارتsql server و طبق قاعده زیر انجام می شود .

INDEX ={index –name|index id }) )

عموماً به جای شناسه ،از نام ایندکس استفاده می شود ،زیرا یافتن نام ایندکس ،ساده تر از یافتن شناسه آن در جدول است .با این وجود ،دو مقدار ویژه برای index –id وجود دارد:مقدار صفر باعث انتخاب روش پیمایش جدول و مقدار یک باعث استفاده از ایندکس کلاستر شده (در صورت وجود )می شود .

حال اجازه دهید تا مثال را در همین زمینه واینکه چرا باید دست sql server را در انتخاب روش دستیابی باز گذاشت را با هم بررسی کنیم .پرس وجوی زیر را در نظر بگیرید.

SET STATISTICS IO ON

SELECT *FROM title (INDEX =titleind)

WHERE title LIKE,%COMPUTER%,

این پرس وجو ، کلیه عناوین مثال کلمه "COMPUTER" را از جدول title بازیابی می کند .در این پرس وجو sql server به استفاده از ایندکس بر روی ستون title مجبور شده است .دستورIO ON SET STATISTICS باعث می شود تا SQL SERVER میزان عملیات ورودی/خروجی انجام شده همین اجرای پرس وجو را به شما نشان می دهد برای این پرس وجوی بخصوص ،نتایج شبیه به این خواهند بود :

Table :titlescan count 1,logical reads:19;

Phisical reads:0,reads ahead reads:0

Sql server تعداد 19 عملیات ورودی/خروجی را برای بازیابی پنج سطر از جدول صورت داده است .

نکته 

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

اکنون همان پرس و جو را بدون مجبور کردن sql server به استفاده از ایندکس می نویسیم :

SET STATICS IO ON

SELECT*FROM titles

WHERE title LIKE,%COMPUTER%,

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

Table :title scan count 1,logical reads:29

Phisical reads:o,reads ahead reada :0

بدون تکمیل ایندکس ،sql server از روش پیمایش جدول بهره می برد که در اینجا به دلیل کوچک بودن جدول ،کارامدتر است.