بهبود بازدهي با بهينه کردن کد SQL – بخش اول

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

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

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

بهبود بازدهي از طريق بهبود syntax

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

در يک عبارت ميتوانيم بگوييم بهترين جستجوهاي شرطي آنهايي هستند که روي تعداد کمي از سطرها اعمال ميشوند.

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

جدول زير اين شروط را از بهترين به بدترين مرتب ميکند

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

اين شرط جستجو 27 امتياز خواهد داشت :

• 5 امتياز براي ستون smallint_column که بتنهايي در سمت چپ قرار دارد

• 2 امتياز براي نوع داده اي ستون smallint_column که عددي است

• 10 امتياز براي عملگر مساوي

• 10 امتياز براي عبارت عددي 12345 که به تنهايي در سمت راست قرار دارد

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

اين مثال تنها 13 امتياز خواهد داشت :

• 5 امتياز براي ستون char_column که بتنهايي در سمت چپ قرار دارد

• 5 امتياز براي عملگر > =

• 3 امتياز براي عبارت ترکيبي varchar_column | | 'x'

اما چنانچه آن را به صورت زير در آوريم :

آنگاه 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

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

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

Query #1:
SELECT * FROM Table1
WHERE date_column = CURRENT_DATE
AND amount * 5 > 100.00
Query #2:
SELECT * FROM Table1
WHERE date_column = DATE '2002-01-01'
AND amount * 5 > 100.00

کد جستجوی دوم سریعتر از کد اول اجرا خواهد گردید اما ذکر این نکته ضروری است که در جهان واقع چنانچه بخواهید اینگونه عمل کنید باید خودتان به طور دستی هر روز این کد را تغییر داده و تاریخ آن روز را وارد نمایید که البته میتوانید این کار را با استفاده از يک query در ابتدای هر روز انجام دهید و یا اینکه به محض تغییر تاریخ این عمل را انجام دهید.

دقت در حذف کدهاي به ظاهر غير مؤثر

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

... WHERE column1 + 0
... WHERE 5 + 0.0
... WHERE column1 IN (1, 3, 3)
... CAST(1 AS INTEGER)
... WHERE 'a' || 'b'

يا مثلا فرض کنيد ستوني به نام col_indx داريم که مقدار آن هميشه بزرگتر از صفر باشد ، شايد جستجويي مثل عبارت زير غير منطقي به نظر برسد :

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

در مثال بالا ممکن است به دليل وجود يک ايندکس روي ستون col_indx ، شرط جستجو به اين شکل در آمده باشد و چنانچه اين کد را حذف کنيم نه تنها بازدهي بالا نخواهد رفت بلکه ممکن است شديدا افت کند.

گاهي اين کدها به دلايل ديگري مثل در نظر نگرفتن ايندکس ها، تغيير نوه دادهاي حاصل از محاسبه، تفاوت قائل شدن بين انواع داده اي مشابه (مثل samllint, integer) و ... ايجاد شده باشند. پس توصيه ما اين است که چنين عباراتي را تا زماني که دليل واقعي آنها را نمي دانيد تغيير ندهيد.

دقت در جستجوهاي حساس به حروف

از انجايي که يک قاعده کلي در مورد حساسيت به حروف در کليه DBMS ها وجود ندارد – مثلا Microsoft Access رشته "SMITH" را با "Smith" يکسان در نطر ميگيرد اما SQL Server به گونه اي است که در آن ميتوان انتخاب کرد که حساس به حروف باشد يا نه- برخي برنامه نويسان در صدد برمي آيند تا با افزودن کدهايي عدم حساسيت به حروف را مثلا اينگونه نشان دهند :

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

عبارت 'résumé' در زبان آلماني به معناي curriculum vitae است که اگر تابع زير روي آن اعمال گردد :

حاصل RESUME خواهد بود بمعناي begin again !!!

چرا که در هنگام تبديل حروف کوچک به بزرگ علايم نحوي آن حذف گرديده اند و معناي لغت بکلي دگرگون شده است.

پرهيز از اعمال توابع بر روي ستونها

Microsoft و Oracle به شدت توصيه دارند که تا حد ممکن از اعمال توابع بر روي ستونها خودداري نماييد مثلا به جاي عبارت زير :

... WHERE column1 = 'SMITH'
OR column1 = 'Smith'

از اين عبارت استفاده نماييد :

... WHERE column1 = 'SMITH'
OR ('SMITH' <> 'Smith' AND column1 = 'Smith')

که باعث مي شود ارجاع به ستون column1 و بازيابي مقدار آن حداقل شود که همين کار باعث بالا رفتن سرعت پردازش ميگردد.

منبع : SQL Tuning
مترجم: علیرضا شیرازی- ar.shirazi@gmail.com