none
בדיקה באמצעות פונקציה ב-SELECT RRS feed

  • שאלה

  • היי.

    הייתה לי שאילתא שמביאה נתוני ביצוע של מטלות למשתמש הנוכחי, אותו היא מקבלת באמצעות הפונקציה udf_GetUserId.

    בהתחלה השאילתא נכתבה כך:

    ; WITH pp as (select dbo.udf_GetUserId() as UserID)

    SELECT *

    FROM pp JOIN EventsExecutions ee ON pp.UserID = ee.UserID

    בצורה זו, היא רצה כ-45 שניות.

    עבדנו כדי לשפר אותה, ניסינו כל מיני דברים, ובסוף מה שהוריד את הזמן ל-2 שניות היה:

     

    SELECT *

    FROM EventsExecutions ee

    WHERE ee.UserID = dbo.udf_GetUserId()

    כמו שכולם יודעים, זה עובד ולא נוגעים, ועם תוצאות לא מתווכחים, ושאר קלישאות...... אבל עדיין משהו מטריד אותי פה:

    באינטואיציה, זה אמור להיות לא טוב (מבחינת ביצועים לפחות) להשוות בכל שורה מחדש לפונקציה.... ולכן דווקא JOIN לתוצאה של הפונקציה היה אמור להיות טוב יותר, לא?

    מה אני מפספס פה?????

    תודה.


    itaigitt, http://copypastenet.blogspot.com
    יום שלישי 20 דצמבר 2011 09:38

תשובות

  • הי איתי,

    זה היה די ברור שהפונקציה סקלארית אחרת היתה מקבל שגיאה בהשוואה בין UserId לערך המוחזר.

    פה לא הבעיה, לדעתי ההבדל הוא בקריאה לפונקציה עבור ה- Users שנמצאים בטבלת EventExecutions לבין כל ה- Users כפי שמופיע בשאילתא הראשונה.

    הרי מה קורה בפועל:

    בשאילתה הראשונה קודם נוצרת הטבלה עם כל ה- Uses-ים ואז מתבצע Join.

    בשניה יש קריאה לפונקציה רק עבור ה- User הספציפי, מכוון שכנראה ה- UserId מאונדקס האזי הפונקציה מייצר מידע רק עבור אותו Uesr.

    תוכניות ביצוע יתנו לך הסבר ברור בהרבה.

     


    אסף שלם
    • סומן כתשובה על-ידי itaigitt יום שלישי 20 דצמבר 2011 14:00
    יום שלישי 20 דצמבר 2011 11:32
  • לא בהכרח... האופטי-ממזר יותר חכם מזה.

    מכיוון שאחת הדרישות מפונקציה היא שהיא תהיה דטרמיניסטית (וזאת אחת הסיבות...), ומכיוון שבמקרה הזה אין פרמטרים לפונקציה, הוא מתייחס אליה כביטוי (Expression) מכיוון שלא ייתכן שיחזרו תוצאות שונות מהפונקציה עבור כל שורה.

    HTH


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--
    • סומן כתשובה על-ידי itaigitt יום רביעי 21 דצמבר 2011 15:30
    יום רביעי 21 דצמבר 2011 13:40
  • נכון.

    אותו דבר תקף גם ל:  (WHERE Column = (5+5

    WHERE Column = @@SPID

    ואפילו:

    ()WHERE Column = GETDATE או (...)WHERE Column > RAND וכולי.

    אין שום סיבה לחשב מחדש את הביטוי עבור כל שורה.

    החריג לכלל הוא פונקציית NEWID.

    HTH


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--
    • סומן כתשובה על-ידי itaigitt יום רביעי 21 דצמבר 2011 15:29
    יום רביעי 21 דצמבר 2011 15:26
  • היי.

    אסף - כמו שרשמתי, הפונקציה תמיד תחזיר ערך (USER ID) אחד.

     

    ההסבר למה שהיה הוא כזה:

    כשמשווים עמודה לפונקציית SCALAR (כמו בשאילתא הטובה)– הדבר מבוצע כך:

    התוצאה של הפונקציה מחושבת ואז מושווה לעמודה בתור ערך קבוע, בדומה להשוואה של תוצאת DATEADD לעמודה.

    ולכן הפונקציה רצה רק פעם אחת והאופטימייזר יודע שהוא צריך לשלוף רק שורות ששוות לערך ספציפי.

    כשהפונקציה בשימוש בתור טבלה, האופטימייזר, כנראה, סורק את הטבלה החיצונית כי הוא לא יודע שיש מולו ערך סלקטיבי, כי הוא חושב שעומד בפניו הצורך לעשות JOIN לטבלה שלמה בלי לדעת כמה שורות עונות על התנאי שב JOIN.

    ותודה לשי אנגלברג! 


    itaigitt, http://copypastenet.blogspot.com
    • סומן כתשובה על-ידי itaigitt יום שלישי 20 דצמבר 2011 13:47
    יום שלישי 20 דצמבר 2011 13:47

כל התגובות

  • הי שרון,

    בגדול אתה יכול להסתכל בתוכניות הביצועה של שני השאילתות ולהבין את ההבדל.

    ניחוש\תחושה שלי , בחלק הראשון נוצר result set בשם PP שמכיל את כל ה- Users של המערכת מכוון שהוא נבנה לפני ה- Join.

    בשני אומנם יש לך Join כאשר שדה = ערך מוחזור מפונקציה על רק עבור ה- Users שנמצאים בטבלת EventsExecutions.

    בקיצור תוכנית הביצוע תתן לך תשובה ברורה ובמידה ולא תצרף את תוכניות הביצוע ואני ינסה להבין את ההבדלים.

    בהצלחה

     

     


    אסף שלם
    יום שלישי 20 דצמבר 2011 10:07
  • היי אסף.

    קודם כל איתי, לא שרון :-)

    אולי לא הסברתי מספיק ברור: הפונקציה היא סקלארית ומחזירה USERID אחד, ולפיו מפלטרים שורות מה-VIEW של EventsExecutions.

    איתי.


    itaigitt, http://copypastenet.blogspot.com
    יום שלישי 20 דצמבר 2011 11:25
  • הי איתי,

    זה היה די ברור שהפונקציה סקלארית אחרת היתה מקבל שגיאה בהשוואה בין UserId לערך המוחזר.

    פה לא הבעיה, לדעתי ההבדל הוא בקריאה לפונקציה עבור ה- Users שנמצאים בטבלת EventExecutions לבין כל ה- Users כפי שמופיע בשאילתא הראשונה.

    הרי מה קורה בפועל:

    בשאילתה הראשונה קודם נוצרת הטבלה עם כל ה- Uses-ים ואז מתבצע Join.

    בשניה יש קריאה לפונקציה רק עבור ה- User הספציפי, מכוון שכנראה ה- UserId מאונדקס האזי הפונקציה מייצר מידע רק עבור אותו Uesr.

    תוכניות ביצוע יתנו לך הסבר ברור בהרבה.

     


    אסף שלם
    • סומן כתשובה על-ידי itaigitt יום שלישי 20 דצמבר 2011 14:00
    יום שלישי 20 דצמבר 2011 11:32
  • אני מניח בניחוש מוחלט (ואני לא מבין למה לא מצורף DDL+DML או לפחות תוכנית הרצה כדי שנוכל לעבור מעולם הניחוש לעולם המסקנות) שבשימוש הישיר יש עם כן מעבר על כל הנתונים גם אם UserId מאונדקס. פשוט מפני שהתנאי מופעל על תוצאת הפונקציה ולכן אין ברירה אלא לבדוק את החישוב עבור כל רשומה כדי לבדוק אם התנאי מתקיים (לכן אלא אם התוצאה של החישוב נשמרת באינדקס אין ברירה אלא לעבור על כל הרשומות).

    אבל (ושוב זה זריקה ברמת ניחוש)

    הדרך השנייה מבוצעת הפונקציה על כל רשומה אבל אין ביצוע של פעולה יקרה של JOIN שכנראה בדרך הראשונה אולי מבוצעת בפועל בין כל הנתונים לכל הנתונים המחושבים (בדומה ל SELF JOIN)

    כמו כן אם השדה מאונדקס הרי שבדרך זו אולי הפונקציה בוצעה על האינדקס ולא על השדה שבטבלה המקורית (ז"א יהיה סריקה של האינדקס במקום סריקה של הטבלה)

    כמו כן תוצאות של החישוב בפונקציה אולי נשמרות ב CASH כלשהו ולכן במקרה זה לא באמת עוברים על כל הרשומות. מה שמעלה את השאלה למה לא לעשות שימוש העמודה מחושבת בטבלה עצמה במקום לעבוד בצורה זו (להוסיף למשל עמודה שהערך שלה זה התוצאה של החישוב או מתקבל מהחישוב... הייתי בודק אפשרות זו למשל). עמודה מחושבת זו ניתן לאנדקס דבר שיכול לשפר את העבודה (או להפריע כמו כל אינדקס).


    signature
    יום שלישי 20 דצמבר 2011 13:45
    מנחה דיון
  • היי.

    אסף - כמו שרשמתי, הפונקציה תמיד תחזיר ערך (USER ID) אחד.

     

    ההסבר למה שהיה הוא כזה:

    כשמשווים עמודה לפונקציית SCALAR (כמו בשאילתא הטובה)– הדבר מבוצע כך:

    התוצאה של הפונקציה מחושבת ואז מושווה לעמודה בתור ערך קבוע, בדומה להשוואה של תוצאת DATEADD לעמודה.

    ולכן הפונקציה רצה רק פעם אחת והאופטימייזר יודע שהוא צריך לשלוף רק שורות ששוות לערך ספציפי.

    כשהפונקציה בשימוש בתור טבלה, האופטימייזר, כנראה, סורק את הטבלה החיצונית כי הוא לא יודע שיש מולו ערך סלקטיבי, כי הוא חושב שעומד בפניו הצורך לעשות JOIN לטבלה שלמה בלי לדעת כמה שורות עונות על התנאי שב JOIN.

    ותודה לשי אנגלברג! 


    itaigitt, http://copypastenet.blogspot.com
    • סומן כתשובה על-ידי itaigitt יום שלישי 20 דצמבר 2011 13:47
    יום שלישי 20 דצמבר 2011 13:47
  • הי איתי,

    פעם הבאה אני ינסה להיות יותר מדוייק אבל זה בדיוק מה שהתכוונתי.

    ד"ש לשי, בעצם עזוב אני ידבר איתי הוא פה לידי :).

    יום טוב


    אסף שלם
    יום שלישי 20 דצמבר 2011 13:50
  • תודה אסף!
    itaigitt, http://copypastenet.blogspot.com
    יום שלישי 20 דצמבר 2011 14:01
  • עדיין לפי מה שאני מבין החישוב של הפונקציה בוצע על כל שורה לפחות פעם אחת
    signature
    יום רביעי 21 דצמבר 2011 06:43
    מנחה דיון
  • לא בהכרח... האופטי-ממזר יותר חכם מזה.

    מכיוון שאחת הדרישות מפונקציה היא שהיא תהיה דטרמיניסטית (וזאת אחת הסיבות...), ומכיוון שבמקרה הזה אין פרמטרים לפונקציה, הוא מתייחס אליה כביטוי (Expression) מכיוון שלא ייתכן שיחזרו תוצאות שונות מהפונקציה עבור כל שורה.

    HTH


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--
    • סומן כתשובה על-ידי itaigitt יום רביעי 21 דצמבר 2011 15:30
    יום רביעי 21 דצמבר 2011 13:40
  • אני בתגובה האינטואטיבית שלי תמיד קודם כל חושב שפונקציה ב-WHERE או ב-JOIN זה לא טוב, כי היא תופעל על כל שורה, ופה זה לא המקרה (וזה בטוח, כי עם העובדות של הביצועים ווה-PLANS אי אפשר להתווכח...) ולא הבנתי עד להערה של עמי למה.

    אז אם אני מבין עכשיו נכון - זה שהפונקציה מחזירה ערך קבוע זאת הסיבה שהיחס אליה הוא כביטוי ולא כמשהו שצריך לחשב מחדש עבור כל שורה?


    itaigitt, http://copypastenet.blogspot.com
    יום רביעי 21 דצמבר 2011 14:01
  • נכון.

    אותו דבר תקף גם ל:  (WHERE Column = (5+5

    WHERE Column = @@SPID

    ואפילו:

    ()WHERE Column = GETDATE או (...)WHERE Column > RAND וכולי.

    אין שום סיבה לחשב מחדש את הביטוי עבור כל שורה.

    החריג לכלל הוא פונקציית NEWID.

    HTH


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--
    • סומן כתשובה על-ידי itaigitt יום רביעי 21 דצמבר 2011 15:29
    יום רביעי 21 דצמבר 2011 15:26
  • תודה לכולם!

    חג חנוכה שמח!


    itaigitt, http://copypastenet.blogspot.com
    יום רביעי 21 דצמבר 2011 15:31
  • צודק :-)

    לא שמתי לב שתוצאת הפונקציה לא תלויה ברשומה עצמה אלא מחזירה תוצאה קבועה.

    חג שמח

    יום רביעי 21 דצמבר 2011 18:38
    מנחה דיון