ריבוי תנאים הפונים לאותו אובייקט
נניח שאנחנו מחפשים עובד שיש לו גם הכשרה ב-SQL Server, גם ב-C# וגם ב-XML,
או לחילופין סכימה בדטבייס שברשימת האובייקטים שלה יש גם פרוצדורות, גם טבלאות, וגם Views.
כמקובל צריך לנסח שליפה ותנאים, אך יש לשים לב שבשתי הדוגמאות הנ"ל כל התנאים פונים לאותה טבלה (טבלת ההכשרות או טבלת האובייקטים) ובדיקה של כל תנאי בנפרד תגרום לשלוש פניות לטבלה וזה קצת מיותר.
נציע מספר פתרונות, ונבחן אותם באמצעות ה-Execution Plan וסיכומי Statistics IO לגבי פעולות הקריאה: לי עצמי לקח זמן רב להבין כיצד להיעזר באמצעים האלו, וגם כיום אינני מבין את הכל. מקווה שזה יעזור למי שנמצא בתחילת הדרך- במקום בו אני הייתי בעבר. הבעייה של מציאת העובדים או הסכימות העונים על התנאים אינם העיקר בפוסט הזה, אלא ההבנה של המידע בכלי ניתוח הביצועים של SQL Server.
לצורך הדוגמה ניצור העתקים של sys.schemas (טבלת הסכימות) ו-sys.objects (טבלת האובייקטים): טבלאות המערכת המקוריות פונות לטבלאות מערכת בסיסיות יותר ולא ניתן לאנדקס אותן, והטבלאות שניצור יהיו טבלאות משתמש רגילות, ונוכל לבדוק את הפתרונות השונים פעם ללא אינדקסים, ופעם עם אינדקסים מלאים:
Use AdventureWorks;
Go
If Object_Id('objects','U') Is Not Null Drop Table objects;
Go
Select *
Into objects
From sys.objects;
Go
If Object_Id('schemas','U') Is Not Null Drop Table schemas;
Go
Select *
Into schemas
From sys.schemas;
Go
Select *
From objects;
Go
Select *
From schemas;
Go
ננסה חמש שליפות שונות – מוצלחות יותר ומוצלחות פחות, ואם פיספסתי רעיון מקורי לעשות זאת אחרת- אשמח לשמוע:
Select * /*1*******************************************************************/
From schemas
Where schema_id In (Select schema_id
From objects
Where type_desc='SQL_STORED_PROCEDURE')
And schema_id In (Select schema_id
From objects
Where type_desc='USER_TABLE')
And schema_id In (Select schema_id
From objects
Where type_desc='VIEW');
Select * /*2*******************************************************************/
From schemas
Where schema_id In (Select schema_id
From objects
Where type_desc In ('SQL_STORED_PROCEDURE','USER_TABLE','VIEW')
Group By schema_id
Having Count(Distinct type_desc)=3);
Select S.name /*3**************************************************************/
From schemas S
Inner Join objects O
On S.schema_id=O.schema_id
Where O.type_desc In ('SQL_STORED_PROCEDURE','USER_TABLE','VIEW')
Group By S.name
Having Count(Distinct O.type_desc)=3;
Select S.schema_id, /*4********************************************************/
Max(S.name) name
From schemas S
Inner Join objects O
On S.schema_id=O.schema_id
Where O.type_desc In ('SQL_STORED_PROCEDURE','USER_TABLE','VIEW')
Group By S.schema_id
Having Count(Distinct O.type_desc)=3;
Select O.schema_id, /*5********************************************************/
Schema_name(O.schema_id) name
From objects O
Where O.type_desc In ('SQL_STORED_PROCEDURE','USER_TABLE','VIEW')
Group By O.schema_id
Having Count(Distinct O.type_desc)=3;
את הריצות הנ"ל ביצעתי פעמיים- פעם ללא אינדקסים ופעם עם (כולל יחסי Foreign Key):
Create Clustered Index Idx_objects On objects(schema_id,type_desc);
Go
Alter Table schemas
Add Constraint PK_schemas
Primary Key Clustered (schema_id);
Go
Alter Table objects
Add Constraint FK_objects_schema_id
Foreign Key(schema_id) References schemas;
Go
מי שירצה לבטל את האינדקסים כדי לחזור למצב הפתיחה:
Drop Index objects.Idx_objects;
Go
Alter Table objects Drop Constraint FK_objects_schema_id;
Go
Alter Table schemas Drop Constraint PK_schemas;
Go
בשני המקרים הפעלתי את Statistics IO:
ניתן להוריד את קבצי ה-Execution Plan ללא אינדקסים ועם אינדקסים,
ואת קבצי ה-Statistics IO ללא אינדקסים ועם אינדקסים.
שאילתה 1- שליפה תמימה שבודקת את שלושת התנאים כפשוטם בנפרד:
ללא אינדקסים זה עלה לנו בשלושה Table Scan – המערכת נאלצה לעבור פעם אחת על טבלת schemas ושלוש פעמים על טבלת objects.
עם אינדקסים- המערכת עוברת פעם אחת על טבלת schemas (במקרה זה אין הבדל בין Table Scan על טבלת ללא Clustered Index ו-Clustered Index Scan כשיש), ושלוש פעמים Seek על טבלת Objects (Seek הוא פניה ממוקדת כשיש אינדקס בניגוד ל-Scan הבזבזני כשאין).
אפשר לראות שללא אינדקסים- היה צורך לבצע פעם אחת מיון במחיר יקר.
ההבדל ב"מחיר" בין שני ה-Execution Plans אינו ירידה מ-20% ל-18% (זו הירידה במחיר ביחס לשליפות האחרות), אלא ירידה מ-0.053 ל-0.027 (את זה ניתן לראות בקובץ המקורי), או אם נעיין ב-Statistics IO:
ללא אינדקסים
Table 'objects'. Scan count 3, logical reads 89, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'schemas'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
עם אינדקסים
Table 'objects'. Scan count 9, logical reads 32, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'schemas'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
בשני המקרים התבצעו logical reads 12 בטבלת schemas,
אבל בראשון התבצעו logical reads 89 בטבלת objects (שלושה Scans מלאים) ובשני רק logical reads 32 (שלושה Seeks ממוקדים).
מדד Scan count מעט מטעה מכיוון שהמערכת מונה בנפרד כל פניה לטבלת objects גם אם מדובר ב-Seek נקודתי.
שאילתה 2- נבדוק קודם ב-objects אילו scema_id מקיימות את שלושת התנאים (בעזרת Group By ו-Count), ואת טבלת schemas נתנה בהם:
בראשון- שני Table Scans מלאים – אחד על כל טבלה, ובהיעדר אינדקסים מיון של objects לפני ה-Group By, ושמירת התוצאות ב-Table Spool (טבלה זמנית שהתהליך יוצר ב-tempdb עם תוצאות ה-Group By ומולו מתבצעת בדיקת התנאי של schemas).
בשני התוכנית נראית יעילה מאוד- צריך לזכור שתמיד המחיר יסתכם ב-100%, אלא שכאן אין פעולות משמעותיות שניתן להימנע מהן כמו Sort, אלא רק Clustered Index Scan על objects לצורך ה-Group By (שהוא מאוד זול כי הטבלה ממויינת הודות ל-Clustered Index) –Clustered Index Seek ממוקד ב-schemas רק עבור schema_id שעונים על התנאי.
בשני המקרים מדובר בשליפה הכי יעילה, אבל ניכר שאת הראשונה ניתן לשפר בהרבה, ואת השניה כנראה שלא.
ללא אינדקסים
Table 'Worktable'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'objects'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'schemas'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
עם אינדקסים
Table 'schemas'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'objects'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
בשני המקרים מספר דומה פחות או יותר של logical reads על טבלת objects.
אין לי הסבר מניח את הדעת מדוע מספר ה- logical readsעל schemas עלה מ-2 ל-4,
אך בכל מקרה "המנה העיקרית" היא ה- logical reads על worktable (ה-Table Spool הנ"ל ב-tempdb).
שאילתה 3- דומה במקצת לקודמת, אבל מתבצע קודם כל Join וה-Group By (לפי name) מתבצע עליו:
ללא אינדקסים מתבצעים שני Table Scans על שתי הטבלאות, ומכיוון שהן אינן ממויינות ולמערכת לא כדאי למיין אותן- מתבצע Hash Match יקר בינהן ורק לאחר מכן מיון לצורך ה-Group By.
עם אינדקסים מתבצעים שני Clustered Index Scans (שכשלעצמם "עולים" בדיוק כמו Table Scans), ה-Merge Join החסכוני הוא בזכות העובדה שכעת שתי הטבלאות ממויינות, אבל לאחר מכן יש לבצע מיון לפי name כי הוא אינו חלק מהאינדקסים והדבר נדרש ל-Group By בהמשך.
האינדקסים שיפרו את השליפה ומחירה המוחלט ירד מ-0.050 ל-0.036, אבל יחסית לשליפות האחרות השיפור לא היה גדול (כפי שהוסבר) ולכן המחיר היחסי עלה מ-18% ל-23%.
ללא אינדקסים
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'objects'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'schemas'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
עם אינדקסים
Table 'objects'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'schemas'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
בסטטיסטיקה ניתן לראות שה- Scan count וה- logical reads בשתי הטבלאות דומה בשני המקרים, ומחירי ה-Joins והמיונים אינם מופיעים כאן כי הסטטיסטיקה מודדת רק את פעולות ה-IO (קריאה וכתיבה לדיסק ולזכרון).
לסיכום- ביצוע ה-Group By לפי עמודת name הלא מאונדקסת הייתה בעוכרינו.
שאילתה 4- ננסה לשפר את השליפה הקודמת בכך שנבצע Group By לפי עמודה schema_id המאנדקסת ולמצוא את (Max(name:
ללא אינדקסים מתבצעים שני Table Scan מלאים על כל טבלה, לאחר מכן Hash Match יקר בין שתיהן כי הן אינן ממויינות ולמערכת לא כדאי למיין אותן לפני ה-Group By, התוצאה נשמרת ב-Table Spool בצד וכעת בשני מסלולים במקביל המערכת מבצעת מיון לפי name מטבלת schemas כדי למצוא את המקסימום עבור כל schema_id (למטה ב-Table Spool) ומיון לפי type_desc כדי לבצע Count Distinct (למעלה עבור הפילטור =3 בהמשך); והתוצאות של שני אלו מותאמות בעזרת Merge Join עם עמודת schema_id.
עם אינדקסים מתבצעים שני Clustered Index Scans על שתי הטבלאות, Merge Join יעיל לנוכח העובדה ששתי הטבלאות ממויינות הודות ל-Clustered Index, אבל גם כאן יש לחזור ל"תרגיל" עם ה-Table Spool וכן למיין מכיוון שגם כאן המערכת צריכה למצוא Max ו-Distinct Count.
כמו בשליפה הקודמת- האינדקסים שיפרו את השליפה ומחירה המוחלט ירד מ-0.085 ל-0.058, אבל יחסית לשליפות האחרות השיפור לא היה גדול (כפי שהוסבר) ולכן המחיר היחסי עלה מ-31% ל-37%.
ללא אינדקסים
Table 'Worktable'. Scan count 2, logical reads 289, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'objects'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'schemas'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
עם אינדקסים
Table 'Worktable'. Scan count 2, logical reads 289, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'objects'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'schemas'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
גם כאן אין הבדלים גדולים בין הסטטיסטיקות (שמודדות רק פעולות IO – קריאה וכתיבה מהדיסק ומהזכרון) ושתיהן כוללות את מחיר חישוב ה- Max וה-Distinct Count (השימוש ב-worktable).
שאילתה 5- במקום לבצע Group By על name (שאילתה 3) או Max על name, ננסה למצוא אותו בעזרת פונקציית המערכת Schema_Name, ואז נוכל לוותר על הפנייה לטבלה schemas:
ללא אינדקסים המערכת מבצעת Table Scan בודד על טבלת objects, וממיינת אותה עבור האגרגציה.
עם אינדקסים המערכת מבצעת Clustered Index Scan ולאחר מכן שתי אגרגציות (Group By לפי schema_id ו-Count Distinct לפי object_name).
ללא אינדקסים
Table 'objects'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
עם אינדקסים
Table 'objects'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
הסטטיסטיקה נראית דומה ואולי אפילו עם יתרון קל לכאורה לטובת הטבלה נטולת האינדקס, אבל יש לזכור שוב שהסטטיסטיקה אינה מחשבת את מחיר המיון.
כדאי לציין בנוסף שלא ברור לי מה מחיר השימוש בפונקציית המערכת Schema_Name: לכאורה מדובר ב-Scan על טבלת sys.schemas כדי למצוא את ה-name של schema_id נתון. בדוגמה זו יש לי פונקציה מן המוכן, אך מה קורה אם לא (למשל- בדוגמה עם העובד שיש לו הכשרה בשלושה תחומים)?
שורה תחתונה- במקרה זה האפשרות הטובה ביותר היא שליפה מספר 5, בכפוף לכך שיש לנו פונקציה שמוצאת את התיאור או שהתיאור אינו נדרש; והיא טובה גם אם יש אינדקסים וגם אם אין.
אם נדרש התיאור ואין פונקציה מתאימה (ובמחיר אפס..) אזי שליפה מספר 2 היא העדיפה- גם אז בשני המקרים.
לבסוף- אינדוקס נכון הוא מפתח חשוב לשיפור ביצועים, ומי שמעיין ב-Execution Plan שינסה לחפש פעולות יקרות מבחינת האחוזים מהסה"כ שניתן להימנע מהן בעזרת אינדקסים מתאימים (Seek במקום Scan, Nested Loops במקום Hash Match או Merge Join במקום שניהם, הימנעות מ-Sorts וכו').
לבסוף- לא לשכוח למחוק את שתי הטבלאות ולבטל את המעקב אחר הסטטיסטיקה:
Set Statistics IO Off;
Go
If Object_Id('objects','U') Is Not Null Drop Table objects;
Go
If Object_Id('schemas','U') Is Not Null Drop Table schemas;
Go