ההבדל בין Inline Table-Valued Functions ו-Multistatement Table-valued Functions

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

Use AdventureWorks;

Go

Select  *

From    sys.objects

Where   type Like '_F';

Inline Table-Valued Functions (להלן Inline) – פונקציה הכוללת (חוץ מההגדרות הטריוויאליות) – פקודת Select בודדה שעושה שימוש בפרמטרים של הפונקציה ומחזירה טבלה (סט), וכפי שנראה בהמשך- מדובר ב-View משופר.

Multistatement Table-valued Functions (להלן Multistatement)– פונקציה שיכולה לכלול מספר פקודות שבמהלכן מוכנסות שורות למשתנה טבלה שהוגדר בתחילתה, ותוכן משתנה הטבלה הזה מוחזר לבסוף; וכפי שנראה בהמשך- מדובר בפרוצדורה מוגבלת.

מה ההבדל? ניקח כדוגמה שליפה מפולטרת מטבלת מערכת-

Use tempdb;

Go

If Object_Id('F_Inline','IF') Is Not Null Drop Function F_Inline;

Go

Create Function dbo.F_Inline(@Schema_id Int)

Returns Table As Return

Select  *

From    sys.objects

Where   Schema_id=@Schema_id;

Go

If Object_Id('F_Multistatement','TF') Is Not Null Drop Function F_Multistatement;

Go

Create Function dbo.F_Multistatement(@Schema_id Int)

Returns @Tbl Table(name sysname,

                   object_id Int,

                   principal_id Int,

                   schema_id Int,

                   parent_object_id Int,

                   type Varchar(2),

                   type_desc Varchar(50),

                   create_date DateTime,

                   modify_date DateTime,

                   is_ms_shipped Int,

                   is_published Int,

                   is_schema_published Int)

                   As

Begin

Insert

Into    @Tbl

Select  *

From    sys.objects

Where   Schema_id=@Schema_id;

Return

End;

Go

Select * From dbo.F_Inline(1);

Select * From dbo.F_Multistatement(1);

ונעיין ב-Executions Plans:

כפי שאפשר לראות- ה-Inline זולה יותר ו"עולה" רק 35%,

ואילו ה-Multistatement עולה בשני תשלומים 8%+58%=66% כאשר השני מבינהם כולל אותם סעיפים פחות או יותר כמו ה-Inline אבל בצירוף הכנסת הפלט לטבלה הזמנית (Table Insert), והראשון כולל את הפעלת הפונקציה והשליפה מהטבלה הזמנית.

Inline כפי שציינתי היא View משופר שכולל אופציה לפרמטרים. לא ניתן להגדיר בה משתנים, לבצע חישובי ביניים וכו'; אבל היא חוסכת את הצורך ב-Insert & Select לטבלה זמנית, ובנוסף- המערכת יכולה להשתמש באינדקסים ובסטטיסטיקות של טבלאות המקור כשבמצעים Join בינה לבין טבלאות אחרות. כלומר- אם ב-View יש Join בין טבלה A וטבלה B, ונבצע Join בין ה-View לבין טבלה C – המערכת תסדר את שני ה-Joins בסדר שיראה לה יעיל יותר ולא תהיה "מחוייבת" לבצע קודם את ה-Join שבתוך ה-View ולאחר מכן את החיצוני; וכך גם פני הדברים ב-Inline.

בנוסף- ניתן לבצע ל-Inline פעולות Delete & Update & Insert. ניצור טבלה, Inline השולפת ממנה, ונבצע Insert לפונקציה:

Use tempdb;

Go

If Object_Id('MyTbl','U') Is Not Null Drop Table MyTbl;

Go

Create Table MyTbl(ID Int Identity Primary Key,

                   Name Varchar(50));

Go

If Object_Id('dbo.MyInlineFunc','IF') Is Not Null Drop Function dbo.MyInlineFunc;

Go

Create Function dbo.MyInlineFunc()

Returns Table As Return

Select  *

From    MyTbl;

Go

Insert

Into    dbo.MyInlineFunc()

Select  'Try';

Select  *

From    dbo.MyInlineFunc();

ננסה לבצע זאת ל-MultiStatement וכצפוי ניכשל:

If Object_Id('dbo.MyMultistatementFunc','TF') Is Not Null Drop Function dbo.MyMultistatementFunc;

Go

 

 CreateFunction dbo.MyMultistatementFunc()

Returns @Tbl Table(ID Int Primary Key,

                   Name Varchar(50))

As

Begin

Insert

Into    @Tbl

Select  *

From    MyTbl;

Return

End;

Go

Insert

Into   dbo.MyMultistatementFunc()

Select 'Try';

Go

לסיכום: היכן שאפשר עדיף בדרך כלל להשתמש ב-Inline, כמובן- בכפוף לכך שניתן יהיה לעשות הכל במכה אחת עם פקודת Select אחת.

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