האופרטור In / Not In: חלופות, וביצועים

31/10/2012

תגיות: , , , , , ,
אין תגובות

כשהייתי בראשית דרכי ב-SQL Server, שמעתי באחד הכנסים ש"בדקו ומצאו" שהשימוש ב-Inner Join מעט יותר יעיל מאשר השימוש ב-In. אינני זוכר בוודאות ממי שמעתי את זה, אך עובדה שבמשך שנים התייחסתי לכך כאל תורה מסיני, ואף ציטטתי זאת בידענות פה ושם.
לאחרונה כשנזכרתי בכך, קמטתי את מצחי ותהיתי האמנם.. דווקא הנסיון והידע שצברתי מאז הובילו אותי לחשוב כיום שאולי דווקא In יעיל יותר: בניגוד ל-Join בו לכל שורה מצד ה-From יש למצוא את כל ההתאמות בצד ה-Join, ב-In יש למצוא רק את ההתאמה הראשונה.. ואם כבר- אז מה עם השימוש ב-Exists? האם מדובר רק ב-In משופר או אולי לא רק?
ואם כבר- מה קורה במקרה ההפוך של Not In?
בקיצור- צריך לבדוק!
יצרתי שלושה העתקים של טבלת sys.Objects (קישור לכל הקודים שבפוסט):
1. Heap
2. Index
3. Primary Key Clustered Index
יצרתי חמישה העתקים של טבלת sys.tables:
1. Heap
2. Index
3. Clustred Index
4. Primary Key Clustered Index
כעת ביצעתי 3*4=12 בדיקות של ה-Execution Plans של טבלת Objects מול טבלת Tables, פעם ב-In, פעם ב-Join ופעם ב-Exists:

Select *

From   T_Objects_

Where  object_id In (Select object_id

                    From    T_Tables_);

 

Select O.*

From   T_Objects_ O

Inner Join T_Tables_ T

       On O.object_id=T.object_id;

 

Select *

From   T_Objects_ O

Where  Exists (Select 1

              From    T_Tables_ T

              Where   T.object_id=O.object_id);

בשני המקרים אני מחפש שורות מ-Objects שנמצאות גם ב-Tables,

ולהלן תוצאות האמת (כולל קולות החיילים):

clip_image002

ברוב המקרים ה-Execution Plans זהים וגם הביצועים, למעט כאשר יש ל-Objects אינדקס פשוט.

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

יחד עם זאת, הודות לסטטיסטיקות, המערכת יכולה לדעת שהערכים בכל אחת מהטבלאות הם יחודיים פחות או יותר, גם כשאין אינדקסים שמבטיחים זאת (Primary Key), ויוצרת Execution Plans דומים.

להמחשה, נתבונן בסטטיסטיקות של Tables_1 ו-Objects_A (שתיהן Heaps):

DBCC Show_Statistics(T_Tables_1,'object_id') With Histogram;

DBCC Show_Statistics(T_Objects_A,'object_id') With Histogram;

clip_image004

ניתן לראות בעמודה הימנית (Avg_Range_Rows) שכל ערך Object_ID מופיע פעם אחת.

ההבדל באותם מקרים בהם היחס הינו 28:45:28 או 33:35:33 הוא שהמערכת מעדיפה לבצע Group By או Sort על טבלת Tables במקרים של In ושל Exists כדי להבטיח שהערכים יהיו יחודיים וממויינים, דבר שלא תוכל לעשות במקרה של Inner Join בו יש למצוא את כל ההתאמות ולא רק את הראשונה.

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

מאי נפקא מינה מקרה זה? במה הוא שונה ממקרים אחרים?

אם בטבלת Tables הערכים לא יהיו יחודיים התוצאות של Inner Join יהיו גרועות יותר מכיוון שהמערכת תצטרך לחפש את כל ההתאמות ולא תוכל להעריך שתהיה רק אחת. לכך יש להוסיף מקרים בהם המשתמש יבצע Select * מיותר במקרה של Join ואז השליפה של Tables תהיה מסורבלת יותר.

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

גם במקרה ההפוך בו נרצה למצוא אילו שורות מ-Objects אינן ב-Tables התוצאות יהיו דומות, כאשר השאילתות יהיו כדלקמן:

Select *

From   T_Objects_

Where  object_id Not In (Select object_id

                        From    T_Tables_);

 

Select O.*

From   T_Objects_ O

Left Join T_Tables_ T

       On O.object_id=T.object_id

Where  T.object_id Is Null;

 

Select *

From   T_Objects_ O

Where  Not Exists (Select 1

                  From    T_Tables_ T

                  Where   T.object_id=O.object_id);

clip_image006

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

עד כאן ניתן לצאת עם כותרת בסגנון של Myth Busters ולהזים את השמועות.

היכן ה-Catch? כדאי לשים לב שאת הטבלאות יצרנו על ידי פקודות Select Into, ובכולן עמודת המפתח Object_ID הוגדרה כ-Not Null (מפתח חייב להיות Not Null אך לא בכל הטבלאות הוא הוגדר ככזה). אם נשנה את ההגדרה ל-Null היכן שאפשר, תהיה לכך השפעה דרמטית על הביצועים והתוצאות.

הקוד מופיע בסקיפט המצורף, ויש רק להדגיש שבטבלאות T_Objects_C & T_Tables_4 בהן הוגדרו Primary Key לא ניתן לשנות את העמודות ל-Null ולכן השארתי אותן על כנן.

בתוצאות במקרה של In לא חלו שינויים דרמטיים (שינויים קוסמטיים באחוזים):

clip_image008

אך שורו הביטו וראו מה קורה במקרה של Not In:

clip_image010

הביצועים של In בכל הגזרות (למעט כאשר בשני הצדדים יש עמודות מסוג Not Null) מתדרדרות פלאים, וה-Execution Plan הופך להזוי; למשל במקרה בו שתי הטבלאות הן Heap (ללא אינדקסים):

clip_image012

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

ה-Table Scan האדום הוא היקר ביותר (32%) והוא משתמש ל-Nested Loops מול Objects שבסופו כל השורות מ-Objects שאין בהן ערך Null עוברות הלאה (כרגע העמודה מוגדרת כ-Null אך בפועל אין בה ערך כזה). לא ברור לי מדוע מתבצע Table Scan ב-Tables, וזה כך גם כש-Tables ריק.

ה-Table Scan הכחול סופר את השורות מ-Tables בהן יש Null (כנ"ל לגבי ה-Null), ושומר את התוצאה בטבלה זמנית, ומבצע Nested Loops עם הפלט של האדום. כלומר- כל שורה מהפלט של האדום מושווית מול התוצאה באותה טבלה זמנית, ורק אם הסכום הוא 0 – היא ממשיכה הלאה.

ה-Table Scan הירוק כל שורה מ-Objects (זה שליד האדום) שצלחה את שני הקודמים (כלומר- היא עצמה אינה Null וב-Tables אין אף Null) נבדקת סופסוף מול Tables ואם אינה שם (הפעם בלי "להתקטנן" על כך שערך קיים אינו שונה מ-Null) היא עוברת הלאה.

כדי להבין מדוע יש טיפול נפרד בערכים וב-Nulls נהפוך בכל טבלה שורה אחת ל-Object_ID=Null (הקוד בסקריפט המצורף), והפעם לא נבדוק את הביצועים אלא את הפלט – כמה שורות חוזרות:

clip_image014

כעת כבר לא מדובר בענייני ביצועים (דבר שלפעמים הוא זניח כשמדובר בשרת חזק ובטבלאות לא גדולות) אלא בפלט המוחזר: מסתבר ש-Not In אינו מחזיר שורות כשיש Null, ולשיטתו Null גם אינו שווה וגם אינו שונה מערך כלשהו, ולכן לא חוזר דבר. במילים אחרות- כל הערכים מ-Objects מושווים מול Null שב-Tables, אף אחד מהם אינו שונה ממנו, ולכן אף ערך לא מוחזר. לוגית זה נכון מפני ש-Null אינו ערך, אך מעשית לא תמיד לכך אנחנו מצפים..

דווקא Left Join ו-Not Exists מחזירים פלט "הגיוני" המתיישב בדרך כלל עם האינטואיציה שלנו (אינטואיציה האומרת ש-Null "שונה" מ-2 למשל..).

סיכום (הפעם סיכום מלא וסופי):

1. In ו-Exists יעילים יותר מ-Join מכיוון שהם מוצאים את ההתאמה הראשונה ולא את כל ההתאמות (בתנאי שמדובר בעמודות המוגדרות כ-Not Null).

2. Not In הוא מאוד לא יעיל כשמדובר בעמודות המוגדרות כ-Null.

3. Not In מחזיר תוצאות לא צפויות כשמדובר בעמודות המוגדרות כ-Null (נכונות לוגית אך לא אינטואיטיבית).

כתוצאה מכל זה- מומלץ להשתמש ב-Exists שבכל הנסיבות הוא יעיל יותר (או לא פחות) מהחלופות ותוצאותיו אינטואיטיביות; כל זאת בצד יכולות משופרות שאין ל-In. יחד עם זאת, הסינטקס שלו מורכב יחסית לזה של In, ומפתחים מתחילים עלולים להסתבך מעט בשימוש בו.

הקודים השונים שנעשה בהם כאן שימוש

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

כתיבת תגובה

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