alireza sh
مدیر بازنشسته
با سلام به دوستان گرامي
مطالبي که از نظر ميگذرانيد بخشي است از تحقيق اينجانب در درس پايگاه داده که اميدوارم مورد توجه واقع گردد.
بهبود بازدهي با بهينه کردن کد SQL
با توجه به تجربيات ما ، 80 % بازدهي در SQL Server از طريق بهبود کد SQL حاصل ميشود.
Kevin kline
تجربيات نشان ميدهد که 80 تا 90 درصد بهبود بازدهي در سطح برنامه است نه در سطح پايگاه داده.
Thomas Kyte
بدون در نظر گرفتن اينکه از چه DBMS اي استفاده ميکنيد ، شما ميتوانيد بازدهي را با بهبود کد SQL نوشته شده ارتقا دهيد. اين مقاله سعي بر آن دارد تا راههاي عمده بهبود کد SQL را مستقل از نوع DBMS آموزش دهد.
بهبود باردهي از طريق بهبود syntax
يک syntax مجموعه اي است از عبارتها و نحوه قرارگيري آنها در يک عبارت بزرگتر.
معمولا نمي توان صرفا Syntax را زياد تغيير داد چراکه زبان SQL تعداد محدودي دستور دارد که در آنها گزينه هاي اختياري زيادي وجود دارد.اما مهمترين Syntax اي که قابليت بهينه سازي زيادي دارد عبارتهاي شرطي در جستجوهاست.
در يک عبارت ميتوانيم بگوييم بهترين جستجوهاي شرطي آنهايي هستند که روي تعداد کمي از سطرها اعمال ميشوند.
امتيازدهي به عبارت SQL
جدول زير اين شروط را از بهترين به بدترين مرتب ميکند.
علامت ----- جداساز ستونها و علامت |<>| جداساز عملگر و امتياز آن است (خدا بگم چيکارتون کنه با اين اديتورتون !!! :ungry
____________________________________________
امتياز |<>| عملگر -------------- امتياز | عملگر
____________________________________________
10 |<>| = -------------- 10 |<>| کاراکتر تنها
____________________________________________
5 |<>| > -------------- 5 |<>| ستون تنها
____________________________________________
5 |<>| > = -------------- 5 |<>| پارامتر تنها
____________________________________________
5 |<>| < -------------- 3 |<>| عبارت ترکيبي
____________________________________________
5 |<>| < = -------------- 2 |<>| نوع داده اي عددي
____________________________________________
3 |<>| LIKE ک -------------- 1 |<>| انواع داده اي ديگر
____________________________________________
0 |<>| <> -------------- 0 |<>| نوع داده اي کاراکتر
____________________________________________
---------------------------- 0 |<>| NULL
____________________________________________
به اين مثال دقت کنيد :
…. Where smallint_column = 12345
اين شرط جستجو 27 امتياز خواهد داشت :
• 5 امتياز براي ستون smallint_column که بتنهايي در سمت چپ قرار دارد
• 2 امتياز براي نوع داده اي ستون smallint_column که عددي است
• 10 امتياز براي عملگر مساوي
• 10 امتياز براي عبارت عددي 12345 که به تنهايي در سمت راست قرار دارد
حال به اين مثال دقت کنيد :
… WHERE char_column > = varchar_column | | 'x'
اين مثال تنها 13 امتياز خواهد داشت :
• 5 امتياز براي ستون char_column که بتنهايي در سمت چپ قرار دارد
• 5 امتياز براي عملگر > =
• 3 امتياز براي عبارت ترکيبي varchar_column | | 'x'
اما چنانچه آن را به صورت زير در آوريم :
GET var AS varchar_column | | 'x'.
…. WHERE char_column > = var
آنگاه 15 امتياز خواهد گرفت که کاملا منطقي است. چرا که عمل or بين varchar_column و 'x' فقط يکبار انجام ميشود.
قرار دادن ستونها در ترتيب صحيح خود
يکي از راههاي بهبود بازدهي در زماني که در يک عبارت جستجو ميخواهيم چند ستون را شرکت دهيم ، رعايت ترتيب ستونهاست مثل :
SELECT * FROM Table1
WHERE column1 = 5
AND column2 = 77.3
AND column3 = 'Smith'
AND column4 < 117
AND column4 > column5
نکته : اکثر DBMS هاي معروف به طور خودکار اين عمل را انجام ميدهند.
اصل تعدي
بر اساس اصل تعدي داريم :
IF
(A <comparison operator> B) IS TRUE AND (B <comparison operator> C) IS TRUE
THEN
(A <comparison operator> C) IS TRUE AND NOT (A <comparison operator> C) IS FALSE
با استفاده از اين اصل در برخي موارد ميتوان جاي عملوندها را بدون تغيير مفهوم کلي عبارت تغيير دهيم به گونه اي که عبارت شرطي ما امتياز بيشتري بدست آورد مثل :
Expression #1
... WHERE column1 < column2
AND column2 = column3
AND column1 = 5
Expression #2
... WHERE 5 < column2
AND column2 = column3
AND column1 = 5
که عبارت دوم بهينه تر از عبارت نخست است.
در مثال زير ، با تغيير عبارت SQL و تبديل آن به حالت دوم ، سرعت اجرا نيم برابر بيشتر مي شود.
Expression #1
SELECT * FROM Table1
WHERE column1 = 5 AND
NOT (column3 = 7 OR column1 = column2)
Expression #2
SELECT * FROM Table1
WHERE column1 = 5
AND column3 <> 7
AND column2 <> 5
نکته : تنها نگراني در استفاده از اصل تعدي ، برخورد با مقادير NULL است که خوشبختانه DMBS هاي مطرح مثل SQL Server هنگامي که طرف راست يک عملگر مثل > = مقدار NULL باشد FALSE بر ميگردانند.
مثالي از راهنماي online پايگاه داده MySQL
... WHERE a < b AND b = c AND a = 5
transforms to:
... WHERE b > 5 AND b = c AND a = 5
تبديل ثوابت به مقدار عددي آنها
بعضا در بسياري از موارد با ثوابت زيادي در جستجوها روبرو ميشويم. اين ثوابت هرچند باعث خوانايي کد و سادگي برنامه نويسي ميشنود اما تاثير منفي بر بازدهي دارند.مثلا کد زير را در نظر بگيريد :
.
.
.
.
عدم حذف کدهاي به ظاهر غير مؤثر
در برخي مواقع به کدهايي برميخوريم که بديهي به نظر ميرسند و به ظاهر زائد ميباشند. مثلا قطعه کدهاي زير را ببينيد :
... WHERE column1 + 0
... WHERE 5 + 0.0
... WHERE column1 IN (1, 3, 3)
... CAST(1 AS INTEGER)
... WHERE 'a' || 'b'
يا مثلا فرض کنيد ستوني به نام col_indx داريم که مقدار آن هميشه بزرگتر از صفر باشد ، شايد جستجويي مثل عبارت زير غير منطقي به نظر برسد :
Select * from myTable
Where col_indx > 0
و بهتر باشد که حذف گردد اما در اغلب اين مواقع چنانچه دليل اصلي وجود چنين عبارتهايي را نمي دانيم بهتر است که آنها را به حال خود رها کنيم و يا چنانچه قصد بهينه سازي آنها را داريم، بسيار محتاط عمل کنيم چرا که در اکثر قريب به اتفاق مواردي که به چنين کدهايي بر ميخوريم ، برنامه نويس ايجاد کننده آن کدها دليل خاصي براي اين کار خود داشته است.
در مثال بالا ممکن است به دليل وجود يک ايندکس روي ستون col_indx ، شرط جستجو به اين شکل در آمده باشد و چنانچه اين کد را حذف کنيم نه تنها بازدهي بالا نخواهد رفت بلکه ممکن است شديدا افت کند.
گاهي اين کدها به دلايل ديگري مثل در نظر نگرفتن ايندکس ها، تغيير نوه دادهاي حاصل از محاسبه، تفاوت قائل شدن بين انواع داده اي مشابه (مثل samllint, integer) و ... ايجاد شده باشند. پس توصيه ما اين است که چنين عباراتي را تا زماني که دلطل واقعي آنها را نمي دانيد تغيير ندهيد.
دقت در جستجوهاي حساس به حروف
از انجايي که يک قاعده کلي در مورد حساسيت به حروف در کليه DBMS ها وجود ندارد – مثلا Microsoft Access رشته "SMITH" را با "Smith" يکسان در نطر ميگيرد اما SQL Server به گونه اي است که در آن ميتوان انتخاب کرد که حساس به حروف باشد يا نه- برخي برنامه نويسان در صدد برمي آيند تا با افزودن کدهايي عدم حساسيت به حروف را مثلا اينگونه نشان دهند :
... WHERE UPPER(column1) = 'SMITH'
اين کار هرچند با ظاهر در موقعي که با کاراکترهاي لاتين کار ميکنيم ممکن است مشکلي ايجاد نکند اما در هنگام کار با ديگر زبان ها به خصوص آلماني و فرانسوي ميتواند مشکل ساز باشد. به مثال زير دقت کنيد :
عبارت 'résumé' در زبان آلماني به معناي curriculum vitae است که اگر تابع زير روي آن اعمال گردد :
... UPPER('résumé')
حاصل RESUME خواهد بود بمعناي begin again !!!
چرا که در هنگام تبديل حروف کوچک به بزرگ علايم نحوي آن حذف گرديده اند و معناي لغت بکلي دگرگون شده است.
پرهيز از اعمال توابع بر روي ستونها
Microsoft و Oracle به شدت توصيه دارند که تا حد ممکن از اعمال توابع بر روي ستونها خودداري نماييد مثلا به جاي عبارت زير :
... WHERE column1 = 'SMITH'
OR column1 = 'Smith'
از اين عبارت استفاده نماييد :
... WHERE column1 = 'SMITH'
OR ('SMITH' <> 'Smith' AND column1 = 'Smith')
که باعث مي شود ارجاع به ستون column1 و بازيابي مقدار آن حداقل شود که همين کار باعث بالا رفتن سرعت پردازش ميگردد.
استفاده بهينه از عملگرهاي منطقي
استفاده صحيح و بهينه از عملگرهاي منطقي در بسياري موارد تاثيري چشمگير بر افزايش بازدهي دارد. در اين قسمت اين عملگرها را با هم بررسي ميکنيم. البته اين نکته را ذکر کنيم که ORACLE برعکس ديگر DBMS ها عبارتهايي که در آنها از عملگرهاي منطقي استفاده شده است را از راست به چپ بررسي ميکند.
OR
براي بهينه سازي اين عملگر بهتر است ساده ترين و محتمل ترين شرط در سمت چپ عبارت قرار گيرد، چه آنکه اگر عبارت مذکور با مقدار true ارزيابي گردد ديگر نيازي به ارزيابي ديگر عبارتهاي سمت راست OR نيست.
مثال :
فرض کنيد در سازمان تازه تاسيسي به دنبال کارمنداني ميگرديم که يا ساعات کاري آنها بيش از 140 ساعت در ماه باشد يا سابقه کاريشان کمتر از 5 سال باشد.از آنجا که شرط اول محتمل تر است بهتر است اين جستجو به اين صورت درآيد :
… WHERE exp < 5 OR Hpm > 100
چون در اين حالت خاص تعداد کارکناني که ساعات کاريشان بيش از 140 ساعت در ماه باشد بسيار کمتر از کارمنداني است که کمتر از 5 سال سابقه دارند
AND
براي بهينه سازي اين عملگر بهتر است غير محتمل ترين شرط در سمت چپ عبارت قرار گيرد، چه آنکه اگر عبارت شرطي مذکور با مقدار false ارزيابي گردد ديگر نيازي به ارزيابي ديگر عبارتهاي سمت راست AND نيست.
در مثال قبلي فرض کنيد بدنبال کارمنداني ميگرديم که بيشتر از 5 سال سابقه دارند و در ماه حداکثر 130 ساعت کار ميکنند، بهترين حالت براي نوشتن آن جستجو اين است :
… WHERE exp > 5 AND Hpm < = 100
چون در اين حالت بسيار کم بر ميخوريم به کارمنداني که بالاي 5 سال سابقه کار دارند.
ترکيبي از AND و OR
به قانون دمورگان دقت کنيد
A AND (B OR C) = (A AND B) OR (A AND C)
در بسياري مواقع اين تبديل سرعت کار را بالا ميبرد.
جدول زير را در نظر بگيريد :
Column2 Column1 ROW#
A 3 1
B 2 2
C 1 3
فرض کنيد شما اين Query را ميخواهيد روي اين جدول اجرا کنيد :
SELECT * FROM Table1
WHERE (column1 = 1 AND column2 = 'A')
OR (column1 = 1 AND column2 = 'B')
اکثرا DBMS ها اگر از index lookup استفاده نمايند عملکردشان چيزي شبيه به اين خواهد بود :
Index lookup: column1=1. Result set = {row 3}
Index lookup: column2='A'. Result set = {row 1}
AND to merge the result sets. Result set = {}
Index lookup: column1=1. Result set = {row 3}
Index lookup: column2='A'. Result set = {row 1}
AND to merge the result sets. Result set = {}
OR to merge the result sets. Result set = {}
حال اگر اين جستجو را به اين صورت درآوريم
SELECT * FROM Table1
WHERE column1 = 1
AND (column2 = 'A' OR column2 = 'B')
عملکرد index lookup به اين شکل درمي آيد :
Index lookup: column2='A'. Result set = {row 1}
Index lookup: column2='B'. Result set = {row 2}
OR to merge the result sets. Result set = {row 1, 2}
Index lookup: column1=1. Result set = {row 3}
AND to merge the result sets. Result set = {}
که همانگونه که ميبينيد عمليات پردازش کمتر شده و سرعت کار بالاتر خواهد رفت.البته تعدادي از DBMS ها هستند که به طور خودکار اين عمل را انجام ميدهند.
چند نکته
در کامپيوترهاي 32 بيتي سريعترين حالت براي محاسبات رياضي زماني است که عملگرها integer باشند تا زماني که samllint و float و ... باشند پس بهتر است تا حد امکان از integer ها در عمليات رياضي استفاده کنيم.
NOT
حتي المقدور از عملگر NOT پرهيز کنيد !!! مثلا عبارتهاي
... WHERE NOT (column1 > 5)
... WHERE NOT (column1 > 5 OR column2 = 7)
را به
... WHERE column1 <= 5
... WHERE column1 <= 5 AND column2 <> 7
تبديل نماييد. از آنجايي که برخي DBMS ها از ايندکس ها براي عملگر نامساوي استفاده نمي کنند بهتر است عبارتي مثل :
... WHERE NOT (bloodtype = 'O')
به اين عبارت تبديل شود :
... WHERE bloodtype < 'O'
OR bloodtype > 'O'
نکته : در مثال قبل از آنجايي اين تبديل را انجام داديم که به عنوان مثال ميدانستيم در کشورهاي آمريکاي شمالي گروه خوني اکثر مردم O است.
IN
بسياري فکر ميکنند که بين دو عبارت زير تفاوتي وجود ندارد :
... WHERE column1 = 5 OR column1 = 6
... WHERE column1 IN (5, 6)
با توجه به تجربيات ما در برخي از DBMS هاي بزرگ عملگر IN اندکي سريعتر از عملگر OR ميباشد.البته در مواردي که در يک محدوده متوالي جستجو ميکنيم بهتر است آنچه را که در اين محدوده نيست بيابيم مثلا به جاي استفاده از :
WHERE column1 IN (1, 3, 4, 5)
اينگونه بنويسيم :
WHERE column1 BETWEEN 1 AND 5
AND column1 <> 2
که بازدهي را به طور چشمگيري بالا ميبرد.
مطالبي که از نظر ميگذرانيد بخشي است از تحقيق اينجانب در درس پايگاه داده که اميدوارم مورد توجه واقع گردد.
بهبود بازدهي با بهينه کردن کد SQL
با توجه به تجربيات ما ، 80 % بازدهي در SQL Server از طريق بهبود کد SQL حاصل ميشود.
Kevin kline
تجربيات نشان ميدهد که 80 تا 90 درصد بهبود بازدهي در سطح برنامه است نه در سطح پايگاه داده.
Thomas Kyte
بدون در نظر گرفتن اينکه از چه DBMS اي استفاده ميکنيد ، شما ميتوانيد بازدهي را با بهبود کد SQL نوشته شده ارتقا دهيد. اين مقاله سعي بر آن دارد تا راههاي عمده بهبود کد SQL را مستقل از نوع DBMS آموزش دهد.
بهبود باردهي از طريق بهبود syntax
يک syntax مجموعه اي است از عبارتها و نحوه قرارگيري آنها در يک عبارت بزرگتر.
معمولا نمي توان صرفا Syntax را زياد تغيير داد چراکه زبان SQL تعداد محدودي دستور دارد که در آنها گزينه هاي اختياري زيادي وجود دارد.اما مهمترين Syntax اي که قابليت بهينه سازي زيادي دارد عبارتهاي شرطي در جستجوهاست.
در يک عبارت ميتوانيم بگوييم بهترين جستجوهاي شرطي آنهايي هستند که روي تعداد کمي از سطرها اعمال ميشوند.
امتيازدهي به عبارت SQL
جدول زير اين شروط را از بهترين به بدترين مرتب ميکند.
علامت ----- جداساز ستونها و علامت |<>| جداساز عملگر و امتياز آن است (خدا بگم چيکارتون کنه با اين اديتورتون !!! :ungry
____________________________________________
امتياز |<>| عملگر -------------- امتياز | عملگر
____________________________________________
10 |<>| = -------------- 10 |<>| کاراکتر تنها
____________________________________________
5 |<>| > -------------- 5 |<>| ستون تنها
____________________________________________
5 |<>| > = -------------- 5 |<>| پارامتر تنها
____________________________________________
5 |<>| < -------------- 3 |<>| عبارت ترکيبي
____________________________________________
5 |<>| < = -------------- 2 |<>| نوع داده اي عددي
____________________________________________
3 |<>| LIKE ک -------------- 1 |<>| انواع داده اي ديگر
____________________________________________
0 |<>| <> -------------- 0 |<>| نوع داده اي کاراکتر
____________________________________________
---------------------------- 0 |<>| NULL
____________________________________________
به اين مثال دقت کنيد :
…. Where smallint_column = 12345
اين شرط جستجو 27 امتياز خواهد داشت :
• 5 امتياز براي ستون smallint_column که بتنهايي در سمت چپ قرار دارد
• 2 امتياز براي نوع داده اي ستون smallint_column که عددي است
• 10 امتياز براي عملگر مساوي
• 10 امتياز براي عبارت عددي 12345 که به تنهايي در سمت راست قرار دارد
حال به اين مثال دقت کنيد :
… WHERE char_column > = varchar_column | | 'x'
اين مثال تنها 13 امتياز خواهد داشت :
• 5 امتياز براي ستون char_column که بتنهايي در سمت چپ قرار دارد
• 5 امتياز براي عملگر > =
• 3 امتياز براي عبارت ترکيبي varchar_column | | 'x'
اما چنانچه آن را به صورت زير در آوريم :
GET var AS varchar_column | | 'x'.
…. WHERE char_column > = var
آنگاه 15 امتياز خواهد گرفت که کاملا منطقي است. چرا که عمل or بين varchar_column و 'x' فقط يکبار انجام ميشود.
قرار دادن ستونها در ترتيب صحيح خود
يکي از راههاي بهبود بازدهي در زماني که در يک عبارت جستجو ميخواهيم چند ستون را شرکت دهيم ، رعايت ترتيب ستونهاست مثل :
SELECT * FROM Table1
WHERE column1 = 5
AND column2 = 77.3
AND column3 = 'Smith'
AND column4 < 117
AND column4 > column5
نکته : اکثر DBMS هاي معروف به طور خودکار اين عمل را انجام ميدهند.
اصل تعدي
بر اساس اصل تعدي داريم :
IF
(A <comparison operator> B) IS TRUE AND (B <comparison operator> C) IS TRUE
THEN
(A <comparison operator> C) IS TRUE AND NOT (A <comparison operator> C) IS FALSE
با استفاده از اين اصل در برخي موارد ميتوان جاي عملوندها را بدون تغيير مفهوم کلي عبارت تغيير دهيم به گونه اي که عبارت شرطي ما امتياز بيشتري بدست آورد مثل :
Expression #1
... WHERE column1 < column2
AND column2 = column3
AND column1 = 5
Expression #2
... WHERE 5 < column2
AND column2 = column3
AND column1 = 5
که عبارت دوم بهينه تر از عبارت نخست است.
در مثال زير ، با تغيير عبارت SQL و تبديل آن به حالت دوم ، سرعت اجرا نيم برابر بيشتر مي شود.
Expression #1
SELECT * FROM Table1
WHERE column1 = 5 AND
NOT (column3 = 7 OR column1 = column2)
Expression #2
SELECT * FROM Table1
WHERE column1 = 5
AND column3 <> 7
AND column2 <> 5
نکته : تنها نگراني در استفاده از اصل تعدي ، برخورد با مقادير NULL است که خوشبختانه DMBS هاي مطرح مثل SQL Server هنگامي که طرف راست يک عملگر مثل > = مقدار NULL باشد FALSE بر ميگردانند.
مثالي از راهنماي online پايگاه داده MySQL
... WHERE a < b AND b = c AND a = 5
transforms to:
... WHERE b > 5 AND b = c AND a = 5
تبديل ثوابت به مقدار عددي آنها
بعضا در بسياري از موارد با ثوابت زيادي در جستجوها روبرو ميشويم. اين ثوابت هرچند باعث خوانايي کد و سادگي برنامه نويسي ميشنود اما تاثير منفي بر بازدهي دارند.مثلا کد زير را در نظر بگيريد :
.
.
.
.
عدم حذف کدهاي به ظاهر غير مؤثر
در برخي مواقع به کدهايي برميخوريم که بديهي به نظر ميرسند و به ظاهر زائد ميباشند. مثلا قطعه کدهاي زير را ببينيد :
... WHERE column1 + 0
... WHERE 5 + 0.0
... WHERE column1 IN (1, 3, 3)
... CAST(1 AS INTEGER)
... WHERE 'a' || 'b'
يا مثلا فرض کنيد ستوني به نام col_indx داريم که مقدار آن هميشه بزرگتر از صفر باشد ، شايد جستجويي مثل عبارت زير غير منطقي به نظر برسد :
Select * from myTable
Where col_indx > 0
و بهتر باشد که حذف گردد اما در اغلب اين مواقع چنانچه دليل اصلي وجود چنين عبارتهايي را نمي دانيم بهتر است که آنها را به حال خود رها کنيم و يا چنانچه قصد بهينه سازي آنها را داريم، بسيار محتاط عمل کنيم چرا که در اکثر قريب به اتفاق مواردي که به چنين کدهايي بر ميخوريم ، برنامه نويس ايجاد کننده آن کدها دليل خاصي براي اين کار خود داشته است.
در مثال بالا ممکن است به دليل وجود يک ايندکس روي ستون col_indx ، شرط جستجو به اين شکل در آمده باشد و چنانچه اين کد را حذف کنيم نه تنها بازدهي بالا نخواهد رفت بلکه ممکن است شديدا افت کند.
گاهي اين کدها به دلايل ديگري مثل در نظر نگرفتن ايندکس ها، تغيير نوه دادهاي حاصل از محاسبه، تفاوت قائل شدن بين انواع داده اي مشابه (مثل samllint, integer) و ... ايجاد شده باشند. پس توصيه ما اين است که چنين عباراتي را تا زماني که دلطل واقعي آنها را نمي دانيد تغيير ندهيد.
دقت در جستجوهاي حساس به حروف
از انجايي که يک قاعده کلي در مورد حساسيت به حروف در کليه DBMS ها وجود ندارد – مثلا Microsoft Access رشته "SMITH" را با "Smith" يکسان در نطر ميگيرد اما SQL Server به گونه اي است که در آن ميتوان انتخاب کرد که حساس به حروف باشد يا نه- برخي برنامه نويسان در صدد برمي آيند تا با افزودن کدهايي عدم حساسيت به حروف را مثلا اينگونه نشان دهند :
... WHERE UPPER(column1) = 'SMITH'
اين کار هرچند با ظاهر در موقعي که با کاراکترهاي لاتين کار ميکنيم ممکن است مشکلي ايجاد نکند اما در هنگام کار با ديگر زبان ها به خصوص آلماني و فرانسوي ميتواند مشکل ساز باشد. به مثال زير دقت کنيد :
عبارت 'résumé' در زبان آلماني به معناي curriculum vitae است که اگر تابع زير روي آن اعمال گردد :
... UPPER('résumé')
حاصل RESUME خواهد بود بمعناي begin again !!!
چرا که در هنگام تبديل حروف کوچک به بزرگ علايم نحوي آن حذف گرديده اند و معناي لغت بکلي دگرگون شده است.
پرهيز از اعمال توابع بر روي ستونها
Microsoft و Oracle به شدت توصيه دارند که تا حد ممکن از اعمال توابع بر روي ستونها خودداري نماييد مثلا به جاي عبارت زير :
... WHERE column1 = 'SMITH'
OR column1 = 'Smith'
از اين عبارت استفاده نماييد :
... WHERE column1 = 'SMITH'
OR ('SMITH' <> 'Smith' AND column1 = 'Smith')
که باعث مي شود ارجاع به ستون column1 و بازيابي مقدار آن حداقل شود که همين کار باعث بالا رفتن سرعت پردازش ميگردد.
استفاده بهينه از عملگرهاي منطقي
استفاده صحيح و بهينه از عملگرهاي منطقي در بسياري موارد تاثيري چشمگير بر افزايش بازدهي دارد. در اين قسمت اين عملگرها را با هم بررسي ميکنيم. البته اين نکته را ذکر کنيم که ORACLE برعکس ديگر DBMS ها عبارتهايي که در آنها از عملگرهاي منطقي استفاده شده است را از راست به چپ بررسي ميکند.
OR
براي بهينه سازي اين عملگر بهتر است ساده ترين و محتمل ترين شرط در سمت چپ عبارت قرار گيرد، چه آنکه اگر عبارت مذکور با مقدار true ارزيابي گردد ديگر نيازي به ارزيابي ديگر عبارتهاي سمت راست OR نيست.
مثال :
فرض کنيد در سازمان تازه تاسيسي به دنبال کارمنداني ميگرديم که يا ساعات کاري آنها بيش از 140 ساعت در ماه باشد يا سابقه کاريشان کمتر از 5 سال باشد.از آنجا که شرط اول محتمل تر است بهتر است اين جستجو به اين صورت درآيد :
… WHERE exp < 5 OR Hpm > 100
چون در اين حالت خاص تعداد کارکناني که ساعات کاريشان بيش از 140 ساعت در ماه باشد بسيار کمتر از کارمنداني است که کمتر از 5 سال سابقه دارند
AND
براي بهينه سازي اين عملگر بهتر است غير محتمل ترين شرط در سمت چپ عبارت قرار گيرد، چه آنکه اگر عبارت شرطي مذکور با مقدار false ارزيابي گردد ديگر نيازي به ارزيابي ديگر عبارتهاي سمت راست AND نيست.
در مثال قبلي فرض کنيد بدنبال کارمنداني ميگرديم که بيشتر از 5 سال سابقه دارند و در ماه حداکثر 130 ساعت کار ميکنند، بهترين حالت براي نوشتن آن جستجو اين است :
… WHERE exp > 5 AND Hpm < = 100
چون در اين حالت بسيار کم بر ميخوريم به کارمنداني که بالاي 5 سال سابقه کار دارند.
ترکيبي از AND و OR
به قانون دمورگان دقت کنيد
A AND (B OR C) = (A AND B) OR (A AND C)
در بسياري مواقع اين تبديل سرعت کار را بالا ميبرد.
جدول زير را در نظر بگيريد :
Column2 Column1 ROW#
A 3 1
B 2 2
C 1 3
فرض کنيد شما اين Query را ميخواهيد روي اين جدول اجرا کنيد :
SELECT * FROM Table1
WHERE (column1 = 1 AND column2 = 'A')
OR (column1 = 1 AND column2 = 'B')
اکثرا DBMS ها اگر از index lookup استفاده نمايند عملکردشان چيزي شبيه به اين خواهد بود :
Index lookup: column1=1. Result set = {row 3}
Index lookup: column2='A'. Result set = {row 1}
AND to merge the result sets. Result set = {}
Index lookup: column1=1. Result set = {row 3}
Index lookup: column2='A'. Result set = {row 1}
AND to merge the result sets. Result set = {}
OR to merge the result sets. Result set = {}
حال اگر اين جستجو را به اين صورت درآوريم
SELECT * FROM Table1
WHERE column1 = 1
AND (column2 = 'A' OR column2 = 'B')
عملکرد index lookup به اين شکل درمي آيد :
Index lookup: column2='A'. Result set = {row 1}
Index lookup: column2='B'. Result set = {row 2}
OR to merge the result sets. Result set = {row 1, 2}
Index lookup: column1=1. Result set = {row 3}
AND to merge the result sets. Result set = {}
که همانگونه که ميبينيد عمليات پردازش کمتر شده و سرعت کار بالاتر خواهد رفت.البته تعدادي از DBMS ها هستند که به طور خودکار اين عمل را انجام ميدهند.
چند نکته
در کامپيوترهاي 32 بيتي سريعترين حالت براي محاسبات رياضي زماني است که عملگرها integer باشند تا زماني که samllint و float و ... باشند پس بهتر است تا حد امکان از integer ها در عمليات رياضي استفاده کنيم.
NOT
حتي المقدور از عملگر NOT پرهيز کنيد !!! مثلا عبارتهاي
... WHERE NOT (column1 > 5)
... WHERE NOT (column1 > 5 OR column2 = 7)
را به
... WHERE column1 <= 5
... WHERE column1 <= 5 AND column2 <> 7
تبديل نماييد. از آنجايي که برخي DBMS ها از ايندکس ها براي عملگر نامساوي استفاده نمي کنند بهتر است عبارتي مثل :
... WHERE NOT (bloodtype = 'O')
به اين عبارت تبديل شود :
... WHERE bloodtype < 'O'
OR bloodtype > 'O'
نکته : در مثال قبل از آنجايي اين تبديل را انجام داديم که به عنوان مثال ميدانستيم در کشورهاي آمريکاي شمالي گروه خوني اکثر مردم O است.
IN
بسياري فکر ميکنند که بين دو عبارت زير تفاوتي وجود ندارد :
... WHERE column1 = 5 OR column1 = 6
... WHERE column1 IN (5, 6)
با توجه به تجربيات ما در برخي از DBMS هاي بزرگ عملگر IN اندکي سريعتر از عملگر OR ميباشد.البته در مواردي که در يک محدوده متوالي جستجو ميکنيم بهتر است آنچه را که در اين محدوده نيست بيابيم مثلا به جاي استفاده از :
WHERE column1 IN (1, 3, 4, 5)
اينگونه بنويسيم :
WHERE column1 BETWEEN 1 AND 5
AND column1 <> 2
که بازدهي را به طور چشمگيري بالا ميبرد.
