بهبود بازدهي با بهينه کردن کد SQL

[alireza sh]

با سلام به دوستان گرامي

مطالبي که از نظر ميگذرانيد بخشي است از تحقيق اينجانب در درس پايگاه داده که اميدوارم مورد توجه واقع گردد.

بهبود بازدهي با بهينه کردن کد SQL

با توجه به تجربيات ما ، 80 % بازدهي در SQL Server از طريق بهبود کد SQL حاصل ميشود.
Kevin kline

تجربيات نشان ميدهد که 80 تا 90 درصد بهبود بازدهي در سطح برنامه است نه در سطح پايگاه داده.
Thomas Kyte

بدون در نظر گرفتن اينکه از چه DBMS اي استفاده ميکنيد ، شما ميتوانيد بازدهي را با بهبود کد SQL نوشته شده ارتقا دهيد. اين مقاله سعي بر آن دارد تا راههاي عمده بهبود کد SQL را مستقل از نوع DBMS آموزش دهد.

بهبود باردهي از طريق بهبود syntax
يک syntax مجموعه اي است از عبارتها و نحوه قرارگيري آنها در يک عبارت بزرگتر.
معمولا نمي توان صرفا Syntax را زياد تغيير داد چراکه زبان SQL تعداد محدودي دستور دارد که در آنها گزينه هاي اختياري زيادي وجود دارد.اما مهمترين Syntax اي که قابليت بهينه سازي زيادي دارد عبارتهاي شرطي در جستجوهاست.
در يک عبارت ميتوانيم بگوييم بهترين جستجوهاي شرطي آنهايي هستند که روي تعداد کمي از سطرها اعمال ميشوند.

امتيازدهي به عبارت SQL


جدول زير اين شروط را از بهترين به بدترين مرتب ميکند.
علامت ----- جداساز ستونها و علامت |<>| جداساز عملگر و امتياز آن است (خدا بگم چيکارتون کنه با اين اديتورتون




____________________________________________
امتياز |<>| عملگر -------------- امتياز | عملگر
____________________________________________
10 |<>| = -------------- 10 |<>| کاراکتر تنها
____________________________________________
5 |<>| > -------------- 5 |<>| ستون تنها
____________________________________________
5 |<>| > = -------------- 5 |<>| پارامتر تنها
____________________________________________
5 |<>| < -------------- 3 |<>| عبارت ترکيبي
____________________________________________
5 |<>| < = -------------- 2 |<>| نوع داده اي عددي
____________________________________________
3 |<>| LIKE ک -------------- 1 |<>| انواع داده اي ديگر
____________________________________________
0 |<>| <> -------------- 0 |<>| نوع داده اي کاراکتر

____________________________________________
---------------------------- 0 |<>| NULL

____________________________________________


به اين مثال دقت کنيد :

…. Where smallint_column = 1234

اين شرط جستجو 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 هاي معروف به طور خودکار اين عمل را انجام ميدهند.

 

[alireza sh]

اصل تعدي
بر اساس اصل تعدي داريم :

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

 

[alireza sh]

تبديل ثوابت به مقدار عددي آنها

بعضا در بسياري از موارد با ثوابت زيادي در جستجوها روبرو ميشويم. اين ثوابت هرچند باعث خوانايي کد و سادگي برنامه نويسي ميشنود اما تاثير منفي بر بازدهي دارند.مثلا کد زير را در نظر بگيريد :
.
.
.
.

عدم حذف کدهاي به ظاهر غير مؤثر
در برخي مواقع به کدهايي برميخوريم که بديهي به نظر ميرسند و به ظاهر زائد ميباشند. مثلا قطعه کدهاي زير را ببينيد :

... 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 !!!
چرا که در هنگام تبديل حروف کوچک به بزرگ علايم نحوي آن حذف گرديده اند و معناي لغت بکلي دگرگون شده است.

 

[mazoolagh]

قبل از هرچیز اجازه بدین از شما بخاطر این مطالب مفید تشکر کنم.

این قسمت رو متوجه نشدم تبديل ثوابت به مقدار عددي آنها ظاهرا کدها جا افتاده!

 

[alireza sh]

قبل از هرچیز اجازه بدین از شما بخاطر این مطالب مفید تشکر کنم.

این قسمت رو متوجه نشدم تبديل ثوابت به مقدار عددي آنها ظاهرا کدها جا افتاده!

متاسفانه هنوز اون قسمت رو نرسيدم کامل کنم

لطفا نظر خودتون رو در مورد محتواها بگين ممنون

 

[Mehdi]

قشنگ بود
منتظر بقيش هستيم

 

[alireza sh]

به زودی این بخش را ادامه میدهم !!! پس از یک سال و اندی !!!

 

[شايان]

جناب شیرازی ممنون بابت تاپیک و وقتی که گذاشتید.

 

[Graveworm]

به زودی این بخش را ادامه میدهم !!! پس از یک سال و اندی !!!

من الان این مطلب رو خوندم، جالب بود
دستت درد نکنه، با اجازه منم اگه به نکته ای برخوردم، مینویسم

 

[alireza sh]

جناب شیرازی ممنون بابت تاپیک و وقتی که گذاشتید.

خواهش میکنم شایان جان

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

از grave moon هم تشکر میکنم

 

[alireza sh]

بنده ادامه مطلب را در سایت خودم قرار دادم

آدرس : http://shirazionline.net/system/content/category/4/13/31/

متاسفانه چون ادیتور فوروم جدول را پشتیبانی نمی کند ، بنده نمیتوانم کل مطلب را اینجا قرار دهم.

دوستان میتوانند سوالات را در همین تاپیک بپرسند

 

[CZed]

من فايده همچين كدي رو متوجه نميشم:

... WHERE column1 IN (1, 3, 3)

ميشه توضيح بدي كه كجا ممكنه همچين كدي كار مفيدي انجام بده؟

 

[parial1]

قشنگ بود چرا ادامه ندادید!!!!!!!!!!!!!!!!

 

[baxiha]

لازم بود تاپیک یه قرن پیش رو بیاری بالا آخه!

 

[shamabadi]

بازم تشکر میکنم