none
שאילתא עם פרמטרים מול שאילתא עם קבועים RRS feed

  • שאלה

  • היי.

    SELECT D.Name, D.ParentID, ...
    FROM	[Org].[udf_Function] (0,null) D
    JOIN	SomeTable t ON D.ID = t.ID
    
    declare @UserID int = 0, @RootID int = null
    
    SELECT D.Name, D.ParentID, ...
    FROM	[Org].[udf_Function] (@UserID,@RootID) D
    JOIN	SomeTable t ON D.ID = t.ID

    השאילתא הראשונה לוקחת 0 זמן, השניה מספר שניות. השניה נמצאת אצלי ב-SP שמקבלת את הפרמטרים שאני הגדרתי פה ידנית.

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

    תודה!


    itaigitt, http://copypastenet.blogspot.com

    יום שני 30 יולי 2012 08:50

תשובות

כל התגובות

  • הי ,

    נשמע כמו בעיה של Execution plan  שלא מתאים.
    תשווה בין ה Execution plan  של 2 המקרים

    ותנסה להריץ עם With recompile

    http://msdn.microsoft.com/en-us/library/ms190439.aspx

    תסתכל בבקשה גם על המאמרים הללו:
    http://pratchev.blogspot.co.uk/2007/08/parameter-sniffing.html

    http://msdn.microsoft.com/en-us/library/ms177500(v=sql.105).aspx

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

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

    נועם

    • סומן כתשובה על-ידי itaigitt יום שני 30 יולי 2012 11:12
    יום שני 30 יולי 2012 10:24
  • תודה!

    itaigitt, http://copypastenet.blogspot.com

    יום שני 30 יולי 2012 11:12
  • הי איתי,

    אני מניח שהפונקציה "Org.udf_Function" היא Inline Function, ולכן המקרה שלך לא קשור ל-Parameter Sniffing. ה-Query Processor לא יוצר Execution Plan נפרד עבור Inline Function. הוא בונה שאילתה חדשה שמורכבת מהשאילתה החיצונית שקוראת לפונקציה והשאילתה שבתוך הפונקציה, ולזה הוא יוצר Execution Plan. לכן אין כאן Parameter Sniffing.

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

    כדי לפתור את הבעיה, יש כמה אפשרויות:

    1. אם אתה יכול להרשות לעצמך לקמפל מחדש את השאילתה כל פעם, אז אתה יכול להוסיף (OPTION (RECOMPILE בסוף השאילתה.

    2. להעביר את הערכים כפרמטרים ל-Stored Procedure או לקוד דינמי (sp_executesql), ואז ה-Optimizer יוכל להשתמש ב-Parameter Sniffing.

    3. אם הערכים לא אמורים להשתנות, פשוט תשתמש בקבועים.

    בהצלחה!

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

    יום שישי 10 אוגוסט 2012 04:21
    מנחה דיון