שימוש יעיל ב-Table Valued Functions

30/04/2012

אין תגובות

כששולפים מטבלה ידוע שיש להיזהר בשימוש ב-Scalar Functions הכוללות פניה נוספת לְטבלה מכיוון שהפניה לטבלה השנייה מתבצעת בנפרד לכל שורה מהטבלה הראשונה, אך מה קורה כשמבצעים Join בין טבלה לבין Table Valued Function? האם גם במקרה זה תתבצע פניה נפרדת לכל שורה?
לאחר שבפוסט הקודם הראיתי כיצד ניתן לעקוב אחר ריצות של פונקציות, ננצל את הפתרון משם כדי לבדוק כמה פעמים הפונקציה מופעלת.
ניצור Table Valued Function שמקבלת פרמטר ומחזירה אותו בתור טבלה, שימוש מגוחך כמובן לצורך הבדיקה בלבד; ואת הפרמטר נפנה לקובץ טקסט לצורך המעקב (בכל הפעלה יופנה הפרמטר לקובץ):

Use tempdb;

Go

 

If Object_ID('MyFn') Is Not Null Drop Function MyFn;

Go

 

Create Function dbo.MyFn(@I Int)

Returns @Tbl Table(I Int)

As

Begin

--כתיבה לקובץ הטקסט

Declare @S Varchar(8000);

Set @S='Echo '+Cast(@I As Varchar)+' >> C:\Tmp\MyFn.txt';

Exec XP_CmdShell @S;

--אכלוס הטבלה שיש להחזיר

Insert

Into @Tbl

Select @I;

--סיום

Return

End

Go

נשתמש בה עם טבלת sys.objects ונפנה אליה את ה-Object_ID של כל אובייקט, ובכל הרצה נקפיד למחוק את הקובץ כדי שנקבל קובץ חדש לכל הרצה:

Exec XP_CmdShell 'Del/Q C:\Tmp\MyFn.txt';

Select  O.name,

        O.object_id,

        O.schema_id,

        Fn.*

From    sys.objects O

Cross Apply dbo.MyFn(Object_ID) Fn;

clip_image002

כשפותחים את קובץ הטקסט C:\Tmp\Fn.txt הוא נראה כצפוי כך-

clip_image004

ובמקרה שלי יש בו 65 שורות כנגד 65 שורות בטבלת sys.objects, ללמדנו שהפונקציה הופעלה פעם אחת בכל שורה; די צפוי לאור העובדה שעליה להחזיר בכל פעם ערך אחר.

מה יקרה אם נפנה אליה אותו ערך בכל השורות? נפעיל אותה הפעם עם הפרמטר 1:

Exec XP_CmdShell 'Del/Q C:\Tmp\MyFn.txt';

Select  O.name,

        O.object_id,

        O.schema_id,

        Fn.*

From    sys.objects O

Cross Apply dbo.MyFn(1) Fn;

clip_image006

במקרה זה אנחנו למדים מקובץ הטקסט שהפונקציה הופעלה פעם אחת בלבד:

clip_image008

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

מה יקרה איפוא במקרה ביניים בו יהיו ערכים שונים שחוזרים על עצמם? ננסה להפנות אליה את הערך של עמודה schema_id. בדרך כלל יש בטבלה שב-tempdb שני ערכים שונים – 1 , 4 (סכימות sys ו-dbo):

Exec XP_CmdShell 'Del/Q C:\Tmp\MyFn.txt';

Select  O.name,

        O.object_id,

        O.schema_id,

        Fn.*

From    sys.objects O

Cross Apply dbo.MyFn(Schema_ID) Fn;

clip_image010

לפני שנתבונן בקובץ הטקסט נהמר בינינו לבין עצמנו כמה שורות אנחנו צפויים למצוא שם- האם 2 כנגד שני ערכים של schema_id או 65 כנגד מספר השורות בטבלה, אך הפתעה: יש בקובץ 7 שורות (בסביבות אחרות זה עלול להשתנות כפי שיוסבר בהמשך):

clip_image012

הכיצד? מתברר שהמערכת מבצעת Scan על טבלת sys.objects, כשהערך של schema_id בשורה כלשהי זהה לזה שבשורה הקודמת- היא משתמשת בפלט השמור אצלה בזכרון ולא מפעילה את הפונקציה מחדש, וכשהוא שונה- היא כן מפעילה.

בצילום המסך הנ"ל מה-SSMS ניתן לראות שהערך של schema_id השתנה 7 פעמים (תצטרכו להאמין לי שלפני שורה 45 ולאחר שורה 61 מופיעים רק ערכי 4)..

אותי זה הפתיע!

מה יקרה אם נמיין את הטבלה לפי schema_id כך שהערך ישתנה רק פעם אחת?

Exec XP_CmdShell 'Del/Q C:\Tmp\MyFn.txt';

Select  O.name,

        O.object_id,

        O.schema_id,

        Fn.*

From    sys.objects O

Cross Apply dbo.MyFn(Schema_ID) Fn

Order By Schema_ID;

clip_image014

וקובץ הטקסט:

clip_image016

הפונקציה הופעלה כעת פעמיים בלבד, וניתן ללמוד מכך שהמיון בוצע לפני ה-Join.

מסקנה: כשמבצעים Join (כלומר- Cross Apply) בין טבלה ל-Table Valued Function כדאי לשקול למיין את הטבלה לפי העמודה המופנית לפונקציה כפרמטר, כדי שהיא תופעל פחות פעמים.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *