דוגמה ליתרונות ולחסרונות של פונקציות החלון

04/06/2012

אין תגובות

נתקלתי באחד הפורומים בשאלה הבאה (השואל תרגם שאלה אמיתית לדוגמה טכנית ואני תרגמתי אותה חזרה לדוגמה "הגיונית"): נתון קוד של אובייקט בדטבייס, ויש לשלוף את כל האובייקטים שהם מאותו סוג ומאותה סכימה כמוהו.
כלומר- אם נתון המספר 1234 שהוא הקוד של טבלה מסכימה sales, יש לשלוף את פרטי כל הטבלאות מסכימה Sales.
ניצור קודם כל העתק של sys.objects כדי שנעבוד מול טבלה אמיתית ולא מול view של המערכת:

Use tempdb;

Go

 

If Object_ID('T_Objects','U') Is Not Null Drop Table T_Objects;

 

Select  *

Into    T_Objects

From    AdventureWorks.sys.objects;

 

Select * From T_Objects;

clip_image002

הפתרון שהוא הציע ושתאם את ניסוח הבעייה כלל שתי פניות ב-Where לטבלה, ויחד עם ה-From המקורי – הגענו לשלוש פניות לטבלה.

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

לבסוף – שימוש בפונקצית חלון שמאפשר לפנות פעם אחת לטבלה: נחלק את הטבלה לקבוצות (Partition By) על פי סוג האובייקט והסכימה, ונציג רק את הקבוצה של האובייקט הנתון.

(מי שמנסה- שיבחר במספר מתאים מהטבלה במקום 18099105)

Select  *

From    T_Objects

Where   schema_id=(Select schema_id From T_Objects Where object_id=18099105)

        And type_desc=(Select type_desc From T_Objects Where object_id=18099105);

 

Select  *

From    T_Objects T1

Where   Exists (Select 1

                From   T_Objects T2

                Where  T2.object_id=18099105

                       And T2.schema_id=T1.schema_id

                       And T2.type_desc=T1.type_desc);

 

With T As

(Select Max(Case When Object_id=18099105 Then 1 End) Over(Partition By schema_id,type_desc) MaxID,

        *

From    T_Objects)

Select  *

From    T

Where   MaxID=1;

clip_image004

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

יש שתי אפשרויות כיצד לאנדקס: או לשים קודם את ה-object_id שמשמש לפילטור של שאילתת המשנה, או לשים קודם את ה-schema_id & type_desc שמשמשים לילטור השאילתה הראשית.

נסיון ראשון:

If (Select Count(1) From sys.indexes Where name='IDX_T')>0 Drop Index T_Objects.IDX_T;

Create Clustered Index IDX_T On T_Objects(object_id,schema_id,type_desc);

clip_image006

השאילתה הראשונה היא היעילח ביותר במקרה זה, העלות המוחלטת שלה היא 0.0220846, שתי הפניות ב-Where הן Index Seek יעיל ורק הפניה ב-Select הראשי מצריכה Table Scan מלא.

השאילתה השניה פחות יעילה- אמנם Index Seek אחד ו-Table Scan אחד, אבל בינהם יש Hash Match שהוא פחות יעיל מה-Nested Loops של הראשונה.

השאילתה השלישית – הכי גרועה גם כאן וגם בסה"כ- בעיקר מפני שהאינדקס לא מסייע לה, וקודם מתבצעת החלוקה לקבוצות; ולכן יש למיין את הנתונים בהתאם להן.

נסיון שני:

If (Select Count(1) From sys.indexes Where name='IDX_T')>0 Drop Index T_Objects.IDX_T;

Create Clustered Index IDX_T On T_Objects(schema_id,type_desc,object_id);

clip_image008

השליפה הראשונה היא הכי גרועה במקרה זה והיא מבצעת שני Tables Scan (כלומר- האינדקס אינו עוזר לה לבצע Index Seek) ו-Index Seek אחד.

השליפה השניה היא הכי טובה גם כאן וגם בסך הכל וכוללת Table Scan לאיתור השורה בשאילתת המשנה, ו-Index Seek לחיפוש שאר השורות.

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

נשווה את העלויות המוחלטות (Estimated Subtree Cost) של השליפות השונות:

שלוש פניות לטבלה

שתי פניות לטבלה

פניה אחת לטבלה

אינדקס ראשון

0.0220846 (2)

0.0378408

0.0482616

אינדקס שני

0.0300508

0.0158846 (1)

0.0261624 (3)

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

1. לשליפות שונות מתאימים אינדקסים שונים, ולכן יש להתאים את השליפה לאינדקס ולהיפך.

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

3. התוצאות האלו נכונות לטבלה עליה התבצעו הנסיונות. בטבלאות אחרות יתכנו תוצאות שונות.

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

כתיבת תגובה

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