שיפור ביצועי Join עצמי

22/08/2014

אין תגובות

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

  • בטבלת נוכחות נרצה להתאים את ההגעה ליציאה
  • בטבלת התחברות לאתר נרצה להתאים את ה-logins ל-logouts
  • בטבלת המכירות נרצה להתאים את המכירות של כל סוכן למכירות בחודש הקודם או בחודש המקביל אשתקד לצורך השוואה
  • בטבלת שערי מניות נרצה להתאים את השער של כל יום לזה הקודם כדי לחשב את השינוי באחוזים

לפני כמה שנים החלטתי לשפר מעט את הספרדית שלי – אותה אני מכיר ברמה בסיסית כדי לדבר עם הדודים והדודות מארגנטינה (משם באו הורין שלי), ובעיקר בתחום עיסוקי המקצועי (הרי מלדבר עם הדודה לא לומדים איך אומרים בספרדית בסיס נתונים או שרת או טבלה). בין המקורות שסייעו לי היו ה-BOL שלכל דף שלו יש גרסאות מקבילות באנגלית ובעוד מספר שפות (כולל ספרדית ולא כולל עברית), וגם טבלת הודעות השגיאות sys.messages שבה כל הודעת שגיאה מופיעה במספר שפות.
המשימה איפוא היא לבנות שליפה מהעתק של sys.messages כך שליד כל הודעה באנגלית יופיע התרגום שלה לספרדית (בפועל יש מספר קטן של הודעות באנגלית שאין להן תרגום לספרדית ואותן נסנן).
נתחיל מיצירת הטבלה:

Use tempdb;

Go

 

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

Go

 

Select    *

Into    T_Messages

From    sys.messages;

הפתרון הראשון שעלה בדעתי היה ליצור Join עצמי בין ההודעות באנגלית להודעות בספרדית.
לשם כך יש צורך באינדקס שהעמודה הראשית שלו היא קוד השפה (language_id כאשר אנגלית=1033 וספרדית=3082) עבור שליפת שני הסטים, והעמודה המשנית היא קוד השפה (message_id) עבור ההתאמה בין שניהם:

Create Index Idx_T_Messages_language_id_message_id On T_Messages(language_id,message_id) Include(text);

 

Select    M1.text English,

        M2.text Español

From    T_Messages M1

Inner Join T_Messages M2

        On M1.message_id=M2.message_id

Where    M1.language_id=1033

        And M2.language_id=3082;

image

הפתרון השני הוא לבצע Group By message_id כך שלכל מספר הודעה תהיה שורה אחת, ובעמודה אחת להציג את ההודעה באנגלית ובשניה בספרדית (ולסנן את ההודעות שלפחות אחת מהן היא Null). ניצור אינדקס לפי message_id ונראה אם המערכת תשתמש בה עבור ה-Group By:

Create Index Idx_T_Messages_message_id_language_id On T_Messages(message_id,language_id) Include(text);

 

Select    Max(Iif(language_id=1033,text,Null)) English,

        Max(Iif(language_id=3082,text,Null)) Español

From    T_Messagesחה 

Where    language_id In (1033,3082)

Group By message_id

Having    Max(Iif(language_id=1033,text,Null))+Max(Iif(language_id=3082,text,Null)) Is Not Null;

image

כפי שאפשר לראות – המערכת לא השתמשה באינדקס החדש אלא בקודם, ונאלצה לשלם על כך במיון יקר לפי message_id עבור ה-Group By.

הפתרון השלישי הוא להשתמש בפונקציית החלון Lag כדי למצוא לכל הודעה בספרדית את הקודמת לה באנגלית (בהנחה שהמיון הוא לפי message_id ו-language_id):

With T As

(Select    Lag(text,1) Over(Partition By message_id Order By language_id) English,

        text Español

From    T_Messages

Where    language_id In (1033,3082))

Select    *

From    T

Where    English Is Not Null;

image

גם במקרה זה המערכת העדיפה את האינדקס הראשון למרות המיון היקר הכרוך בו.

נשווה כעת בין שלוש השליפות:

Select    M1.text English,

        M2.text Español

From    T_Messages M1

Inner Join T_Messages M2

        On M1.message_id=M2.message_id

Where    M1.language_id=1033

        And M2.language_id=3082;

 

 

Select    Max(Iif(language_id=1033,text,Null)) English,

        Max(Iif(language_id=3082,text,Null)) Español

From    T_Messages

Where    language_id In (1033,3082)

Group By message_id

Having    Max(Iif(language_id=1033,text,Null))+Max(Iif(language_id=3082,text,Null)) Is Not Null;

 

With T As

(Select    Lag(text,1) Over(Partition By message_id Order By language_id) English,

        text Español

From    T_Messages

Where    language_id In (1033,3082))

Select    *

From    T

Where    English Is Not Null;

image

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

שימו לב שבשליפה הראשונה מתבצע בין שני הסטים Nested Loops שזה אומר שהמערכת לוקחת ערך מהסט הראשון ומחפשת לו התאמות בסט השני (בעזרת האינדקס כמובן). מדוע אינה מבצעת Merge Join שזו אופציה הרבה יותר יעילה שניתנת למימוש במקרה זה בו שני הסטים ממויינים לפי message_id וניתן להתאים בקלות שורה מפה לשורה משם לפי הסדר?
מסתבר שהייתה לי טעות “קטנה”: שכחתי לציין באינדקסים שיצרתי שהם Unique. המידע הזה חשוב ומאפשר לייעל פעולות שונות וזו דוגמה לכך. המערכת לא “ידעה” שהם unique ולכן הניחה שעל כל ערך בסט העליון עלולים להיות כמה בשני ויש למצוא את כולם (ב-Merge Join זה לא ניתן). נתקן איפוא את שני האינדקסים ונריץ שוב את השליפות (השליפות לא השתנו):

Drop Index T_Messages.Idx_T_Messages_language_id_message_id;

Create Unique Index Idx_T_Messages_language_id_message_id On T_Messages(language_id,message_id) Include(text);

Drop Index T_Messages.Idx_T_Messages_message_id_language_id;

Create Unique Index Idx_T_Messages_message_id_language_id On T_Messages(message_id,language_id) Include(text);

image

גבירותי ורבותי מהפך: כעת מתבצע Merge Join בראשון (עם ה-Join העצמי) והוא הרבה יותר יעיל משתי האחרות.
אין לנו אלא לתת את דעתנו לשתיהן ולנסות למצוא דרך להיפטר מה-Sort. הסיבה שהוא קיים היא כי המערכת צריכה לבחור בין אינדקס שיאפשר לפלטר בקלות ויעלה במיון יקר, או אינדקס שיחזיר סט ממויין לפי message_id ויעלה ב-Full Index Scan. בדיוק בנושא הזה עסקתי בפוסט קודם שלי, ושם הראתי שניתן לפתור את הבעייה במקרים מסויימים בעזרת Filtered Index. אם חושבים על זה- זה די הגיוני: הרי אנחנו צריכים רק את ההודעות באנגלית ובספרדית.
ניצור filtered index:

Create Unique Index Idx_T_Messages_message_id_language_id_2 On T_Messages(message_id,language_id) Include(text) Where language_id In (1033,3082);

image

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

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

כתיבת תגובה

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