הבלוג של גרי רשף

07/12/2011

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

Filed under: Uncategorized — גרי רשף @ 21:55

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

Use AdventureWorks;
Go

Select  *
From    sys.objects
Where   type Like '_F';

clip_image002

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);

clip_image004

ונעיין ב-Executions Plans:

clip_image006כפי שאפשר לראות- ה-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();

clip_image008

ננסה לבצע זאת ל-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

clip_image010

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

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

מודעות פרסומת

3 תגובות »

  1. היי גרי,
    כל הכבוד

    תגובה של drorch11 — 02/01/2012 @ 16:41

  2. יפה מאוד.
    עולם הפונקציות ב SQL זה עולם מאד שונה מתיכנות רגיל של OO.
    כדאי לשים לב לעולם הביצועים ששונה מכתיבת OO רגילה.

    כל הכבוד.

    תגובה של פיני קרישר — 11/12/2011 @ 10:00


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: