שיפור ביצועי שאילתות על ידי שימוש ב-Group By במקום ב-Join / Exists

21/11/2010

אין תגובות

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

בטבלה יש למצוא את כל החשבונות הרדומים- אלו שבשנה האחרונה לא היו בהם תנועות אבל לפני כן, ולציין מה תאריך התנועה האחרון (אי שם לפני למעלה משנה).

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

שלוף את כל החשבונות עם תאריך התנועה האחרון שלהם שלא היו להם תנועות חובה או זכות בשנה האחרונה וכן היו להם תנועות חובה או זכות לפני כן.

ובשפת SQL:

Select Distinct T1.Bank,

        T1.Snif,

        zT1.Heshbon,

        T3.Taarih

From    MyTbl T1

Left Join (Select Bank,

                Snif,

                Heshbon,

                Max(Taarih) Taarih

        From    MyTbl

        Where    Taarih<DateAdd(Year,-1,GetDate())

                And (Hova>0 Or Zhut>0)

        Group By Bank,

                Snif,

                Heshbon) T3

        On T3.Bank=T1.Bank

        And T3.Snif=T1.Snif

        And T3.Heshbon=T1.Heshbon

Where    T1.Taarih>=DateAdd(Year,-1,GetDate())

        And Not Exists (Select    *

                        From    MyTbl T2

                        Where    T2.Taarih>=DateAdd(Year,-1,GetDate())

                                And T2.Bank=T1.Bank

                                And T2.Snif=T1.Snif

                                And T2.Heshbon=T1.Heshbon

                                And (T2.Hova>0 Or T2.Zhut>0))

קל ללכת לאיבוד אבל ננסה:

  1. Select – מה אנחנו רוצים לקבל (כולל Distinct כדי לקבל כל חשבון פעם אחת למרות שהוא מופיע בסיכום של כל יום).
  2. Where – התאריך שלו בשנה השוטפת, ולא קיימות לו תנועות חובה או זכות במהלך שנה זו (Not Exists..).
  3. Left Join – לכל חשבון כזה יש למצוא את התאריך האחרון (=המקסימלי) לפני השנה השוטפת בו הייתה לו תנועת זכות או חובה.

אפשר לראות שמתבצעות כאן שלוש פניות שונות לטבלה (From, Left Join, Not Exists) שמבטיחות "טחינה" של השרת וביצועים מפוקפקים.

אפשרות חלופית, קצת פחות אינטואיטיבית במישור של תרגום מעברית לקוד SQL, הייתה להשתמש ב-Group By: "נכווץ" את הטבלה לחשבונות שונים, תוך שאנו מחשבים לכל שורה מכווצת כזו את תנועות הזכות והחובה המקסימליות לשנה האחרונה (כדי לפלטר את המקרים שאינם אפס), ואת התאריך האחרון בו הייתה תנועת חובה או זכות קודם לכן:

Select    Bank,

        Snif,

        Heshbon,

        Max(Case When (Hova>0 Or Zhut>0) Then Taarih End) Taarih

From    MyTbl

Group By Bank,

        Snif,

        Heshbon

Having    Max(Taarih)>=DateAdd(Year,-1,GetDate())

        And Max(Case When (Hova>0 Or Zhut>0) Then Taarih End)<DateAdd(Year,-1,GetDate());

לכל צירוף של בנק-סניף-חשבון מוצאים את התאריך האחרון בו הייתה תנועת זכות או חובה,

עבור חשבונות פעילים (שיש לגביהם פרטים במהלך השנה האחרונה),

ושהתאריך האחרון הנ"ל- היה לפני למעלה משנה..

אני משער שמי שלא "טחן" את המשימה קצת קשה לו לרדת לעומקו של הקוד; ולכן אדגיש את מה שהוא העיקר כאן:

במקום לבצע אינטואיטיבית Join + Not Exists, מבצעים Group By ומפלטרים בעזרת Having (כלומר- לפי הסיכומים).

מבחינת קוד- הרבה פחות "כתוב", ומה השיפור בביצועים?

clip_image002

במבט ראשון לא משהו (סדר השאילתות כאן הפוך מזה בו הצגתי אותן): 52% ל-Join & Not Exists מול 48% ל-Group By, אלא ש-

  1. 4% ההפרש הם ביחס ל-50% ולכן מהותית הם 8%, וזה לא מעט.
  2. למערכת יש המלצה על הוספת אינדקס (הטקסט בירוק), והעובדה שיש שימוש ב-Hash Match לא יעיל בביצוע ה-Group By- מצדיקה הוספת אינדקס על העמודה הרלוונטית:
Create NnoClustered Index [Idx_#temp_1] On [MyTbl] (Taarih) INCLUDE 

(Bank,Snif,Heshbon,Hova,Zhut) -המלצת המערכת

Go

 

Create NnoClustered Index [Idx_#temp_2] On [MyTbl] 

(Bank,Snif,Heshbon,Taarih,Hova,Zhut) --Hash Match מניעת

Go

כעת ה-Execution Plan נראה כך:

clip_image004

הפעם הפער משמעותי מאוד, וה-Group By משיג תוצאות מצויינות- כמעט פי שניים טוב יותר מהשני.

אל תתנו למראה עינכם להטעות אתכם- ה-Join & Not Exists נראה כעת הרבה יותר מסובך מכיוון שהמערכת מבצעת Index Seek + Constant Scan (השימוש ב-Included Columns למיטב הבנתי), אולם הוא יעיל משמעותית בזכות האינדקס; אבל השיפור ב-Group By היה הרבה הרבה יותר משמעותי.

 

נעבור לדוגמה אחרת- באחד הדטבייסים יש מספר טבלאות שלכל אחת יש טבלה "תאומה" ששמה זהה אך בצירוף _temp בסוף (נניח- MyTbl ו- MyTbl_temp), וצריך לקבל את רשימת הטבלאות- או מפני שתמיד שוכחים אילו טבלאות כלולות ברשימה, או מפני שהרבה פעולות מתבצעות דינאמית על כולן ויש צורך לפתוח Cursor לשם כך.

הפתרון האינטואיטיבי- כל הטבלאות מ-sys.tables שיש להן מקבילה בעלת סיומת _temp:

Select    name

From    sys.tables T1

Where    Exists (Select *

                From    sys.tables T2

                Where    T2.name=T1.name+'_temp');

הפתרון הפחות אינטואיטיבי המתבסס על Group By:

Select    Case When Right(name,5)='_temp' Then Left(name,Len(name)-5) Else name End

From    sys.tables

Group By Case When Right(name,5)='_temp' Then Left(name,Len(name)-5) Else name End

Having    Max(name)=Min(name)+'_temp';

כלומר- נבצע Group By על שמות הטבלאות ללא ה-_temp שבסוף, ואם ה-Min (הטבלה ללא ה-_temp) בצירוף _temp שווה ל-Max (הטבלה עם ה-_temp) אזי יש להציג אותה.

clip_image006

התוצאות מדברות בעד עצמן- השימוש ב-Group By יעיל כמעט פי ארבעה!

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

 

ולסיום- חיפוש "איים" ברצף מספרים. נתון רצף של מספרים, ואנחנו מחפשים היכן מתחיל ומסתיים כל אי, כאשר אי הוא רצף של מספרים. נניח: 1,5,6,7,8; האיים הם 1 & 5-8.

לבעייה יש מספר וריאציות- לעיתים שולפים רק את המספר הראשון בכל אי ולעיתים את הראשון והאחרון בכל אי (זה מה שאני אעשה); ולפעמים מתייחסים בכלל ל"חורים" שהם המספרים החסרים.

ניצור טבלת מספרים 1-100 עם מספר חורים בגדלים שונים בדרך:

Use tempdb;

go

 

Create Table T(I Int Primary Key);

Go

 

Declare    @I Int;

Set        @I=1;

While @I<=100

    Begin

    If @I Not In (2,3,5,10,11,12,13,59,60,61,62,63,64,65,66,67,68,69,70,72,73,74)

        Insert Into T Select @I;

    Set    @I=@I+1;

    End;

כדי לשלוף את נקודות ההתחלה והסיום של כל חור מקובל להשתמש בשליפה הבאה:

Select    IsNull(T1.I,T2.I) I

From    T T1

Full Outer Join T T2

        On T1.I+1=T2.I

Where    T1.I Is Null

        Or T2.I Is Null;

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

הפלט שנקבל: 1,1,4,4,6,9,14,58,71,71,75,100 (כלומר: 1-1, 4-4, 6-9,..).

פתרון אלטרנטיבי בעזרת Group By יהיה מעט מורכב: פעם אחת נקבץ לפי I/2 ופעם לפי 2/(1+I) ואת שני הסטים נחבר בעזרת Union All. במקרה הראשון כל מספר זוגי ישודך לזה שאחריו (6 ו-7 שווים 3 בחלוקה ל-2), ובמקרה השני – כל מספר אי זוגי לזה שאחריו (7 ו-8 שווים 4 כשמוסיפים להם 1 ומחלקים ב-2). לכל אחד מהסטים נצרף תנאי Count(I)=1 כדי להשאיר את כל הקבוצות שיש בהן רק מספר אחד (כי אין "קודם" או אין "הבא"):

Select    Max(I)

From    T

Group By I/2

Having    Count(I)=1

Union All

Select    Max(I)

From    T    

Group By (I+1)/2

Having    Count(I)=1;

מה פוסק ה-Execution Plan?

image

אופס- ל-Group By ביצועים כה גרועים שכל אחד משני חלקיו פחות טוב מה-Outer Join כולו..

ננסה לאנדקס: לכאורה אין מה שכן לטבלה יש עמודה אחת – I והיא כבר מאונדקסת בתור Primary Key. מה שה-Group By צריך זה אינדקס על ה-I/2 וה-2/(1+I), ואת זה ניתן לעשות או בעזרת הוספת עמודות מחושבות לטבלה או באמצעות Indexed View ואנחנו נלך על האופציה השניה:

Create View dbo.V With SchemaBinding As

Select    I,

        I/2 J,

        (I+1)/2 K

From    dbo.T;

Go

 

Create Unique Clustered Index Idx_V_I On V(I);

Go

 

Create Index Idx_V_J On V(J);

Go

 

Create Index Idx_V_K On V(K);

Go

וכעת ננסח אתה-Group By מחדש תוך שימוש בעמודות המחושבות J,K:

Select    Max(I)

From    V

Group By J

Having    Count(I)=1

Union All

Select    Max(I)

From    V    

Group By K

Having    Count(I)=1;

וה-Execution Plan:

image

כמעט פי שניים יותר טוב!

אפשר כמובן לטעון שנעשה פה תרגיל לא הוגן- הוספתי אינדקסים לשליפת ה-Group By כדי לגרום לה להיות יעילה יותר ומתאימה לתזה, אז מדוע שלא נשפר באופן דומה גם את שליפת ה-Full Outer Join? זהו שלא: שאילתת ה-Join כבר עושה שימוש אופטימלי באינדקס לביצוע Scan ו-Merge Join (שזה ה-Join הכי יעיל) ואין כאן מה לשפר (אלא אם כן נמצא שליפה טובה יותר מזו).

 

לסיכום:

1. לשאילתות הכוללות Join או Exist בין הטבלה לעצמה ניתן לעיתים להציע אטרנטיבות יעילות יותר המתבססות על Group By, וכדאי להיות ערים לאפשרות זו. Group By כולל Scan אחד על הטבלה לעומת שניים במקרה של Join או Exists.

2. כדאי להיות ערים לאינדוקס של הטבלאות מכיוון שהאינדקס הנדרש לביצוע Join או Exist יעיל שונה מזה הנדרש לביצוע Group By יעיל.

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

כתיבת תגובה

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