none
האם בפרוצדורה בה יש IF...ELSE מחושבים שני חלקי ה-IF? RRS feed

  • שאלה

  • אהלן.

    יש לי פרוצדורה אשר מריצה שאילתא, ולפי פרמטר מחליטה האם להוסיף LEFT JOIN לעוד טבלאות או שלא. ממומש עם IF...ELSE.

    האם במקרה כזה ה-SQL מבצע את שני חלקי ה-IF ואז בוחר לפי הפרמטר מה להחזיר, או שקודם כל הוא רואה מה הפרמטר ומריץ את השאילתא הרלוונטית בלבד?

    בניתוח ה-execution plan ראינו דברים סותרים ומבלבלים, מבחינת זמנים היה נראה, לפחות במקרים מסויימים, כאילו זמן הריצה עם IF...ELSE ובלעדיו שונים, כאשר בלעדיו הוא בערך חצי זמן....

    הנה דוגמא:

    CREATE PROCEDURE [dbo].[spr_ifExecPlan_CTE]
    	@Mode int
    	WITH RECOMPILE
    AS
    
    IF @Mode = 0
    BEGIN
    	; with MyCTE as
    	(
    		select IntCol1, IntCol2
    		from dbo.ValuesTest v
    		join dbo.Employee e	on v.IntCol1 = e.EmpID
    	)
    	SELECT	a.Column1, s.IntColumn
    	FROM	dbo.AllTable a
    	JOIN	dbo.SimpleTable s	ON	a.AllID = s.SimpleTableID
    	JOIN		MyCTE m				ON	s.SimpleTableID = m.IntCol1
    END
    ELSE
    BEGIN
    	; with MyCTE as
    	(
    		select IntCol1, IntCol2
    		from dbo.ValuesTest v
    		join dbo.Employee e	on v.IntCol1 = e.EmpID
    	)
    	SELECT	a.Column1, s.IntColumn, i.IntC
    	FROM		dbo.AllTable a
    	JOIN		dbo.SimpleTable s	ON	a.AllID = s.SimpleTableID
    	JOIN		MyCTE m				ON	s.SimpleTableID = m.IntCol1
    	LEFT JOIN	dbo.IntTable i		ON	s.SimpleTableID = i.IntA
    END
    GO
    
    EXEC [spr_ifExecPlan_CTE] 0
    
    EXEC [spr_ifExecPlan_CTE] 1
    בהרצת הפרוצדורה ובדיקה רגילה של ה-EXECUTION PLAN, כל הרצה מבצעת רק את החלק הרלוונטי של ה-IF

    וכנ"ל לגבי הרצה עם הערך 1.

    אבל, כאשר הרצנו set showplan_xml on ולקחנו את ה-XML לתוכנה שמציגה EXECUTION PLANS ראינו:

    כאמור - מבחינת זמני ריצה אכן נראה שיש חישוב כפול....

    אני יודע שלגבי CASE תמיד מתבצעים כל חלקי ה-CASE ונבחר המתאים, האם זה כך גם לגבי IF?

    תודה, איתי.


    itaigitt, http://copypastenet.blogspot.com

    יום שלישי 07 ינואר 2014 10:23

תשובות

  • היי איתי,

    עשיתי בדיקה קצרה אצלי ומבחינת ה-Plan באמת ראיתי את מה שאמרת:

    ב-Plan שהוא יוצר שאותו ניתן לשלוף מה-Cache, יש תנאי ומתחתיו הפנייה לשאילתא הרלוונטית, לא משנה מה מעבירים.

    אבל ב-Plan שרואים בזמן ריצה רואים רק את החלק הרלוונטי. יכול להיות שהוא נעשה קצת יותר חכם מאז ההרצאה של בוב.

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


    Matan Yungman
    SQL Server Expert, Blogger and Podcaster
    Blog
    Podcast


    • נערך על-ידי Matan Yungman יום רביעי 08 ינואר 2014 11:18
    • סומן כתשובה על-ידי itaigitt יום רביעי 08 ינואר 2014 12:36
    יום רביעי 08 ינואר 2014 11:17
  • הי,

    הסיפור די פשוט...

    בזמן קומפילציה, SQL Server מקמפל את כל השאילתות שמופיעות בפרוצדורה כולל כל ההסתעפויות (אלא אם כן יש הסתעפות שלעולם לא תתרחש). התוצאה היא תוכנית ביצוע שכוללת בתוכה את כל ההסתעפויות ואת כל השאילתות, בדומה למה שמתן הציג. בזמן ריצה, לעומת זאת, רק השאילתות הרלוונטיות מורצות, וה-Actual Plan כולל רק את השאילתות האלה בלי כל ההסתעפויות.

    מקווה שעזרתי...

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי itaigitt יום ראשון 19 ינואר 2014 07:34
    יום ראשון 19 ינואר 2014 07:06
    מנחה דיון

כל התגובות

  • היי איתי,

    אני ממליץ בחום על ההרצאה המעולה הזו של Bob Beauchemin מ-SQLSkills:

    http://channel9.msdn.com/Events/TechEd/Europe/2009/DAT405

    הוא מדבר שם על הרבה דברים שקשורים ל-Plan Cache ול-Parameter Sniffing שלדעתי רלוונטיים לשאלות האחרונות ששאלת.

    ספציפית לגבי המקרה הזה, הוא אומר שאם מעבירים פרמטר שנופל ב-IF, תהיה אופטימיזציה לשתי השאילתות, אבל אם עובר פרמטר שנופל ב-ELSE, תהיה אופטימיזציה רק לחלק של ה-ELSE.

    הרבה יותר בריא שה-IF יפנה לפרוצדורות שכל אחת תעשה את ה-Select שרלוונטי לה. ככה האופטימיזציה תהיה הרבה יותר ממוקדת וטובה.


    Matan Yungman
    SQL Server Expert, Blogger and Podcaster
    Blog
    Podcast

    • הוצע כתשובה על-ידי Eran Sharvit יום רביעי 08 ינואר 2014 12:32
    יום שלישי 07 ינואר 2014 12:09
  • היי מתן,

    בתמונה שהעליתי בשאלה ניתן לראות שגם במקרה ה-IF הוא הראה לכאורה ריצה על החלק המתאים בלבד (למרות שמה שאמרת מסתדר עם בדיקה שערכנו לגבי זמני ריצה)....

    תודה!


    itaigitt, http://copypastenet.blogspot.com

    יום שלישי 07 ינואר 2014 12:19
  • היי איתי,

    עשיתי בדיקה קצרה אצלי ומבחינת ה-Plan באמת ראיתי את מה שאמרת:

    ב-Plan שהוא יוצר שאותו ניתן לשלוף מה-Cache, יש תנאי ומתחתיו הפנייה לשאילתא הרלוונטית, לא משנה מה מעבירים.

    אבל ב-Plan שרואים בזמן ריצה רואים רק את החלק הרלוונטי. יכול להיות שהוא נעשה קצת יותר חכם מאז ההרצאה של בוב.

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


    Matan Yungman
    SQL Server Expert, Blogger and Podcaster
    Blog
    Podcast


    • נערך על-ידי Matan Yungman יום רביעי 08 ינואר 2014 11:18
    • סומן כתשובה על-ידי itaigitt יום רביעי 08 ינואר 2014 12:36
    יום רביעי 08 ינואר 2014 11:17
  • הי,

    הסיפור די פשוט...

    בזמן קומפילציה, SQL Server מקמפל את כל השאילתות שמופיעות בפרוצדורה כולל כל ההסתעפויות (אלא אם כן יש הסתעפות שלעולם לא תתרחש). התוצאה היא תוכנית ביצוע שכוללת בתוכה את כל ההסתעפויות ואת כל השאילתות, בדומה למה שמתן הציג. בזמן ריצה, לעומת זאת, רק השאילתות הרלוונטיות מורצות, וה-Actual Plan כולל רק את השאילתות האלה בלי כל ההסתעפויות.

    מקווה שעזרתי...

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי itaigitt יום ראשון 19 ינואר 2014 07:34
    יום ראשון 19 ינואר 2014 07:06
    מנחה דיון