آژانس هواپیماییexchanging

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

شروع موضوع توسط alireza sh ‏3 ژانویه 2006 در انجمن زبان T-SQL

  1. alireza sh

    alireza sh مدیر بازنشسته

    تاریخ عضویت:
    ‏2 ژانویه 2006
    نوشته ها:
    2,668
    تشکر شده:
    66
    محل سکونت:
    نصف جهان
    با سلام به دوستان گرامي

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

    بهبود بازدهي با بهينه کردن کد 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 هاي معروف به طور خودکار اين عمل را انجام ميدهند.
     
  2. alireza sh

    alireza sh مدیر بازنشسته

    تاریخ عضویت:
    ‏2 ژانویه 2006
    نوشته ها:
    2,668
    تشکر شده:
    66
    محل سکونت:
    نصف جهان
    اصل تعدي
    بر اساس اصل تعدي داريم :
    کد:
    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
    
     
     
  3. alireza sh

    alireza sh مدیر بازنشسته

    تاریخ عضویت:
    ‏2 ژانویه 2006
    نوشته ها:
    2,668
    تشکر شده:
    66
    محل سکونت:
    نصف جهان
    تبديل ثوابت به مقدار عددي آنها

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

    عدم حذف کدهاي به ظاهر غير مؤثر
    در برخي مواقع به کدهايي برميخوريم که بديهي به نظر ميرسند و به ظاهر زائد ميباشند. مثلا قطعه کدهاي زير را ببينيد :
    کد:
    ... 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 !!!
    چرا که در هنگام تبديل حروف کوچک به بزرگ علايم نحوي آن حذف گرديده اند و معناي لغت بکلي دگرگون شده است.
     
    parial1 از این نوشته تشکر کرده است.
  4. mazoolagh

    mazoolagh Registered User

    تاریخ عضویت:
    ‏10 آپریل 2004
    نوشته ها:
    2,940
    تشکر شده:
    7
    قبل از هرچیز اجازه بدین از شما بخاطر این مطالب مفید تشکر کنم.

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

    alireza sh مدیر بازنشسته

    تاریخ عضویت:
    ‏2 ژانویه 2006
    نوشته ها:
    2,668
    تشکر شده:
    66
    محل سکونت:
    نصف جهان
    متاسفانه هنوز اون قسمت رو نرسيدم کامل کنم

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

    Mehdi مدیر بازنشسته

    تاریخ عضویت:
    ‏1 آگوست 2004
    نوشته ها:
    5,569
    تشکر شده:
    49
    محل سکونت:
    Anywhere
    قشنگ بود
    منتظر بقيش هستيم
     
  7. appbannerkhuniresbanner
  8. alireza sh

    alireza sh مدیر بازنشسته

    تاریخ عضویت:
    ‏2 ژانویه 2006
    نوشته ها:
    2,668
    تشکر شده:
    66
    محل سکونت:
    نصف جهان
    به زودی این بخش را ادامه میدهم !!! پس از یک سال و اندی !!!
     
  9. شايان

    شايان مدیران قدیمی

    تاریخ عضویت:
    ‏2 سپتامبر 2003
    نوشته ها:
    4,806
    تشکر شده:
    9
    جناب شیرازی ممنون بابت تاپیک و وقتی که گذاشتید.
     
  10. Graveworm

    Graveworm Registered User

    تاریخ عضویت:
    ‏23 می 2005
    نوشته ها:
    1,063
    تشکر شده:
    12
    محل سکونت:
    Tehran
    من الان این مطلب رو خوندم، جالب بود
    دستت درد نکنه، با اجازه منم اگه به نکته ای برخوردم، مینویسم :)
     
  11. alireza sh

    alireza sh مدیر بازنشسته

    تاریخ عضویت:
    ‏2 ژانویه 2006
    نوشته ها:
    2,668
    تشکر شده:
    66
    محل سکونت:
    نصف جهان
    خواهش میکنم شایان جان

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

    از grave moon هم تشکر میکنم
     
  12. alireza sh

    alireza sh مدیر بازنشسته

    تاریخ عضویت:
    ‏2 ژانویه 2006
    نوشته ها:
    2,668
    تشکر شده:
    66
    محل سکونت:
    نصف جهان
    بنده ادامه مطلب را در سایت خودم قرار دادم

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

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

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

    CZed کاربر تازه وارد

    تاریخ عضویت:
    ‏6 سپتامبر 2007
    نوشته ها:
    145
    تشکر شده:
    0
    محل سکونت:
    Tehran
    من فايده همچين كدي رو متوجه نميشم:
    کد:
    ... WHERE column1 IN (1, 3, 3)
    
    ميشه توضيح بدي كه كجا ممكنه همچين كدي كار مفيدي انجام بده؟
     
  14. avajang.com .leftavajang.com.right
  15. parial1

    parial1 Registered User

    تاریخ عضویت:
    ‏8 می 2008
    نوشته ها:
    110
    تشکر شده:
    37
    محل سکونت:
    تهران
    قشنگ بود چرا ادامه ندادید!!!!!!!!!!!!!!!!
     
  16. baxiha

    baxiha کاربر تازه وارد

    تاریخ عضویت:
    ‏24 سپتامبر 2012
    نوشته ها:
    653
    تشکر شده:
    174
    لازم بود تاپیک یه قرن پیش رو بیاری بالا آخه!
     
  17. shamabadi

    shamabadi Registered User

    تاریخ عضویت:
    ‏11 اکتبر 2012
    نوشته ها:
    127
    تشکر شده:
    14
    بازم تشکر میکنم