הנה מקור לטעויות: מנסים לעדכן טבלה אחת על פי השניה, בשעה שלכל שורה בראשונה יש מספר שורות מתאימות בשניה.
עוד לפני שנראה מה קורה- ברור שלא זה הדרך: בין אם הפקודה תעבוד ובין אם לא- כיצד נדע איך הטבלה הראשונה תתעדכן?
לאחר שנראה מה קורה נבין שכל שורה תתעדכן פעם אחת באופן אקראי, וכך לא עובדים.
דוגמה:
Use tempdb;
Go
If Object_Id('MyTbl1','U') Is Not Null Drop Table MyTbl1;
Go
Create Table MyTbl1(ID Int,
Txt Varchar(50));
Go
Insert
Into MyTbl1(ID)
Select 1 Union All
Select 2 Union All
Select 3;
If Object_Id('MyTbl2','U') Is Not Null Drop Table MyTbl2;
Go
Create Table MyTbl2(ID Int,
Txt Varchar(50) Not Null);
Go
Insert
Into MyTbl2(ID,Txt)
Select 1,'a' Union All
Select 1,'b' Union All
Select 1,'c' Union All
Select 2,'aa' Union All
Select 2,'bb' Union All
Select 2,'cc' Union All
Select 3,'aaa' Union All
Select 3,'bbb' Union All
Select 3,'ccc';
Select * From MyTbl1;
Select * From MyTbl2;
כפי שניתן לראות- לכל ID מהטבלה הראשונה יש שלוש שורות בטבלה השניה, וכעת ננסה לעדכן:
Update MyTbl1
Set Txt=MyTbl2.Txt
From MyTbl1
Inner Join MyTbl2
On MyTbl1.ID=MyTbl2.ID;
Select *
From MyTbl1;
אולי - חולף בנו הרהור - כל שורה התעדכנה שלוש פעמים, וזה הערך האחרון שנשאר?
ננסה אם כך לצבור בכל שורה בטבלה הראשונה את הערכים שהוכנסו לתוכה-
נאפס את עמודה Txt בטבלה הראשונה ונבדוק:
Update MyTbl1
Set Txt=Null;
Update MyTbl1
Set Txt=IsNull(MyTbl1.Txt,'')+MyTbl2.Txt
From MyTbl1
Inner Join MyTbl2
On MyTbl1.ID=MyTbl2.ID;
Select *
From MyTbl1;
אין כל שינוי.
מי שטיפה יתעמק- יוכל לראות שכל שורה קיבלה באופן מעשי את הערך הראשון שהגיע אליה, אבל פורמלית מדובר בבחירה אקראית של המערכת.
מי שעדיין יש לו ספק- יוכל לעיין ב-Execution Plan:
המערכת מבצעת אגרגציה ומחשבת Top לכל ID בטבלה השניה מכיוון שהיא מזהה שיש ריבוי של שורות לכל ID.
האם זה נכון? מצד אחד- התהליך לא נופל ומקבלים תוצאה שהיא לא יותר נכונה ולא פחות נכונה מהתוצאות האפשריות האחרות, מצד שני- אם יש כמה תוצאות אפשריות אזי כולן לא נכונות..
גרסת SQL Server 2012 מביאה לא מעט חידושים משמחים, ואחד מהם הוא הפונקציה Concat שמשרשרת מספר מחרוזות למחרוזת אחת, בדומה לשימוש באופרטור + לשירשור, אך באופן ידידותי יותר: אין צורך להפוך מספרים ותאריכים לטקסטים בעזרת Cast וגם לא להמיר ערכי Null למחרוזות ריקות פן יהפכו את השירשור כולו ל-Null (מקווה שהבעיות שציינתי שנפתרו- מוכרות).
בהחלט שווה!
דא עקא שפתרו הרבה בעיות קיימות, אבל הכניסו בדלת האחורית בעייה חדשה שלא הייתה קיימת עד כה.
נניח שאנחנו רוצים לקבל מדטבייס ReportServer רשימת טבלאות עם שם הסכימה משורשרת אליהן כמקובל, ופונים לשם כך לטבלת המערכת INFORMATION_SCHEMA.TABLES (הדטבייס אמור להיות קיים אצל כל מי שהתקין התקנה סטנדרטית, וטבלת המערכת שציינתי היא אחת מכמה אופציות שיש). את כל זה אנחנו עושים מדטבייס אחר, נניח tempdb.
בשיטה הישנה היינו נוהגים כך:
Use tempdb;
Go
Select TABLE_SCHEMA+'.'+TABLE_NAME [Table]
From ReportServer.INFORMATION_SCHEMA.TABLES;
אין בעיות אך גם אין סומכים על הנס, ולכן נשתמש כעת ב-Concat:
Use tempdb;
Go
Select Concat(TABLE_SCHEMA,'.',TABLE_NAME) [Table]
From ReportServer.INFORMATION_SCHEMA.TABLES;
עכשיו כן יש בעיות: בגלל שלשני הדטבייסים Collations שונים- המערכת אינה יכולה לשרשר את שמות העמודות שנלקחו מ-ReportServer שלו Collation אחד עם תו נקודה ‘.’ שנלקח מדטבייס tempdb שלו Collation שונה..
קצת מצער לגלות שהפונקציה הזו סלחנית לערכי Null או למספרים המשורשרים לטקסטים, אבל בעיית collation שהוחלקה באלגנטיות בעבר – פתאום הופכת לאבן נגף.
הערה – אני מעריך שהרוב יתקלו בבעייה, בהנחה שה-Collations בשני הדטבייסים הנ”ל שונים.
לפני כארבע שנים, אב”ג פרסם בטור שלו את חידת המשולשים (בסוף המאמר), חידה שכדוגמתה ניתן למצוא באינטרנט או בספרי חידות: צורה גיאומטרית המחולקת למשולשים קטנים שמהם נוצרים משולשים גדולים יותר, וצריך למצוא כמה משולשים יש בסה”כ. בדרך כלל מסתבכים, סופרים חלק מהמשולשים פעמיים, משולשים אחרים מפספסים, קשה לסמן את המשולשים, והשמחה רבה..
הפעם נפתור את הבעייה שאב”ג הציג: היא לא המסובכת מכולם, אבל אנחנו הרי בעסקי SQL..
ניתן שמות לנקודות השונות בציור, וניצור שלוש טבלאות):
טבלת נקודות
טבלת קווים (השם של כל קו יווצר מהנקודה הראשונה והאחרונה שלו)
טבלת קווים-נקודות (יחס רבים לרבים – N:N – בין שתי הנ”ל).
אפשר להסתפק בטבלה האחרונה, ולקבל במקרה הצורך את הקווים או הנקודות על ידי שליפת Distinct, אבל אנחנו נהיה מסודרים- מפתח ראשי לכל טבלה, ויחס 1:N בין שתי הטבלאות הראשונות לטבלת הקשר:
If Object_ID('T_Nekudot') Is Not Null Drop Table T_Nekudot;
Go
Create Table T_Nekudot(Nekuda Char(1) Primary Key);
Go
Insert
Into T_Nekudot
Select 'A' Union All
Select 'B' Union All
Select 'C' Union All
Select 'D' Union All
Select 'E' Union All
Select 'F' Union All
Select 'G' Union All
Select 'H' Union All
Select 'I' Union All
Select 'J' Union All
Select 'K' Union All
Select 'L';
If Object_ID('T_Kavim') Is Not Null Drop Table T_Kavim;
Go
Create Table T_Kavim(Kav Char(2) Primary Key);
Go
Insert
Into T_Kavim
Select 'AK' Union All
Select 'CE' Union All
Select 'FH' Union All
Select 'IK' Union All
Select 'AL' Union All
Select 'BF' Union All
Select 'EI' Union All
Select 'HL' Union All
Select 'BC' Union All
Select 'EF' Union All
Select 'HI' Union All
Select 'KL';
If Object_ID('T_KavimNekudot') Is Not Null Drop Table T_KavimNekudot;
Go
Create Table T_KavimNekudot(Kav Char(2) Not Null,
Nekuda Char(1) Not Null);
Alter Table T_KavimNekudot Add Constraint PK_T_Nekudot Primary Key Clustered (Kav,Nekuda);
Go
Alter Table T_KavimNekudot
Add Foreign Key (Kav) References T_Kavim(Kav);
Go
Alter Table T_KavimNekudot
Add Foreign Key (Nekuda) References T_Nekudot(Nekuda);
Go
Insert
Into T_KavimNekudot
Select 'AK','A' Union All
Select 'AK','B' Union All
Select 'AK','E' Union All
Select 'AK','H' Union All
Select 'AK','K' Union All
Select 'CE','E' Union All
Select 'CE','D' Union All
Select 'CE','C' Union All
Select 'FH','H' Union All
Select 'FH','G' Union All
Select 'FH','F' Union All
Select 'IK','K' Union All
Select 'IK','J' Union All
Select 'IK','I' Union All
Select 'AL','A' Union All
Select 'AL','C' Union All
Select 'AL','F' Union All
Select 'AL','I' Union All
Select 'AL','L' Union All
Select 'BF','B' Union All
Select 'BF','D' Union All
Select 'BF','F' Union All
Select 'EI','E' Union All
Select 'EI','G' Union All
Select 'EI','I' Union All
Select 'HL','H' Union All
Select 'HL','J' Union All
Select 'HL','L' Union All
Select 'BC','B' Union All
Select 'BC','C' Union All
Select 'EF','E' Union All
Select 'EF','F' Union All
Select 'HI','H' Union All
Select 'HI','I' Union All
Select 'KL','K' Union All
Select 'KL','L';
דרך ראשונה תחפש שלשות של קווים שיוצרים משולשים ןתהיה בעלת אופי גיאומטרי יותר:
Select *
From T_Kavim T1
Inner Join T_Kavim T2
On T1.Kav>T2.Kav
Inner Join T_Kavim T3
On T2.Kav>T3.Kav
Where Exists (Select 1
From T_KavimNekudot K1
Inner Join T_KavimNekudot K2
On K1.Nekuda=K2.Nekuda
Where K1.Kav=T1.Kav
And K2.Kav=T2.Kav)
And Exists (Select 1
From T_KavimNekudot K2
Inner Join T_KavimNekudot K3
On K2.Nekuda=K3.Nekuda
Where K2.Kav=T2.Kav
And K3.Kav=T3.Kav)
And Exists (Select 1
From T_KavimNekudot K1
Inner Join T_KavimNekudot K3
On K1.Nekuda=K3.Nekuda
Where K1.Kav=T1.Kav
And K3.Kav=T3.Kav)
And Not Exists (Select Nekuda
From T_KavimNekudot
Where Kav In (T1.Kav,T2.Kav,T3.Kav)
Group By Nekuda
Having Count(Kav)=3)
Order By 1,2,3;
ניצור Join עצמי כפול של טבלת הקווים כדי לקבל את כל הצירופים של שלושה קווים שונים,
נבדוק אם קיימת נקודה שנמצאת גם על הראשון וגם על השני,
נבדוק אם קיימת נקודה שנמצאת גם על השני וגם על השלישי,
נבדוק אם קיימת נקודה שנמצאת גם על השלישי וגם על הראשון,
ונוודא שלא מדובר בשלושת המקרים באותה נקודה (במקרה כזה לא נוצר משולש).
בסה”כ 38 משולשים (אם מישהו ניסה לפתור ידנית..).
הדרך השנייה תהיה לחפש שלשות של נקודות שנמצאות על שלושה קווים שונים, ומבחינה טכנית- קיימים שלושה קווים שעל כל אחד שתי נקודות מהשלוש. פתרון זה יותר טכני וגם הרבה יותר יעיל:
Select *
From T_Nekudot T1
Inner Join T_Nekudot T2
On T1.Nekuda>T2.Nekuda
Inner Join T_Nekudot T3
On T2.Nekuda>T3.Nekuda
Where Exists (Select Count(1)
From (Select Kav
From T_KavimNekudot KN1
Where Nekuda In (T1.Nekuda,T2.Nekuda,T3.Nekuda)
Group By Kav
Having Count(Nekuda)=2) T
Having Count(1)=3)
Order By 1,2,3;
התנאי קצת מסובך: יש לחפש קווים ששתיים משלוש הנקודות נמצאות עליהם (השאילתה הפנימית בתנאי),
ושיש שלושה כאלו (השאילתה החיצונית).
גם כאן יש בסה”כ 38 משולשים, רק שכאן מקבלים את הקודקודים שלהם ולא את הצלעות כמו קודם.
הדרך השלישית תשתמש בטכנולוגיה שונה לחלוטין- Spatial – שתחסוך מאיתנו את הצורך “ללכלך את הידיים” בגאומטריה ותעשה זאת בעצמה בעזרת המתודות המובנות בה. הפעם ניצור רק טבלה אחת ונכניס לתוכה את 12 הקווים שבבעייה – כל אחד עם הנקודות לאורכו.
לשם כך נציב את המשולש כולו במערכת צירים כדי שנוכל לתת את הקואורדינטות של כל הנקודות. לא חייבים לדייק “על הקשקש” ואפשר למפות בקירוב:
If Object_ID('T_Spatial') Is Not Null Drop Table T_Spatial;
Go
Create Table T_Spatial(ID Int Identity,
Kav geometry);
Go
Insert
Into T_Spatial(Kav)
Select geometry::STGeomFromText('LineString (3 6,5 6)', 0) Union All
Select geometry::STGeomFromText('LineString (2 4,6 4)', 0) Union All
Select geometry::STGeomFromText('LineString (1 2,7 2)', 0) Union All
Select geometry::STGeomFromText('LineString (0 0,8 0)', 0) Union All
Select geometry::STGeomFromText('LineString (0 0,4 1,7 2)', 0) Union All
Select geometry::STGeomFromText('LineString (1 2,4 3,6 4)', 0) Union All
Select geometry::STGeomFromText('LineString (2 4,4 5,5 6)', 0) Union All
Select geometry::STGeomFromText('LineString (0 0,1 2,2 4,3 6,4 7)', 0) Union All
Select geometry::STGeomFromText('LineString (4 7,5 6,6 4,7 2,8 0)', 0) Union All
Select geometry::STGeomFromText('LineString (3 6,4 5,6 4)', 0) Union All
Select geometry::STGeomFromText('LineString (2 4,4 3,7 2)', 0) Union All
Select geometry::STGeomFromText('LineString (1 2,4 1,8 0)', 0);
Select ID,
Kav.ToString()
From T_Spatial;
למשל- שורה מספר 8 מייצגת את השוק השמאלית של המשולש כולו החל ממפגש הצירים 0 0 וכלה בקודקוד העליון 7 4 דרך שלוש נקודות נוספות..
ניעזר במתודה STIntersects כדי לבדוק בשלשות הקווים שניצור אם הם נחתכים זה עם זה,
ובמתודה STIntersection לוודא שהם לא נחתכים באותה נקודה (קיימות לפחות שתי נקודות חיתוך שונות):
Select T1.Kav.STIntersection(T2.Kav).ToString(),
T2.Kav.STIntersection(T3.Kav).ToString(),
T3.Kav.STIntersection(T1.Kav).ToString()
From T_Spatial T1
Inner Join T_Spatial T2
On T1.ID>T2.ID
Inner Join T_Spatial T3
On T2.ID>T3.ID
Where T1.Kav.STIntersects(T2.Kav)=1
And T2.Kav.STIntersects(T3.Kav)=1
And T3.Kav.STIntersects(T1.Kav)=1
And T1.Kav.STIntersection(T2.Kav).ToString()<>T2.Kav.STIntersection(T3.Kav).ToString();
גם כאן 38 פתרונות, והפעם אנחנו מקבלים אותם כקואורדינטות.
לפי ה-Execution Plan – פתרון זה הרבה פחות יעיל מהשני.
מהפתרון של אב”ג עולה שהייתה אי הבנה טכנית- בחידה המקורית קו BC לא היה אמור להופיע.. הפתרון של אב”ג – 34 – הוא ללא BC; עם BC נוצרים עוד 4 משולשים (אלו שהוא חלק מהם) ובסה”כ 38.
פרוצדורות וסקריפטים אחרים כוללים אופציה לבצע אותם בזהות אחרת בעזרת אופציית Execute As, בעיקר בשל בעיית הרשאות. ננסה לראות איך זה נראה "מבפנים"..
ניצור Login ולו User עם הרשאות חלקיות, בשעה שאנחנו (כלומר- אני, זה שמריץ את הקודים עם ה-Login וה-User שהוגדרו לי) עם הרשאות sa בשרת ו-dbo בדטבייס בו נעבוד (tempdb),
ולמי שלא זוכר- Login הוא אובייקט ברמת השרת ואף הדומיין כולו, ו-User הוא אובייקט ברמת הדטבייס שמייצג בדרך כלל את ה-Login:
Use tempdb;
Go
Create Login MyLogin With Password='MyLogin',
Default_Database=tempdb,Check_Policy=Off;
Go
Create User MyUser For Login MyLogin With Default_Schema=MySchema;
Go
Exec SP_AddRoleMember 'DB_DDLAdmin', 'MyUser'
Go
Create Schema MySchema Authorization MyUser;
Go
הסבר: ה-Login הוא MyLogin, ה-User שלו ב-tempdb הוא MyUser;
ניצור בנוסף סכימה בשם MySchema בדטבייס tempdb,
ו-MyUser יהיה ה-Owner שלה והיא תהיה ברירת המחדל שלו.
לבסוף- ניתן ל-MyUser הרשאות מתאימות ליצור פרוצדורות, ובהמשך נוסיף לו עוד על פי הצורך (לא להתלהב- בשלב זה יש מעט מאוד דברים שהוא יכול לעשות או לראות בדטבייס..).
כעת ניצור טבלה מבלי לתת לו הרשאות:
If Object_ID('T','U') Is Not Null Drop Table T;
Go
Create Table T(I Int);
Go
Execute As User='MyUser';
Select * From T;
Revert;
הטבלה נוצרה, אבל ל-MyUser אין הרשאות לצפות בה (אין בה נתונים אבל זה לא משנה).
השתמשתי ב-Execute As כדי לבצע את הפקודה עם ההרשאות שלו, וחזרתי לעצמי בעזרת Revert.
לא ניתן ל-MyUser הרשאות באופן ישיר, אך ננחם אותו בשלוש פרוצדורות שייגשו בעצמן לטבלה וניתן לו הרשאות להריצן:
-----------------------------------------------------
If Object_ID('P_T1','P') Is Not Null Drop Proc P_T1;
Go
Create Proc P_T1 As
Select * From T;
Go
Grant Exec On P_T1 To MyUser;
Go
-----------------------------------------------------
If Object_ID('P_T2','P') Is Not Null Drop Proc P_T2;
Go
Create Proc P_T2 As
Exec('Select * From T;')
Go
Grant Exec On P_T2 To MyUser;
Go
-----------------------------------------------------
If Object_ID('P_T3','P') Is Not Null Drop Proc P_T3;
Go
Create Proc P_T3 With Execute As Owner As
Exec('Select * From T;')
Go
Grant Exec On P_T3 To MyUser;
Go
-----------------------------------------------------
Exec dbo.P_T1;
Exec dbo.P_T2;
Exec dbo.P_T3;
שלוש הפרוצדורות תקינות ומתבצעות בהצלחה עם ההרשאות שלי כ-sa (הגם שהן מחזירות סטים ריקים כי אין נתונים בטבלה).
כעת נריץ אותן עם ההרשאות של MyUser:
Execute As User='MyUser';
Exec dbo.P_T1;
Exec dbo.P_T2;
Exec dbo.P_T3;
Revert;
הפרוצדורות הראשונה והשלישית הצליחו, והשניה נכשלה.
הסבר: הראשונה הצליחה מכיוון של-MyUser ניתנה הרשאה להריץ את הפרוצדורה, ובעקיפין ובאופן כללי ניתנו בכך הרשאות לכל האובייקטים שהפרוצדורה מפעילה או ניגשת אליהם (זה לא תמיד כך- כפי שנראה, אבל באופן כללי כן).
הפרוצדורה השניה ביצעה Select באמצעות SQL דינאמי, במקרה זה ההרשאות אינן עוברות לקוד הדינאמי, ומכיוון של-MyUser אין הרשאות ישירות על הטבלה- ה-Select נכשל.
הפרוצדורה השלישית מופעלת עם הרשאות של dbo (הוא ה-Owner של הפרוצדורה כי היא בסכימה dbo שלו), ולכן ה-SQL הדינאמי מצליח כי הוא מתבצע עם הרשאות של dbo.
עד כאן מדוע יש צורך באופרטור Execute As בפרוצדורה במקרים בהם יש פעולות שאינן יורשות את ההרשאות של הפרוצדורה שמבצעת אותן, ויש להבדיל בין השימוש ב-Execute As כחלק מהפרוצדורה שמאפשר לשדרג את הרשאות המשתמש באופן נקודתי, לבין השימוש שאני עושה ב-Execute As כדי "לשנמך" את ההרשאות של עצמי ולהדגים מה קורה כשמשתמש "פשוט" מבצע אותן.
נתחיל את החלק הזה ביצירת שתי פרוצדורות הכוללות Execute As dbo – אחת בכל סכימה, וניתן ל-MyUser הרשאות על זו שב-dbo (על זו שב-MySchema אין צורך לתת כי הןא ה-Owner שלה):
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('P_Exec_dbo_1','P') Is Not Null Drop Proc P_Exec_dbo_1;
Go
Create Proc P_Exec_dbo_1 With Execute As 'dbo' As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
Grant Exec On P_Exec_dbo_1 To MyUser;
Go
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('MySchema.P_Exec_dbo_2','P') Is Not Null Drop Proc MySchema.P_Exec_dbo_2;
Go
Create Proc MySchema.P_Exec_dbo_2 With Execute As 'dbo' As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
---------------------------------------------------------------------------------------------------------------------------------
כעת נבצע את הראשונה- פעם עם הזהות שלי ופעם עם זו של MyUser;
ואת השניה- שוב, פעם עם הזהות שלי ופעם עם זו של MyUser:
Exec dbo.P_Exec_dbo_1;
Execute As User='MyUser';
Exec dbo.P_Exec_dbo_1;
Revert;
Exec MySchema.P_Exec_dbo_2;
Execute As User='MyUser';
Exec MySchema.P_Exec_dbo_2;
Revert;
כפי שניתן לראות- בכל ארבעת המקרים הפלט זהה: המערכת זיהתה את הסכימה של מי שמריץ אותה בתור dbo, את ה-User בתור dbo, ואת ה-Login (של ה-dbo) בתור sa; ללא קשר למי שבאמת מריץ ולסכימה שאליה הפרוצדורה שייכת.
כדאי להזכיר שב-SQL Server המונח dbo הוא גם סכימה וגם User.
מזה אפשר להבין שאם מבצעים Trace על הריצות של הפרוצדורות עלולה להיווצר כאן בעייה בשל ה-Impersonation: כיצד נדע מי באמת הריץ מה? ניתן להגיע לכך באמצעות הפונקציה Original_Login() שמציגה תמיד את ה-Login המקורי בו התחברו למערכת. אני אינני עושה בו כאן שימוש מפני שבכל מקרה הוא יציג את Login_Geri, גם במקרה בו אני מריץ את הפרוצדורה בתור MyUser.
אפשרות אחרת (להרצה בתור dbo) היא לתת לפרוצדורה לרוץ בהקשר (קונטקסט) של הבעלים (Owner) שלה, ובאופן דומה לנ"ל ניצור שתי פרוצדורות ונריץ כל אחת פעמיים- עם כל User בנפרד:
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('P_Exec_Owner_1','P') Is Not Null Drop Proc P_Exec_Owner_1;
Go
Create Proc P_Exec_Owner_1 With Execute As Owner As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
Grant Exec On P_Exec_Owner_1 To MyUser;
Go
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('MySchema.P_Exec_Owner_2','P') Is Not Null Drop Proc MySchema.P_Exec_Owner_2;
Go
Create Proc MySchema.P_Exec_Owner_2 With Execute As Owner As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
---------------------------------------------------------------------------------------------------------------------------------
במקרה זה התוצאות לא תלויות בשאלה מי הריץ באמת: הפרוצדורה זיהתה בכל ארבעת המקרים את ה-User בתור ה-Owner שלה (כלומר- הסכימה לה היא שייכת), את סכימה בתור הסכימה שלה, ואת ה-Login בתור זה של ה-User.
שוב- בפרוצדורה הראשונה dbo הוא גם הסכימה וגם ה-User (שניהם קיימים כברירת מחדל),
ובפרוצדורה השנייה- הסכימה היא MySchema וה-User הוא MyUser (שניהם נוצרו על ידינו בהתחלה).
לסיכום שני המקרים שבדקנו עד כה- פרוצדורה בסכימה dbo תתנהג באופן זהה אם נריץ אותה בתור dbo או בתור ה-Owner מפני שה-Owner הוא dbo. בסכימה MySchema יהיה כמובן הבדל אם נריץ בתור dbo או בתור ה-Owner; אבל בכל מקרה אין זה משנה מי הריץ באמת.
מקרה שלישי שנבדוק הוא הרצה בתור Self, כלומר- בתור זה שיצר את הפרוצדורה. במקרה זה נבדוק 4 פרוצדורות- שתיים בסכימה dbo שאחת נוצרה על ידי והשנייה על ידי MyUser, ושתיים בסכימה MySchema; ואת כל אחת מארבע הפרוצדורות נריץ פעמיים- פעם אחת על ידי ופעם אחת על ידי MyUser:
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('P_Exec_Self_dbo_Geri','P') Is Not Null Drop Proc
P_Exec_Self_dbo_Geri;
Go
Create Proc P_Exec_Self_dbo_GeriWith Execute As Self As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
Grant Exec On P_Exec_Self_dbo_Geri To MyUser;
Go
---------------------------------------------------------------------------------------------------------------------------------
Execute As User='MyUser';
If Object_ID('P_Exec_Self_dbo_MyUser','P') Is Not Null Drop Proc P_Exec_Self_dbo_MyUser;
Go
Create Proc dbo.P_Exec_Self_dbo_MyUser With Execute As Self As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
Revert;
Grant Exec On dbo.P_Exec_Self_dbo_MyUser To MyUser;
Go
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('MySchema.P_Exec_Self_MySchema_Geri','P') Is Not Null Drop Proc MySchema.P_Exec_Self_MySchema_Geri;
Go
Create Proc MySchema.P_Exec_Self_MySchema_Geri With Execute As Self As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
Grant Exec On MySchema.P_Exec_Self_MySchema_Geri To MyUser;
Go
---------------------------------------------------------------------------------------------------------------------------------
Execute As User='MyUser';
If Object_ID('MySchema.P_Exec_Self_MySchema_MyUser','P') Is Not Null Drop Proc MySchema.P_Exec_Self_MySchema_MyUser;
Go
Create Proc MySchema.P_Exec_Self_MySchema_MyUser With Execute As Self As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
Revert;
---------------------------------------------------------------------------------------------------------------------------------
Exec dbo.P_Exec_Self_dbo_Geri;
Execute As User='MyUser';
Exec dbo.P_Exec_Self_dbo_Geri;
Revert;
-----------------------------------------------------
Exec dbo.P_Exec_Self_dbo_MyUser;
Execute As User='MyUser';
Exec dbo.P_Exec_Self_dbo_MyUser;
Revert;
-----------------------------------------------------
Exec MySchema.P_Exec_Self_MySchema_Geri;
Execute As User='MyUser';
Exec MySchema.P_Exec_Self_MySchema_Geri;
Revert;
-----------------------------------------------------
Exec MySchema.P_Exec_Self_MySchema_MyUser;
Execute As User='MyUser';
Exec MySchema.P_Exec_Self_MySchema_MyUser;
Revert;
-----------------------------------------------------
מקווה שהצבעים עוזרים לשייך כל צמד פקודות לפלט שלהן.
ניתן לראות שבכל המקרים – המערכת זיהתה את מי שמריץ אותה כזה שייצר אותה, ולא כמי שמריץ "באמת" וגם לא כ-Owner שלה או של הסכימה אליו היא שייכת.
ברור שאני בודק כאן תסריטים שונים ומשונים, חלקם לא מציאותיים, וברוב המקרים הפרוצדורות נוצרות בסכימה dbo על ידי dbo; וכל התרגילים הטכניים נועדו להמחיש את משמעות האופציות השונות.
לסיום- יש אופציות נוספות לביצוע Impersonation, נדגים ונסביר:
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('dbo.P_1','P') Is Not Null Drop Proc dbo.P_1;
Go
Create Proc dbo.P_1 As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('dbo.P_2','P') Is Not Null Drop Proc dbo.P_2;
Go
Create Proc dbo.P_2 With Execute As Caller As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Exec dbo.P_1;
Go
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('dbo.P_3','P') Is Not Null Drop Proc dbo.P_3;
Go
Create Proc dbo.P_3 With Execute As 'MyUser' As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Exec dbo.P_2;
Go
Grant Exec On dbo.P_3 To MyUser;
Go
---------------------------------------------------------------------------------------------------------------------------------
Exec P_3;
Execute As User='MyUser';
Exec P_3;
Revert;
ההפעלה הראשונית היא של הפרוצדורה P_3 שמתבצעת בתור MyUser ללא קשר למי שבאמת הפעיל אותה.
P_3 מפעילה את P_2 שמתבצעת As Caller, כלומר כמי שהפעיל אותה ("מבחינתה"- זה מי ש- P_3 מזהה בתור המפעיל).
P_2 מפעילה את P_1 ללא כל חיווי מפורש לגבי "המפעיל".
כתוצאה מכך בשני המקרים הופעלו שלוש הפרוצדורות על ידי MyUser, וניתן לראות שאם לא מציינים במפורש מי מבצע הרי זה בעצם As Caller.
טריגר על פעולות DML בטבלה הוא כלי מוכר יחסית, אך לא כל האופציות מוכרות; ואולי מתבלבלים לעיתים בינו לבין טריגרים אחרים.
הפוסט הזה עוסק בטריגרים על טבלאות שמופעלים בעקבות ביצוע פעולות Delete / Update / Insert בטבלה עליה הם הוגדרו. הטריגרים אינם מופעלים בעת ביצוע פקודת Select או Truncate, שינוי סכימה של טבלה, או כל שינוי שהוא באובייקטים של המערכת; ולכל אלו יש פתרונות אחרים.
כדי לפשט את הדוגמאות- אשתמש בכל פעם בטבלה אחת בלבד, והטריגרים יפעילו פקודות Print או Select כדי להמחיש את הפעולה שלהם. במציאות הטריגרים אמורים להפעיל בדרך כלל פקודות Insert לטבלת לוג שמתעדת את השינויים בטבלה אליה הטריגר קשור, אם כי יכולים להיות להם שימושים נוספים.
כפי שציינתי- הטריגרים משמשים בדרך כלל לתיעוד השינויים לטבלת לוג, ולשם כך עליהם "ללכוד" את השורות שהשתנו. הטריגר הוא סקריפט (סדרת פקודות ב-TSQL) שמוגדר על טבלה ומופעל – בהתאם לאופן בו הוא הוגדר – בעקבות ביצוע פעולות Delete / Update / Insert (ניתן להגדיר טריגר על פעולה אחת או על מספר פעולות).
הטריגר יכול להיעזר בשני סטים יחודיים: Inserted שהוא סט השורות שהתווספו, ו-Deleted שהוא סט השורות שנמחקו. בפקודת Insert יש תוכן רק ב-Inserted, בפקודת Delete יש תוכן רק ב-Deleted, ובפקודת Update יש תוכן בשתיהן (Inserted עם השורות לאחר העדכון ו-Deleted עם השורות לפני העדכון).
ניצור טבלה להדגמה וניצור לה טריגר:
Use tempdb;
Go
If Object_Id('T_1','U') Is Not Null Drop Table T_1;
Go
Create Table T_1(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_1 On T_1
After Insert, Update, Delete As
Select Case When I.ID Is Null Then 'Deleted'
When D.ID Is Null Then 'Inserted'
Else 'Updated' End [Type],
IsNull(I.ID,D.ID) ID,
D.Txt OldTxt,
I.Txt NewTxt
From Inserted I
Full Outer Join Deleted D
On I.ID=D.ID;
Go
הטריגר שולף מ-Deleted ו-Inserted את כל השורות הרלוונטיות: בגלל שמדובר ב-Full Outer Join ישלפו במקרה של Insert כל ה-Inserted למרות שאין Deleted, להיפך במקרה של Delete, ובמקרה של Update ישלפו כולם; ולפי עמודת ה-ID נוכל לדעת באיזה סוג פעולה מדובר (עמודה Type);
כאשר אני מדגיש שוב שהשליפה למסך נועדה להמחיש כיצד הטריגר עובד: במציאות הפלט יופנה לטבלת לוג מתאימה.
Insert
Into T_1(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Update T_1
Set Txt='New bb'
Where ID=2;
Delete
From T_1
Where Txt='ccc';
אפשר לראות שכל אחת מהפעולות הפעילה את הטריגר, והוא שלף למסך את המידע השורות הרלוונטיות.
כמובן שניתן ליצור טריגר רק לפעולה אחת או שתיים, וניתן ליצור מספר טריגרים שיפעלו בטור זה אחר זה.
העובדה שהטריגר הנ"ל הוגדר כ-After (כדאי לעיין בקוד) היא ברירת המחדל, וניתן להשתמש באופרטור For במקומה והתוצאה תהיה זהה.
כדאי להדגיש שפעולת ה-DML והפעלת הטריגר מהווים טרנזקציה אחת: אם אחד מהם יכשל- כל הטרנזקציה תיפול, ושתי הפעולות לא יתבצעו. בנוסף- אם הטריגר איטי – כל פעולת ה-DML תהיה איטית ויקח זמן רב מהצפוי עד שנקבל אישור שהפעולה הסתיימה בהצלחה. בנוסף- החיווי בלשונית ה-Messages תתייחס גם לפעולת ה-DML וגם לפעולת הטריגר.
אופציה נוספת מיוחדת לטריגרים היא הפונקציה Update שבעזרתה ניתן לבדוק אילו עמודות השתנו:
Use tempdb;
Go
If Object_Id('T_2','U') Is Not Null Drop Table T_2;
Go
Create Table T_2(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_2 On T_2
After Insert, Update, Delete As
If Update(ID) Print 'ID column was updated';
If Update(Txt) Print 'Txt column was updated';
Go
Insert
Into T_2(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Update T_2
Set Txt='New bb'
Where ID=2;
Delete
From T_2
Where Txt='ccc';
בפקודת ה-Insert עודכנו שתי העמודות ולכן שתי פקודות ה-Print פעלו,
בפקודת ה-Update רק עמודה Txt עודכנה ולכן רק פקודת ה-Print "שלה" פעלה,
ובפקודת ה-Delete העמודות – לצורך העניין – לא התעדכנו ופקודות ה-Print לא פעלו.
האם חייבים לכתוב תנאי Update על כל עמודה וכמודה כדאי לדעת אילו התעדכנו? הרי בטבלה יכולים להיות עד 1024 עמודות (בהנחה שאין שימוש ב-Sparse).. לנוחיות ציבור הנוסעים קיים משתנה בשם Columns_Updated שהמידע הזה מקודד בו: מדובר במשתנה מסוג VarBinary שכל Byte בו מייצג שמונה עמודות על פי הסדר. כלומר- ערכו הבינארי יכול להיות למשל 01001001 ואז ניתן לדעת שעמודות מספר 2,5,8 בשמיניה שאותו Byte מייצג – התעדכנו.
בעזרת טבלת מספרים מ-1 ועד 128 (כי 1024 עמודות הן 128 שמיניות של עמודות), שימוש ב-& (ע"ע Bitwise And), פניה לטבלת המערכת sys.columns בה מופיעות כל העמודות ממוספרות על פי הסדר, הפונקציה DataLength שמחשבת כמה Bytes (שמיניות) יש ב-Columns_Updated ועוד כמה פירוטכניקות מגיעים לקוד הבא (טבלה, טריגר, ופעולות DML להמחשה):
Use tempdb;
Go
If Object_Id('T_3','U') Is Not Null Drop Table T_3;
Go
Create Table T_3(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_3 On T_3
After Insert, Update, Delete As
With Num As
(Select Top 128 Row_Number() Over(Order By DB_ID()) N --טבלת מספרים: מקסימום 128 שמיניות
From sys.messages)
Select Name --שם העמודה
From sys.columns --טבלת העמודות
Inner Join Num --טבלת המספרים הנ"ל
On column_id Between 8*(Num.N-1)+1 And 8*Num.N
Where Object_Name(object_id)='T_3' --רק העמודות של הטבלה לה שייך הטריגר
And Num.N<=DataLength(Columns_Updated()) --צד ימין מציין את מספר השמיניות
And Convert(Binary(1),Substring(Columns_Updated(),Num.N,1))&Power(2,column_id-(8*(Num.N-1)+1))>0 --אילו עמודות נמצאות בשמינייתן
Order By column_id; --מיון לפי סדר העמודות בטבלה
Go
Insert
Into T_3(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Update T_3
Set Txt='New bb'
Where ID=2;
Delete
From T_3
Where Txt='ccc';
שוב- הפלט למסך הוא רק כדי להמחיש מה המערכת זיהתה, ובמציאות המידע יופנה בדרך כלל לטבלה מתאימה.
האם קיים טריגר מסוג Before Insert? לא בדיוק: קיים Instead of Trigger שמתבצע במקום הפעולה (וממילא לפני שהיא מתבצעת..):
Use tempdb;
Go
If Object_Id('T_4','U') Is Not Null Drop Table T_4;
Go
Create Table T_4(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_4 On T_4
Instead of Update, Delete As
Return
Go
Insert
Into T_4(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Update T_4
Set Txt='New bb'
Where ID=2;
Delete
From T_4
Where Txt='ccc';
Select *
From T_4;
הטריגר הפעם הוא על פקודות Update & Delete ולא על Insert, ולכן פקודת ה-Insert התבצעה אך האחרות לא וניתן לראות זאת בפלט של פקודת ה-Select בסוף.
בטריגר עצמו מופיעה רק פקודת Return ולכן הוא אינו מבצע דבר במקום מה שאמור היה להתבצע.
כפי שציינתי- ניתן ליצור מספר טריגרים על אותה טבלה, אבל Instead of Triggers אינם יכולים לחפוף זה את זה (כלומר לא יכולים להיות שני Instead of Insert Triggers), וכשאחד מהם מופעל- הטריגרים האחרים (After Triggers) לא יתבצעו.
סדר ביצוע הטריגרים הוא אקראי אלא אם כן הגדרנו אחרת באמצעות SP_SetTriggerOrder:
Use tempdb;
Go
If Object_Id('T_5','U') Is Not Null Drop Table T_5;
Go
Create Table T_5(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_5_1 On T_5
After Insert, Update, Delete As
Print 'Trg_T_5_1';
Go
Create Trigger Trg_T_5_2 On T_5
After Insert, Update, Delete As
Print 'Trg_T_5_2';
Go
Create Trigger Trg_T_5_3 On T_5
After Insert, Update, Delete As
Print 'Trg_T_5_3';
Go
Insert
Into T_5(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Exec SP_SetTriggerOrder 'Trg_T_5_1', 'Last', 'Update';
Exec SP_SetTriggerOrder 'Trg_T_5_3', 'First', 'Delete';
Update T_5
Set Txt='New bb'
Where ID=2;
Delete
From T_5
Where Txt='ccc';
בפקודת ה-Insert הטריגרים בוצעו בסדר אקראי (במקרה או שלא- לפי סדר היווצרותם).
לאחר מכן הטריגר הראשון הוגדר כאחרון לפקודות Update, והטריגר השלישי כראשון לפקודות Delete.
בפקודת ה-Update הטריגר הראשון בוצע אחרון כפי שהוגדר עבורו,
ובפקודת ה-Delete הטריגר השלישי בוצע ראשון כפי שהוגדר עבורו (והראשון אחריו מכיוון שהוא אינו מוגדר כאחרון עבור Delete אלא רק עבור Update).
לבסוף- ניתן ליצור טריגרים גם על Views, בתנאי שמדובר ב-Instead of Trigger:
Use tempdb;
Go
If Object_Id('T_6','U') Is Not Null Drop Table T_6;
Go
Create Table T_6(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_6_1 On T_6
After Insert, Update, Delete As
Print 'Trg_T_6_1';
Go
If Object_Id('V_6','V') Is Not Null Drop View V_6;
Go
Create View V_6 As
Select *
From T_6;
Go
Create Trigger Trg_T_6_2 On V_6
Instead of Insert, Update, Delete As
Print 'Trg_T_6_2';
Go
Insert
Into T_6(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Insert
Into V_6(Txt)
Select 'd' Union All
Select 'ee' Union All
Select 'fff';
כפי שאפשר להבין- פעולת ה-Insert הראשונה הופנתה לטבלה והתבצעה,
ואילו פעולת ה-Insert השניה הופנתה ל-View והטריגר התבצע במקומה.
אין מניעה לבצע Insert ל-Views כמו זה, אולם המגבלה של Instead of Triggers גורפת לכל סוגי ה-Views בין אם ניתן לבצע עליהם פעולות DML ובין אם לא.
טריגרים ניתן לנטרל מבלי לבטל כך (בעיקר כשמדובר בהשעייה זמנית):
Disable Trigger All On T_6; --כל הטריגרים
Go
Disable Trigger Trg_T_6_1 On T_6; --טריגר ספציפי
Go
ולהחזיר לכשירות כך:
Enable Trigger All On T_6; --כל הטריגרים
Go
Enable Trigger Trg_T_6_1 On T_6; --טריגר ספציפי
Go
ב-TSQL יש פונקציית Sum לסיכום ערכים, אך אין פונקציית Product לכפילתם. כיצד נחשב איפוא מכפלה של ערכים בשליפת Group By?
הזכרתי בעבר את הפתרון שלמדתי מאב"ג ושהוא עצמו למד מאחרים, ומדי פעם אני נאנח בצער על שלא אני המצאתי את הפטנט הזה: כשלמדתי בתיכון לפני קרוב ל-40 שנה מחשבוני הכיס רק הופיעו, אנחנו היינו כחולמים כשראינו שהם מסוגלים לחבר 1 ועוד 1 ולהחזיר את התוצאה 2, והמשוכללים שבהם כללו פונקציות כמו שורש והראו שהשורש הריבועי של 9 הוא 2.9999999 (בערך)..
לשיעורי חשבון הגענו מצויידים בלוח לוגריתמים- חוברת שכלל טבלאות לוגריתמים של מספרים, וטבלאות סינוס/קוסינוס/טנגנס של זוויות; ובעזרתם פתרנו בעיות חישוביות. הלוגריתמים סייעו לבצע מכפלות מסובכות או חישובי חזקות מעוררי חלחלה בכך שבמקום לכפול שני מספרים היינו מחברים את הלוגריתמים שלהם, ומוצאים את האנטילוג של התוצאה.
זה בערך הרעיון גם בפטנט לחישוב מכפלה:
Select ID,
Exp(Sum(Log(MyFld)))
From MyTbl
Group By ID;
פונקציית Log ב-SQL Server היא לפי הבסיס הטבעי e, והפונקציה Exp מחזירה את e בחזקת הפרמטר שזה בעצם האנטילוג.
יש לפתרון הזה מגבלות, ובראש ובראשונה העובדה שאין Log לאפס ולמספרים שליליים, ולכן גם לבעייה זו נאלתר פתרון.
קודם כל- ניצור בעייה שיש לפתור:
Create Table #T(ID Int, I Int);
Go
Insert
Into #T(ID,I)
Select 1,2 Union All --ID=1 is OK
Select 1,8 Union All
Select 1,5 Union All
Select 2,0 Union All --ID=2 has 1 zero
Select 2,2 Union All
Select 2,8 Union All
Select 2,5 Union All
Select 3,-2 Union All --ID=3 has 2 negatives
Select 3,8 Union All
Select 3,-5 Union All
Select 4,2 Union All --ID=4 Has 1 negative
Select 4,-8 Union All
Select 4,5 Union All
Select 5,0 Union All --ID=5 Has 2 zeros and 3 negatives
Select 5,-2 Union All
Select 5,-8 Union All
Select 5,-5 Union All
Select 5,0;
Go
Select *
From #T
Order By ID;
וחישוב המכפלה של I לכל ID:
Select ID,
Case When Min(Abs(I))=0 Then 0
Else Exp(Sum(Log(Case When I=0 Then 1 Else Abs(I) End)))*Power(-1,Count(Case When I<0 Then 1 End))
End
From #T
Group By ID;
התיבה האדומה: אם יש 0 (אפס) בדרך המכפלה גם 0.
התיבה השחורה: חישוב ה-Log מתבצע על הערך המוחלט, ובנפרד אני מחשב כמה שליליים יש (במקרה של 0 לא משנה מה נבחר).
התיבה התכולה: התוצאה מוכפלת ב-(-1) בחזקת מספר השליליים (אם זוגי אזי 1 ואם שלילי -1).
עד כמה התוצאה מדוייקת? הרי הלוגריתמים אינם מספרים שלמים ובחישובים על הרבה ערכים עלולה להיווצר סטייה ונקבל תוצאות לא מדוייקות.. ביצעתי מספר בדיקות- הצלחתי לחשב עצרת עד 15! (יותר מטריליון) ללא כל סטיה, וחזקות של 2 עד 224 (יותר מ-16 מיליון) ללא סטיה. מעבר לכך נוצרות סטיות שהן זניחות בהתחשב בגודל המספרים, והן בעייתיות בעיקר מבחינה אסתטית (מכפלה של שלמים מחזירה מספר לא שלם).
יחד עם זאת- גם הבעייה האסתטית לרוב אינה בעייה כלל: מתי נצטרך לחשב מכפלות בצורה כזו? הרי אף אחד אינו נדרש לחשב מכפלות אסטרונומיות של מספרים שלמים; ולרוב נצטרך לחשב מכפלות של אחוזים (למשל- חישוב ריבית דריבית להלוואות) או מכפלות של סיכויים, ואז מדובר מראש בשברים שמכפלתם אף היא שבר.
מה נעשה אם נרצה להגביל מראש את מספר השורות הנשלפות כדי לא להעמיס על המערכת שלא לצורך,
או אם נרצה לשלוף בסדר אקראי כדי לא לראות בכל פעם אותן 20-30 שורות ראשונות,
או נבקש לבצע פעולה מורכבת יחסית על מדגם מייצג מהטבלה?
הרי בדרך כלל כשאנחנו צריכים להציץ בטבלה כדי להתרשם מ"מה יש שם", אנו כמקובל מפעילים פקודת Select, ממתינים בין שבריר שנייה למספר שניות שהריצה תסתיים, מעיינים, וממשיכים הלאה. אם מתברר שבטבלה יש כמה מליוני שורות והשליפה עלולה להימשך זמן רב- לוחצים כמקובל על האייקון האדום שעוצר את זה באמצע ומסתפקים במה שכבר נשלף.
מישהו נוהג אחרת? כנראה שלא..
אילו אופציות יותר מקצועיות עומדות לרשותנו למקרה הצורך?
האפשרות הראשונה היא למיין באופן אקראי בעזרת NewID:
Use AdventureWorks;
Go
Select Top 20 *
From Sales.Customer
Order By NewID();
אפשר לשלוף את כולן ואפשר לשלוף את חלקן בעזרת Top, עובד כמו שצריך, והבעייה היחידה היא שהמערכת באמת ממיינת את הטבלה, ולא בטוח שלשלוף מליון שורות לטבלה יותר גרוע מלמיין אותן..
למערכת יש פתרון נוסף, פחות מוכר, ועם יותר פונקציונליות:
Select *
From Sales.Customer TableSample(20 Rows);
המערכת מבצעת במקרה זה Scan על הטבלה, ובוחרת אקראית 20 שורות מבלי למיין; וליתר דיוק- היא כנראה בוחרת נקודות אקראיות ושולפת רצף של שורות החל מכל אחת (או משהו בסגנון); כל זה בכפוף לחלק היחסי של 20 מכלל השורות. אני מציין זאת כי לרוב לא יחזרו 20 שורות בדיוק אלא בממוצע: לעיתים יותר, לעיתים פחות, ולעיתים בכלל לא.
באופן דומה ניתן לבחור להציג אחוז מסויים משורות הטבלה:
Select *
From Sales.Customer TableSample(2 Percent);
במקרה זה חוזרות לי תמיד כפולות שלמות של 188 שורות (מתוך 19185), כאשר כל כפולה היא רציפה (שימו לב שבצילום המסך ה-CustomerID רציפים), ו-188 הוא ככל הנראה מספר השורות ב-Page (בנ"ל 103 Pages), וזה נותן רמז לגבי האופן בו המנגנון פועל.
במילים אחרות- לא רק השורות החוזרות עצמן הן אקראיות, אלא גם מספר השורות החוזרות הוא אקראי..
מכיוון שבכל שליפה יחזרו שורות אחרות, יתכן מצב בו נרצה שאותן X שורות אקראיות יחזרו בכל פעם; למשל אם מריצים תהליך על מדגם מהטבלה, מדבּגים, מתקנים, ורוצים להריץ אותו שוב על אותו מדגם לביקורת; או אולי אם מבצעים Join של מדגם מהטבלה עם עצמו, ורוצים ששני המדגמים יהיו זהים כדי שהחיתוך לא יהיה ריק.
במקרה כזה נוכל להפעיל את השליפה עם האופרטור Repeatable ומספר אקראי כלשהו, ואז עבור אותו מספר אקראי יחזור תמיד אותו סט (לצורך ההדגמה אני מריץ אותה שליפה פעמיים עם המספר האקראי 230):
Select *
From Sales.Customer TableSample(20 Rows)
Repeatable(230);
כדאי להסתייג ולציין שהשימוש באופרטור TableSample מוגבל לטבלאות משתמש בלבד, ולא נוכל לפנות בעזרתו לטבלאות מערכת, פונקציות המחזירות טבלה וכו';
ובנוסף- פילטור התוצאות בעזרת Where יפעל על השליפה בדיעבד ולכן יחזרו כנראה פחות שורות ממה שציינו.
נסיונות לעזור לאופטימייזר של ה-SQL Server משול לעיתים לניסיון המיתולוגי לעזור לזקנה לחצות את הכביש: גרמנו יותר תועלת מנזק.. נכון שלא תמיד זה כך, ולכן יש להפעיל שיקול דעת.
הפעם דוגמה מתי לא כדאי: נתונות שתי טבלאות- אחת גדולה (100,000 שורות) ואחת קטנה (33 שורות) ואנחנו מעוניינים לבצע Join בינהן.
העמודה בטבלה הקטנה איתה מתבצע ה-Join היא יחודית ולכאורה היא Primary Key, אך לטבלאות לא הוגדרו אינדקסים.
אנחנו רוצים לעזור לאופטימייזר: הוא הרי יבצע Scan לטבלה הגדולה, ולכל שורה יחפש התאמה בטבלה הקטנה; ומכיוון שהוא אינו "יודע" שיכולה להיות רק התאמה אחת בקטנה- הוא לא יעצר כשימצא אלא ימשיך לחפש וחבל..
היה ראוי ליצור אינדקסים מתאימים ולתת לאופטימייזר להשתמש במידע השלם על פי הבנתו, אבל אנחנו מנסים בטעות להחליט במקומו כיצד לעבוד..
ניצור שתי טבלאות:
If Object_Id('messages','U') Is Not Null Drop Table messages;
Go
Select *
Into messages
From sys.messages;
Go
If Object_Id('syslanguages','U') Is Not Null Drop Table syslanguages;
Go
Select *
Into syslanguages
From sys.syslanguages;
Go
--טבלה גדולה
Select *
From messages;
--טבלה קטנה
Select *
From syslanguages;
וכעת נבצע Join בארבע דרכים שונות:
--1
Select *
From messages M
Inner Join syslanguages L
On M.language_id=L.lcid;
--2
Select *
From messages M
Cross Apply (Select *
From syslanguages L
Where M.language_id=L.lcid) L;
--3
Select *
From messages M
Inner Join (Select lcid,
Max(langid) langid,
Max(dateformat) dateformat,
Max(datefirst) datefirst,
Max(Upgrade) Upgrade,
Max(name) name,
Max(alias) alias,
Max(months) months,
Max(shortmonths) shortmonths,
Max(msglangid) msglangid
From syslanguages
Group By lcid) L
On M.language_id=L.lcid;
--4
Select *
From messages M
Cross Apply (Select Top 1 *
From syslanguages L
Where M.language_id=L.lcid) L;
שליפה 1 – Join רגיל בין שתי הטבלאות.
שליפה 2 – Join בשיטת Cross Apply (לפי הסינטקס ניכר שההבדלים בינו לבין Join רגיל הם קוסמטיים במקרה זה).
שליפה 3 – ניסיון "לעזור" ל-Join הרגיל: נבצע Group By על הטבלה הקטנה כדי "לאלץ" אותה להיות עם Primary Key (זה לא ישנה אותה אבל ירמוז למערכת שכעת היא כזו).
שליפה 4 – ניסיון "לעזור" ל-Cross Apply על ידי הוספת האופרטור Top כדי "לאלץ" את המערכת להסתפק בשורה המתאימה הראשונה ולא לחפש עוד התאמות ללא צורך.
כפי שאפשר לראות מה-Execution Plan – שתי השליפות הראשונות זהות,
השליפה השלישית מעט פחות טובה מהן- השימוש ב-Group By לא הביא תועלת ואולי אף פגע מעט בביצועים (התווספו מיון ואגרגציה),
והשליפה הרביעית גרועה משלוש הקודמות באופן ניכר, וזה מעורר תמהון:
הרי לשימוש ב-Top אמורה להיות רק תועלת ובוודאי שלא נזק כזה..
הסיבה העיקרית לכך נלמדת מה-Properties של ה-Table Scan של הטבלה הקטנה: בעוד שבשלוש השליפות הראשונות התבצע Table Scan אחד שעם התוצאות שלו התבצע Hash March עם הטבלה הגדולה, בשליפה הרביעית התבצעו 97526 Table Scans (במילים- לא אחד אלא תשעים ושבעה אלף חמש מאות עשרים ושישה!) כמספר השורות בטבלה הגדולה.
מתברר שהמערכת מחפשת בטבלה הקטנה Top 1 לכל שורה מהטבלה הגדולה, מכיוון שאין לה סיבה להבין שמספר ערכי ה-Top מוגבל ולכל Language_id מהטבלה הגדולה יחזור אותו lcid מהקטנה.
בקיצור- הטענו את האופטימייזר, וכמו שאמא שלי נהגה לנזוף בי כשהייתי ילד: אם אתה רוצה לעזור- עשה מה שמבקשים ממך (=צור אינדקסים מתאימים), או שלפחות אל תפריע (=השימוש הנפסד באופרטור Top)..
ב-Execution Plans אנחנו מוצאים לא פעם את החיוויים הנ"ל צמודים ל-Hash Match או ל-Nested Loops.
מה המשמעות שלהם?
לגבי Join, Left Join, Right Join לא ארחיב כאן ורק אזכיר שהם מחזירים את כל ההתאמות בין שתי טבלאות או סטים בכפוף להגדרות והתנאים, ואציין כאן שתי דוגמאות מפוסט קודם:
--1
Select *
From messages M
Inner Join syslanguages L
On M.language_id=L.lcid;
--2
Select *
From messages M
Cross Apply (Select *
From syslanguages L
Where M.language_id=L.lcid) L;
הסינטקס שונה אך מדובר בפעולות Join זהות במקרה זה: כל ההתאמות בין הטבלה העליונה לתחתונה מוצגות.
Semi Join ("חצי" Join, Join חלקי..) הוא פעולה שמחזירה את כל השורות מהטבלה הראשונה (Left Semi Join) / מהטבלה השניה (Right Semi Join) שיש להן התאמה בטבלה השניה / הראשונה בהתאם. כלומר- לא כל ההתאמות בין שתי הטבלאות חוזרות, אלא כל השורות מהטבלה האחת שיש להן התאמה באחרת (השורה תוצג פעם אחת גם אם יש לה מספר התאמות).
רוצה לומר שהמערכת מחפשת לכל שורה בטבלה האחת התאמה באחרת, ואם היא מצאה- היא עוצרת את החיפוש ומציגה אותה.
Anti Semi Join ("חצי" Join הפוך) הוא פעולה שמחזירה את כל השורות מהטבלה הראשונה (Left Anti Semi Join) / מהטבלה השניה (Right Anti Semi Join) שאין להן התאמה בטבלה השניה / הראשונה בהתאם. המערכת מחפשת לכל שורה בטבלה האחת התאמות באחרת, אם היא מוצאת היא עוצרת את החיפוש ולא מציגה אותה, ואם היא לא מוצאת – היא כן מציגה אותה.
דוגמאות, ונתחיל מיצירת שתי טבלאות – אחת גדולה ואחת קטנה - להדגמה:
If Object_Id('messages','U') Is Not Null Drop Table messages;
Go
Select *
Into messages
From sys.messages;
Go
If Object_Id('syslanguages','U') Is Not Null Drop Table syslanguages;
Go
Select *
Into syslanguages
From sys.syslanguages;
Go
--טבלה גדולה
Select *
From messages;
--טבלה קטנה
Select *
From syslanguages;
Right Semi Join
--Right Semi Join
Select *
From messages M
Where Exists (Select 1
From syslanguages L
Where L.msglangid=M.language_id);
Select *
From messages
Where language_id In (Select msglangid
From syslanguages);
לשתי השליפות הנ"ל אותו Execution Plan: המערכת מבצעת Scan לטבלה השניה/התחתונה (messages) ולכל שורה מחפשת התאמה ב-Hash Table של הראשונה, ואם יש- מציגה אותה.
Right Anti Semi Join
--Right Anti Semi Join
Select *
From messages M
Where Not Exists (Select 1
From syslanguages L
Where L.msglangid=M.language_id);
Select *
From messages
Where language_id Not In (Select msglangid
From syslanguages);
שוב- שני Execution Plans זהים לשתי השליפות: המערכת מבצעת Scan לטבלה השניה/התחתונה (messages) ולכל שורה מחפשת התאמה ב-Hash Table של הראשונה, ואם יש- אינה מציגה אותה.
Left Semi Join
--Left Semi Join
Select *
From syslanguages L
Where Exists (Select 1
From messages M
Where L.msglangid=M.language_id);
Select *
From syslanguages
Where msglangid In (Select language_id
From messages);
Select msglangid
From syslanguages
Intersect
Select language_id
From messages;
Select language_id
From messages
Intersect
Select msglangid
From syslanguages;
לשליפות הראשונה והשניה אותם Execution Plans, והם די דומים ל-Right Semi Joins שפגשנו קודם מבחינת הסינטקס אך לא מבחינת ה-Execution Plans שכן הטבלה הגדולה messages והטבלה הקטנה syslanguages החליפו מקומות והפניות החוזרות ונשנות לגדולה מחייבות תחליף לאינדקס שאינו קיים וזה ה-Table Spool (טבלה אגרגטיבית זמנית שנוצרת ב-tempdb).
מה שמעניין יותר הוא שתי השליפות האחרונות בהן פקודת Intersect יוצרת Join בין העמודות המתאימות בשתי הטבלאות ומסננת כפילויות. למרות שהתוצאות זהות והאופרטור Intersect הוא סימטרי- המערכת בחרה במקרה הראשון להתחיל עם הטבלה הקטנה, למיין אותה ולכל שורה לחפש התאמה בגדולה (לאחר שעברה אגרגציה למניעת כפילויות); ובמקרה השני להתחיל דווקא מהגדולה, לבצע לה אגרגציה, ולחפש התאמות בקטנה מבלי למיין אותה תחילה.
אפשר להבין שכדי למנוע כפילויות המערכת בוחרת למיין (כדי לאתר אותם בקלות) או לבצע אגרגציה.
בשני המקרים זה Left Semi Join אבל בכל פעם מתחילים מטבלה אחרת, וזה קצת מפתיע לאור העובדה ש-Intersect הוא אופרטור סימטרי והסדר אינו אמור להיות משמעותי עבורו.
Left Anti Semi Join
--Left Anti Semi Join
Select *
From syslanguages L
Where Not Exists (Select 1
From messages M
Where L.msglangid=M.language_id);
Select *
From syslanguages
Where msglangid Not In (Select language_id
From messages);
Select msglangid
From syslanguages
Except
Select language_id
From messages;
Select language_id
From messages
Except
Select msglangid
From syslanguages;
גם כאן שתי השליפות הראשונות זהות מבחינת ה-Executions Plans והפלטים, ומה שמעניין הוא שתי השליפות האחרונות:
בשתיהן התבצעה פעולת Except (חיסור של הטבלה השניה מהראשונה) שמשעותה דומה בבירור ל-Left Anti Semi Join (להציג את השורות מהראשונה שאינן בשניה). Except אינה סימטרית ולכן ברור מדוע יש הבדל ב-Execution Plans.
לסיכום: יוסף בורג ז"ל – שר הפנים הנצחי – אמר פעם- ימין ושמאל רק חול וחול (ציטוט משיר תמים בהקשר פוליטי),
ובאופן דומה נוכל לומר- Left Anti Semi or Right Anti Semi – כולם אנטישמים!
מה ההבדל בין Char / NChar / Varchar / NVarchar?
מתי נבחר במשתנה התומך ביוניקוד (NChar / NVarChar) ומתי לא (Char / Varchar)?
מתי נבחר במשתנה קבוע באורכו (Char / NChar) ומתי בבעל גודל משתנה (Varchar / NVarchar)?
כיצד נדע באיזה גודל משתנה לבחור- גודל מוגבל או לא (Max)?
לרוב הצרכים המעשיים- שימוש ב-Char או ב-Varchar אמור להספיק בהנחה שה-Collation הוא עברי ואנחנו מעוניינים לכתוב בעברית ובאנגלית כולל נִיקוּד בעברית ואותיות גדולות/קטנות ב-English, אך לא בתווים לטיניים מיוחדים האופייניים לחלק מהשפות האירופאיות.
סוגי נתונים כדוגמת NChar או NVarchar עושים שימוש ב-Unicode שהוא תקן המאפשר שימוש בכל השפות במחיר הכפלת גודל השדה. כזכור- לכל תו יש ערך Ascii משלו, אלא שאותו ערך יכול לייצג אותיות שונות בשפות שונות, למשל:
Use ReportServer;
Go
Select Char(233);
Go
Use tempdb;
Go
Select Char(233);
Go
כלומר- בדטבייס הראשון שה-Collation שלו הוא Latin1_General_CI_AS_KS_WS (אינו תומך בעברית), הערך מתאים לתו é הקיים בשפות כמו צרפתית וספרדית,
ובדטבייס השני שה-Collation שלו הוא Hebrew_CI_AS (תומך בעברית וגם באנגלית אך לא בתווים מיוחדים כמו é), הערך מתאים לתו י העברי.
מי שרוצה תמיכה גם בעברית וגם בצרפתית (למשל)- יצטרך להשתמש ביוניקוד, כלומר- בסוגי נתונים כדוגמת NChar / NVarchar, כשרב-השימושיות הזו מתאפשר במחיר של הכפלת גודל הנתון ואז מספר התווים הנתמכים אינו 256 כמו ב-Ascii אלא 65536=256*256; אך כפי שציינתי- ברוב המקרים אין זה כך.
כדאי לציין פונקציות מערכת כדוגמת SP_ExecuteSQL שמקבלות פרמטרים מסוג NVarchar, גם אם קוד ה-SQL הוא באנגלית..
השימוש ב-Char (או לחילופין ב-NChar בסביבות רב לשוניות) שאורכו קבוע מתאים למקרים בהם בכל הנתונים אותו מספר תווים, או הפרש שלא יעלה על 3-4 תווים. למשל- המיקוד בארץ (5 תווים), קידומת של מספר טלפון (2-3 תווים) ועוד. במקרה זה גודל הטקסט יהיה תמיד כגודל המשתנה- גם אם הוא קצר יותר, אבל יחסך הצורך ב-Header לכל נתון בו יצויין מה אורכו.
כלומר- משתנה מסוג Varchar (או NVarchar) מקצה מקום רק לטקסט אותו הוא שומר, אולם בכל שורה (אם מדובר בעמודה מסוג Varchar) הוא ישמור מידע בגודל קבוע בנוסף לטקסט עצמו.
מכל זה אפשר להבין ש-Varchar בגודל קטן מ-5 הוא מיותר..
מתי נשתמש ב-(Varchar(Max? לכאורה, אם הגענו למסקנה שיש להשתמש ב-Varchar ו"שילמנו" את מחיר ה-Header, מה איכפת לנו אם ה-Varchar יהיה יותר גדול ואף מקסימלי בגודלו? בכל מקרה הוא ישמור את מה שצריך, וגם אם יהיה נתון בלתי צפוי באורכו- שום דבר לא ישתבש.. רבים מקצים בטבלת העובדים 50-20 תווים לשם העובד, אבל מה יקרה אם מלך ספרד יחליט לעבוד אצלנו בארגון ויתעקש שנכתוב את שמו המלא על שלל תאריו?..
זו סיבה טובה לשקול את הנושא בכובד ראש, אם כי יש פוסטים באינטרנט הטוענים שהדבר עלול לפגוע בביצועים בפעולות עדכון או הוספה..
לכל זה מצטרפים שיקולים נוספים לכאן או לכאן, למשל- כאשר משרשרים שני נתונים שאורכם המצטבר מעל 8000 תווים (4000 במקרה של NVarchar) שזה אורכו המקסימלי של Varchar שאינו Max – המחרוזת עלולה להיחתך בתו ה-8000:
Declare @S1 Varchar(4500),
@S2 Varchar(4500),
@S3 Varchar(Max);
Select @S1=Replicate('#',4500),
@S2=Replicate('#',4500),
@S3=@S1+@S2;
Select Len(@S1),
Len(@S2),
Len(@S3);
Go
Declare @S1 Varchar(Max),
@S2 Varchar(Max),
@S3 Varchar(Max);
Select @S1=Replicate('#',4500),
@S2=Replicate('#',4500),
@S3=@S1+@S2;
Select Len(@S1),
Len(@S2),
Len(@S3);
Go
(פונקציית Replicate משכפלת תו נתון מספר נתון של פעמים)
במקרה הראשון החיבור של שתי המחרוזות באורך 4500 כל אחת יצר מחרוזת באורך 8000 (שזו שגיאה),
ובמקרה השני החיבור של שתי המחרוזות יצר מחרוזת באורך 9000 (שזה נכון בזכות השימוש ב-(Varchar(Max)
מנגד יש פונקציות מערכת שאינן מקבלות (Varchar(Max אלא רק Varchar מוגבל בגודלו, למשל XP_CmdShell (הראשון יצליח והשני יכשל):
Declare @S Varchar(8000)='Ver';
Exec XP_CmdShell @S;
Go
Declare @S Varchar(Max)='Ver';
Exec XP_CmdShell @S;
Go
נימוק כבד משקל שיש לקחת בחשבון הוא נושא האינדקסים: עמודות מסוג (Varchar(Max או (NVarchar(Max לא ניתן לאנדקס. נכון שניתן לכלול אותן באינדקס על ידי האופרטור Include (במקרה של Covered Index), אך הן יכללו מבלי להיות מאונדקסות.
וכעת לדוגמאות:
Create Table T_Char_20_20(S Char(20));
Create Table T_VarChar_20_20(S VarChar(20));
Create Table T_VarChar_Max_20(S VarChar(Max));
Create Table T_Char_24_20(S Char(24));
Create Table T_VarChar_24_20(S VarChar(24));
Go
Insert Into T_Char_20_20 Select Replicate('#',20) From sys.messages;
Insert Into T_VarChar_20_20 Select Replicate('#',20) From sys.messages;
Insert Into T_VarChar_Max_20 Select Replicate('#',20) From sys.messages;
Insert Into T_Char_24_20 Select Replicate('#',20) From sys.messages;
Insert Into T_VarChar_24_20 Select Replicate('#',20) From sys.messages;
Go
DBCC ShowContig ('T_Char_20_20') With TableResults;
DBCC ShowContig ('T_VarChar_20_20') With TableResults;
DBCC ShowContig ('T_VarChar_Max_20') With TableResults;
DBCC ShowContig ('T_Char_24_20') With TableResults;
DBCC ShowContig ('T_VarChar_24_20') With TableResults;
Go
יצרנו 5 טבלאות, ובכל אחת עמודת טקסט ו-97526 שורות בנות 20 תווים כל אחת:
T_Char_20_20, T_Char_24_20 – עמודת טקסט מסוג Char, באחת בגודל 20 ובאחרת בגודל 24.
T_VarChar_20_20, T_VarChar_24_20 – עמודת טקסט מסוג VarChar, באחת בגודל 20 ובאחרת בגודל 24.
T_VarChar_Max_20 – עמודת טקסט מסוג VarChar בגודל Max.
וכעת על פי הסדר בצילום המסך:
הטבלה הקטנה ביותר היא T_Char_20_20 ש"תפורה" בדיוק לפי המידה, גודלה 350 Pages, וגודל כל רשומה 27 Bytes בלבד.
הטבלה השניה היא מסוג (Varchar(20, היא מחזיקה אותו מידע כמו הקודמת, אך בגלל הצורך ב-Header בכל שורה היא גדולה יותר- 399 Pages וכל רשומה 31 Bytes.
הטבלה השלישית היא מסוג (Varchar(max אבל מבחינת הגודל היא זהה לקודמת (399 Pages, 31 Bytes): העובדה שבחרנו ב-Max ולא ב-20 לא עלתה לנו בנפח אחסון.
הטבלה הרביעית היא מסוג (Char(24 למרות שהיא מחזיקה טקסטים בגודל 20. הדבר כרוך בבזבוז של מקום מיותר, וכתוצאה מכך הגודל שלה זהה לזה של (Varchar(20 וכפי שתיכף נראה – גם לזה של (Varchar(24. ה-Header של ה-Varchar שקול בגודלו ל-4 תווים.
הטבלה החמישית מסוג (Varchar(24, ואין הבדל בגודל בינה לבין (Varchar(20 ו-(Varchar(Max (אני מזכיר שבכל הטבלאות נשמרו טקסטים בגודל 20 תווים).
הערה- כאשר בעמודה בגודל 24 נשמר טקסט בגודל 20, ארבעת התווים המיותרים גורמים לא רק לעלות אחסון מיותרת, אלא עלולה לגרום לשיבושים בעת שירשור מחרוזות, וראוי "לקצץ" את הזנב באמצעות הפונקציה RTrim.
לסיכום הבדיקה בארבע הטבלאות: אין הבדל מבחינת האחסון בין (Varchar(Max ו-Varchar רגיל,
ו-Char בגודל קבוע חסכוני ביחס ל-Varchar כל עוד מדובר במחרוזות בגודל שלו או קטנות בפחות מ-4 תווים.
אני אישית הייתי משתמש בו רק כשהמחרוזות באורך קבוע (למשל- מיקוד): החסכון הזניח בכמה בייטים אינו שווה את ההתעסקות בלקצר את המחרוזות מהתווים הריקים המתווספים אליהם.
לסיום, אינדקסים- נוסיף לטבלה עם ה-(Varchar(Max עמודת Identity וננסה לאנדקס אותה בשלוש דרכים:
אינדקס על עמודת ID,
אינדקס על ID ועל S (עמודת ה-Varchar),
אינדקס על ID הכולל (Include) את S.
Alter Table T_VarChar_Max_20 Add ID Int Identity;
Go
Create Index Idx1_T_VarChar_Max_20 On T_VarChar_Max_20(ID);
Go
Create Index Idx2_T_VarChar_Max_20 On T_VarChar_Max_20(ID, S);
Go
Create Index Idx3_T_VarChar_Max_20 On T_VarChar_Max_20(ID) Include (S);
Go
הודעת השגיאה היא לגבי הנסיון ליצור אינדקס על ID ועל S,
וניתן להבין מכך של-(Varchar(Max לאניתן ליצור אינדקס, ולכן עמודות תיאור (שם ישוב, שם פרטי וכו’) ראוי שיהיו מסוג (Varchar(n כלשהו.
לסיכום:
1. נפח האחסון של Varchar רגיל ו-(Varchar(Max – זהה.
2. השימוש ב-Char מומלץ כשכל הנתונים בעלי אורך זהה.
3. לא ניתן לאנדקס (Varchar(Max, אלא רק לכלול (Include) אותו.
4. שירשור שתי מחרוזות מסוג Varchar רגיל שאורכן המצטבר מעל 8000 יצור מחרוזת באורך 8000.
5. NChar / NVarchar הם החלופות היוניקודיות של Char / Varchar וחלים עליהם אותן מגבלות פחות או יותר.
6. האורך המקסימלי של NChar / NVarchar (שאינו Max) הוא 4000 מכיוון שנפח האחסון של כל תו- כפול.
7. משתנים יוניקודיים תומכים בכל השפות, ללא תלות ב-Collation.
8. יתכן שעדכון או הוספה לעמודות (Varchar(Max איטיים יותר. לא בדקתי.
גרסת RC0 זו גרסת ההתנסות האחרונה לפני ההשקה של SQL 2012 בפורים.
מי שמתקין אותה בשיטת מיקרוסופט (Enter => Next => I Agree => Choose Default/Recommended Option => OK..) עלול לקבל במהלך הגדרות ההתקנה הודעת שגיאה בהאי לישנה: There was a failure to validate setting CTLRUSERS in validaton function ValidateUsers, ולינק לכתובת באינטרנט שלא מופיע בה מידע רלוונטי או הצעה לפתרון.
הפתרון: בשלב Distributed Replay Controller יש להקיש <Next> ולא לבחור ב-Add Current User (בה השתמשנו באחד השלבים הקודמים לבחירת sa):
כדאי לשים לב שבמהלך הגדרות ההתקנה- אנחנו מתקדמים על פי סדר הסעיפים בצד שמאל,
ורק לאחר מכן מתחילה ההתקנה עצמה שנמשכת כשעה.
בנוסף- אין טעם לנסות לשדרג התקנות קיימות לגרסה זו: זו גרסת התנסות, זה לא ילך, ואין בזה כל הגיון.
כאשר לשרת יש יותר ממעבד אחד הוא יכול לבחור לבצע פעולות במקביל וזה משפיע על בחירת ה-Execution Plan האופטימלי.
בפוסט הקודם הראיתי כיצד במקרה של Join בין טבלה לא מאונדקסת לטבלה עם Primary Key & Clustered Index המערכת תבחר בעיבוד במקביל:
Select *
From messages T1
Inner Join messages_ClstInd T2
On T1.message_id=T2.message_id
And T1.language_id=T2.language_id;
מאוד יכול להיות שחלק שמי שניסה את הקוד אצלו קיבל Execution Plan שונה לגמרי.
בשרת עליו הרצתי את הדוגמה יש שני מעבדים ולכן קיימת אופציה לעבודה במקביל:
Select cpu_count
From sys.dm_os_sys_info;
(שתי דרכים כיצד לבדוק כמה מעבדים יש)
מי שיש לו מעבד אחד על השרת יקבל כאמור תוכנית שונה, וניתן לראות זאת אם נגביל את המערכת בעזרת Hint.
ניצור שוב את הטבלאות הדרושות:
--1. No Indexes
Select *
Into messages
From sys.messages;
Go
--2. Clustered Primary Key on message_id,language_id
Select *
Into messages_ClstInd
From sys.messages;
Go
Alter Table messages_ClstInd Add Constraint PK_messages_ClstInd Primary Key Clustered (message_id,language_id);
Go
וכעת נריץ את השליפה הנ"ל עם ובלי Hint, תוך הפעלת Statistics IO ו-Execution Plan:
Set Statistics IO On;
Select *
From messages T1
Inner Join messages_ClstInd T2
On T1.message_id=T2.message_id
And T1.language_id=T2.language_id;
Select *
From messages T1
Inner Join messages_ClstInd T2
On T1.message_id=T2.message_id
And T1.language_id=T2.language_id
Option (MaxDop 1); --שימוש במעבד אחד בלבד
(97526 row(s) affected)
Table 'messages_ClstInd'. Scan count 3, logical reads 2985, physical reads 3, read-ahead reads 2722, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'messages'. Scan count 3, logical reads 2718, physical reads 97, read-ahead reads 2718, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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.
(1 row(s) affected)
(97526 row(s) affected)
Table 'messages_ClstInd'. Scan count 0, logical reads 433221, physical reads 138, read-ahead reads 13280, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'messages'. Scan count 1, logical reads 2718, physical reads 440, read-ahead reads 2703, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
כפי שניתן לראות- עם מעבד אחד המערכת אינה יכולה לעבוד במקביל, ובחרה ב-Nested Loops במקום במקום ב-Hash Match.
כדאי לשים לב בנוסף שבאייקונים ב-Execution Plan הראשון מופיעים עגולים צהובים קטנים עם שני חיצים המציינים שמדובר בעבודה במקביל (בנוסף לאייקונים הספציפיים של ה-Parallelism.
ה-Tale Scan על הטבלה ללא האינדקס זהה בשני המקרים, גם מבחינת הסטטיסטיקה.
הפניה לטבלה עם ה-Clustered Index נראית לכאורה יקרה יותר בשליפה הראשונה (Scan) וממוקדת וזולה יותר בשליפה השניה (Seek) אלא שזו טעות אופטית: בשליפה הראשונה המערכת פנתה פעם אחת לטבלה עם ה-Clustered Index ושלפה ממנה את כל 97526 השורות, ובשליפה השניה המערכת פנתה אליה 97526 פעמים ושלפה בכל פעם שורה אחת.
עיון בסטטיסטיקה ממחיש כמה ממעט Logical Reads התבצעו בה במקרה הראשון וכמה הרבה במקרה השני.
מסקנה- לא לנסות על שרת הייצור שימוש ב-Hint הנ"ל, אבל מי שמסיבות מוצקות יותר מעוניין לשנות את הגדרות השרת – למשל לקבוע שמתוך 4 מעבדים ירוצו במקביל 3 לכל היותר ואחד ישאר פנוי למשימות אחרות, יכול כך (תגידו לבוס שלכם שקראתם על זה באיזה בלוג באינטרנט ותמחקו את ההיסטוריה שלא יוכלו למצוא אותי):
Exec SP_Configure 'Show Advanced Options', 1;
Go
Reconfigure With Override;
Go
Exec sp_ Configure 'Max Degree of Parallelism', 3;
Go
Reconfigure With Override;
Go
האם ה-Execution Plan השני הוא זה שיקבל מי שיש לו מעבד אחד על השרת?
למרבה המזל- לא, ולא ברור לי למה המערכת בחרה בו.
ניסיתי על שרת עם מעבד אחד ולהלן התוצאות:
(76530 row(s) affected)
Table 'messages'. Scan count 1, logical reads 2016, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'messages_ClstInd'. Scan count 1, logical reads 2021, physical reads 0, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
הסטטיסטיקה נראית טוב יותר מהקודמת, אך זה נובע מכך שבשרת הראשון מותקן SQL Server 2008 בו יש 97526 שורות בטבלת הודעות השגיאה, ובשרת השני מותקן SQL Server 2005 בו יש 76530 שורות בלבד..
מה שחשוב יותר זה ה-Execution Plan השפוי המתבסס על מיון הטבלה ללא האינדקס וביצוע Merge Join בין שתי הטבלאות.
כיצד המערכת מבצעת Join ומה השפעת האינדקסים ומספר הרשומות על כך?
בהמשך לפוסט הקודם- אנתח מספר דוגמאות מבחינת התוכנית שהמערכת תבחר ולא מבחינת הביצועים, והן יתבססו על הטבלאות והאינדקסים שיצרנו בפעם הקודמת:
--1. No Indexes
Select *
Into messages
From sys.messages;
Go
--2. Clustered Primary Key on message_id,language_id
Select *
Into messages_ClstInd
From sys.messages;
Go
Alter Table messages_ClstInd
Add Constraint PK_messages_ClstInd Primary Key Clustered (message_id,language_id);
Go
--3. Unique Index on message_id,language_id (if a Primary Key already exists)
Select *
Into messages_Ind
From sys.messages;
Go
Create Unique Index Idx_messages_Ind On messages_Ind(message_id,language_id);
Go
--4. Unique Covered Index- Exists from SQL 2008 on
Select *
Into messages_CvdtInd
From sys.messages;
Go
Create Unique Index Idx_messages_CvdtInd On messages_CvdtInd(message_id,language_id) Include (severity, is_event_logged, [text]);
Go
שימוש ב-Merge Join:
Select *
From messages_ClstInd T2
Inner Join messages_CvdtInd T4
On T2.message_id=T4.message_id
And T2.language_id=T4.language_id;
Select T2.*
From messages_ClstInd T2
Inner Join messages_Ind T3
On T2.message_id=T3.message_id
And T2.language_id=T3.language_id;
Merge Join הוא ה-Join הפיזי היעיל ביותר, והוא מתבצע בדרך כלל בין שני סטים גדולים וממויינים. באופן ציורי ניתן לומר שהמערכת משולה במקרה זה לפקיד שמחזיק שתי רשימות ממויינות על שני דפים נפרדים (למשל- רשימת פריטים מהמלאי ורשימת פריטים שהוזמנו על ידי לקוח כשיש לבדוק מה נמצא ומה חסר במלאי), הוא שם את אצבע ימין בראש רשימה אחת ואת אצבע שמאל בראש רשימה שניה, וסורק במקביל את שתי הרשימות ומאתר התאמות ואי התאמות בין שתי השורות.
אם אחת הרשימות לא הייתה ממויינת- הוא לא היה יכול לנהוג כך, ואם אחת הרשימות הייתה ארוכה והשניה קצרה- יתכן והיה נוהג אחרת, ומכאן ניתן להבין שלא בכל מקרה ניתן להשתמש ב-Merge Join, ואין להבין מהעובדה שזה ה-Join הפיזי הכי יעיל שתמיד כדאי להשתמש בו, אלא שיש לשאוף לכך שהתנאים יאפשרו למערכת להשתמש בו, כמובן- במחיר סביר, שלא יצא ריווחנו בהפסדנו..
בשליפה הראשונה משתתפות שתי טבלאות שלאחת יש Clustered Index עם Primary Key על שתי העמודות המשתתפות ב-Join, ולשניה Covered Index על שתי העמודות המשתתפות ב-Join והוא כולל בנוסף את שתי העמודות האחרות.
במקרה זה נתוני הטבלה הראשונה ממויינים בגלל ה-Clustered Index, נתוני הטבלה השניה נשלפים מהאינדקס הממויין הכולל למעשה את כל הטבלה, ולכן המערכת בוחרת בצדק לבצע Merge Join.
השליפה השניה היא בין טבלה עם Clusterd Index לבין טבלה עם אינדקס רגיל שאינו Covered. יחד עם זאת- האינדקס שלה כולל את שתי העמודות הנדרשות, ושאר העמודות אינן נדרשות כי ה-Select כולל רק את הטבלה הראשונה; ולכן גם במקרה זה אין מניעה לבצע Merge Join.
סוגים שונים של Join פיזי הנבחרים בנסיבות שונות:
יש להבדיל בין Join לוגי ו-Join פיזי: לוגי יכול להיות Left Join או Inner Join וכו', ואילו פיזי יכול להיות Hash Match (יודגם בהמשך) או Nested Loops (יודגם להלן) או Merge Join (הודגם קודם); ובמילים אחריות – לוגי מתייחס ל"מה" ופיזי ל"איך", ומכיוון שטבעה של שפת SQL שהיא שפה דקלרטיבית (הצהרתית) ולא פרוצדוראלית כמו C / VB / Java – המפתח מצהיר "מה" הוא רוצה, והמערכת היא זו שמחליטה "איך".
Select *
From messages T1_1
Inner Join messages T2_2
On T1_1.message_id=T2_2.message_id
And T1_1.language_id=T2_2.language_id
Where T2_2.message_id=21627;
Select *
From messages T1
Inner Join messages_ClstInd T2
On T1.message_id=T2.message_id
And T1.language_id=T2.language_id
Where T1.severity=21;
Select *
From messages T1
Inner Join messages_ClstInd T2
On T1.message_id=T2.message_id
And T1.language_id=T2.language_id
Where T1.severity=20;
Select *
From messages T1
Inner Join messages_ClstInd T2
On T1.message_id=T2.message_id
And T1.language_id=T2.language_id;
בשליפה הראשונה מתבצע Join עצמי של הטבלה ללא האינדקסים, עם תנאי סלקטיבי על המופע השני של הטבלה (סלקטיבי כי יחזיר 11 שורות בלבד).
המערכת מספיק חכמה כדי להבין שאם התנאי הוא על message_id של המופע השני ועמודה זו משתתפת ב-Join – ניתן להפעיל אותו גם על המופע הראשון; אך אינה מספיק חכמה כדי להבין שאם מדובר ב-Join עצמי – לא צריך לבצע Scan על שתי הטבלאות..
בכל מקרה- מתבצע Scan Table מלא על המופע הראשון של הטבלה והסט של השורות העומדות בתנאי נשמר בטבלה זמנית ב-TempDB, ולאחר מכן מתבצע Scan Table על המופע השני של הטבלה ועבור כל שורה שעונה לתנאי מתבצע חיפוש בטבלה הזמנית אחר שורות מתאימות. שיטה זו לביצוע Join פיזי (עבור כל שורה מתאימה בצד של הטבלה הגדולה מחפשים שורה מתאימה בטבלה הקטנה) נקראת Nested Loops.
בשליפה השניה מתבצע Join בין הטבלה ללא האינדקסים והטבלה עם ה-Clustered Index. גם כאן המערכת מעדיפה לבצע Nested Loops מכיוון שבטבלה הראשונה יש לבצע Table Scan כבד ובשניה Seek קל ומהיר; ולכן על כל שורה מתאימה בטבלה חסרת האינדקסים מתבצע Seek בטבלה עם ה-Clustered Index. החלטה זו של המערכת מתבססת על כך שלא צפויות לחזור הרבה שורות מהטבלה ללא האינדקס שעליה מופעל התנאי (749.851 על סמך הסטטיסטיקה) ושלכל שורה מהטבלה הראשונה תותאם שורה מהשניה כי תנאי ה-Join זהים ל-Primary Key שלה.
בשליפה השלישית שדומה לשניה אך עם תנאי סלקטיבי פחות שצפוי להחזיר יותר שורות – המערכת מוותרת על אופציית ה-Nested Loops ומעדיפה למיין את השורות המתאימות מהטבלה ללא האינדקסים, ואז לבצע Merge Join בין שתיהן.
כלומר- קודם המערכת ציפתה לכ-750 שורות ולכן הייתה מוכנה לבצע 750 פעם Seek בטבלה השניה,
אלא שכעת היא מצפה לכ-1093 שורות (1093.44 ליתר דיוק) ולכן מעדיפה כבר לבצע מיון שלהן ו-Merge עם הטבלה המאונדקסת.
בשליפה הרביעית שדומה לשניה ולשלישית אך ללא תנאים – היינו אולי מצפים שהיא תבצע מיון ו-Merge Join כמו בשלישית, אלא שהמיון של קרוב ל-100,000 רשומות כבד מדי ולכן היא מעדיפה לבצע Hash Match: אלגוריתם שפרטיו המדוייקים לא ידועים, אך הוא כרוך בחלוקה של כל טבלה למספר תתי טבלאות בהתאמה וביצוע Join בין כל תת טבלה מטבלה אחת עם תת הטבלה המתאימה לה מטבלה שניה. ה-Parallelism הוא חלוקה של הטבלאות כך שניתן יהיה לבצע את תתי ה-Join האלו במקביל תוך הפנייתם למעבדים השונים של השרת, ואיחוד התוצאות לסט אחד לאחר מכן.
מכיוון שבכל ארבע השליפות האלו לא נעשה שימוש באינדקסים של הטבלאות- בכל אחד מה-Execution Plans מופיעה המלצה להוספת אינדקס.
Join עם Non Covered Index
Select *
From messages_ClstInd T2
Inner Join messages_Ind T3
On T2.message_id=T3.message_id
And T2.language_id=T3.language_id
Where T3.message_id=21627;
Select *
From messages_ClstInd T2
Inner Join messages_Ind T3
On T2.message_id=T3.message_id
And T2.language_id=T3.language_id
Where T3.Language_id=1028;
בשליפה הראשונה יש תנאי סלקטיבי שמחזיר מעט שורות על העמודה הראשית ב-Non Covered Index,
ובשליפה השניה יש תנאי לא סלקטיבי שמחזיר הרבה שורות על העמודה המשנית ב-Non Covered Index.
בשני המקרים החיפוש בטבלה הראשונה עם ה-Clustered Index הוא אחר שורות שעונות על התנאי, למרות שהתנאי מופנה לטבלה השניה: מכיוון שה-Join בין שתי הטבלאות הוא על העמודות האלו, המערכת מבינה שיש להפעיל את התנאי על שתיהן.
יחד עם זאת כדאי לשים לב להבדל- בשליפה הראשונה מתבצע Clustered Index Seek והמערכת ניגשת ישר לשורות הרלוונטיות בטבלת ה-Clustered Index (כי המיון הראשי הוא לפי העמודה המפולטרת),
ואילו בשליפה השניה המערכת מבצעת Clustered Index Scan ועוברת על כל הטבלה (כי המיון הראשי אינו לפי הטבלה המפולטרת).
ומה עם הטבלה עם ה-Non Covered Index שעליה מופעל התנאי ישירות? בשליפה הראשונה הסלקטיבית שצפויה להחזיר מעט שורות- מתבצעת שליפה מהאינדקס (Index Seek שכולל כאמור רק שתי עמודות), לאחר מכן Nested Loops עם מה שנשלף מטבלת ה-Clustered Index, ורק בסוף – Look Up עבור שתי העמודות הנוספות שלא נכללו באינדקס: המערכת מניחה כנראה שחלק מהרשומות עלולות להתנפות ב-Join ולכן אין טעם לטרוח ולבצע עבורן Look Up כבד מראש.
בשליפה השניה הלא סלקטיבית שמחזירה הרבה שורות ומופעלת על העמודה המשנית באינדקס – המערכת מוותרת על השימוש באינדקס, מבצעת Table Scan, מגיעה למסקנה שכדאי להשקיע במיון כדי לאפשר Merge Join וכך גם נוהגת.
סיכום
ב-Join בין שתי טבלאות גדולות ממויינות – המערכת תבצע Merge Join שהוא ה-Join הפיזי הזול יותר.
ב-Join בין שתי טבלאות גדולות שלפחות אחת מהן אינה ממויינת – המערכת תיאלץ לבצע Hash Match שהוא ה-Join הפיזי הכבד יותר.
במקרי ביניים ("גדול" ו-"קטן" אלו מושגים יחסיים..), כשיש טבלה אחת לא ממויינת והיא אינה גדולה מדי, המערכת תחליט אם כדאי למיין אותה ולבצע Merge Join או לא למיין ולבצע Hash Match.
כשיש טבלה אחת גדולה ואחת קטנה – המערכת תבצע בדרך כלל Nested Loops: Scan על הגדולה, ולכל שורה מתאימה שתמצא – תבצע Seek בקטנה (הקטנה צריכה להיות ממויינת).
לכל זה נוספים עוד שיקולים ומקרי ביניים שאינם חד משמעיים-
האם כדאי להשתמש באינדקס לביצוע חיפוש בעמודה שאינה ראשית?
האם יש שימוש בעמודות הלא מאנדקסות, ואם כן- האם כדאי לבצע עבורן Look Up?
וכך הלאה..
כיצד המערכת מחפשת ערכים בטבלה ומה השפעת האינדקסים על כך?
מדובר בנושא די בסיסי בתחום, והפוסט הזה מוקדש למי שעדיין אינו מכיר.
טבלת המערכת הגדולה ביותר היא sys.messages הכוללת את הודעות השגיאה של המערכת (קרוב ל-100,000 שורות בגרסת 2008 ועוד היד נטוייה בגרסאות הבאות):
Select *
From sys.messages;
ניצור מספר עותקים ממנה ונאנדקס כל אחד בדרך אחרת,
ונבדוק כיצד מתבצע החיפוש בכל פעם.
נתחיל עם עותק ללא אינדקסים כלל:
--1. No Indexes
Select *
Into messages
From sys.messages;
Go
--2. Clustered Primary Key on message_id,language_id
Select *
Into messages_ClstInd
From sys.messages;
Go
Alter Table messages_ClstInd
Add Constraint PK_messages_ClstInd Primary Key Clustered (message_id,language_id);
Go
--3. Unique Index on message_id,language_id (if a Primary Key already exists)
Select *
Into messages_Ind
From sys.messages;
Go
Create Unique Index Idx_messages_Ind On messages_Ind(message_id,language_id);
Go
--4. Unique Covered Index- Exists from SQL 2008 on
Select *
Into messages_CvdtInd
From sys.messages;
Go
Create Unique Index Idx_messages_CvdtInd On messages_CvdtInd(message_id,language_id) Include (severity, is_event_logged, [text]);
Go
וכעת נפעיל את הסטטיסטיקה ואת ה-Execution Plan ונבצע שליפה זהה בארבעתן:
Set Statistics IO On;
Go
Select *
From messages
Where message_id=2786;
Select *
From messages_Ind
Where message_id=2786;
Select *
From messages_ClstInd
Where message_id=2786;
Select *
From messages_CvdtInd
Where message_id=2786;
(11 row(s) affected)
Table 'messages'. Scan count 1, logical reads 2719, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(11 row(s) affected)
Table 'messages_ClstInd'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(11 row(s) affected)
Table 'messages_Ind'. 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.
(1 row(s) affected)
(11 row(s) affected)
Table 'messages_CvdtInd'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
שליפה 1- ללא אינדקס. במקרה זה המערכת נאלצת לבצע Scan Table מלא על כל הטבלה, ולא פלא שמבחינת העלות זה 98% ן- logical reads 2719.
שליפה 2- Primary Key שהוא גם Clustered Index. זהו החיפוש הכי יעיל במקרה זה- מתבצע Index Seek ישר לשורות הרלוונטיות ללא בזבוז משאבים על חיפושים מיותרים. העלות אפסית 0% ו- logical reads 4.
שליפה 3- Unique Index. גם כאן מגיעים לערכים המבוקשים ביעילות על ידי Index seek, אך אם רוצים לשלוף גם את העמודות שאינן באינדקס (כלומר- גם את is_evrnt_logged & text) יש לפנות מהאינדקס לטבלה עצמה על ידי Look Up שמייקר את השליפה ל-2% ו- logical reads 13.
אם היינו שולפים רק את העמודות המאנדקסות לא היה צורך ב-Look Up.
העובדה שהאינדקס הוא Unique מונעת הכנסת ערכים כפולים, אך אינה משפיעה על החיפוש במקרה זה.
אם עמודה message_id הייתה פחות סלקטיבית והחיפוש היה מחזיר הרבה יותר ערכים- יתכן שלמערכת לא היה כדאי לבצע הרבה Look Ups והייתה מעדיפה לבצע Table Scan ולהתעלם מהאינדקס.
שליפה 4- Unique Covered Index. החל מגרסת 2008 ניתן לכלול באינדקס עמודות נוספות מבלי לאנדקס אותן, כלומר- האינדקס לא עוזר לבצע בהן חיפוש, אך הוא מונע את הצורך ב-Look Up כמו קודם. במקרה זה הביצועים הם כמו עם Clustered Index מתבצע Index Seek הישר לשורות הרלוונטיות, ואין צורך ב-Look Up. המסקנה היא ש-Covered Index דומה ל-Clustered Index מבחינת הביצועים אך ללא מגבלה על הכמות (Clustered Index – כמו אמא – יש רק אחד..), אבל יש לזה מחיר של הכפלת נפח האחסון ופעולות ה-DML (הוספה, עדכון, מחיקה).
מה יקרה אם החיפוש לא יהיה ממוקד (הנ"ל החזיר 11 שורות)? ננסה לפנות לטווח גדול יותר:
Select *
From messages
Where message_id Between 15000 And 16000;
Select *
From messages_ClstInd
Where message_id Between 15000 And 16000;
Select *
From messages_Ind
Where message_id Between 15000 And 16000;
Select *
From messages_CvdtInd
Where message_id Between 15000 And 16000;
(5577 row(s) affected)
Table 'messages'. Scan count 1, logical reads 2719, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(5577 row(s) affected)
Table 'messages_ClstInd'. Scan count 1, logical reads 116, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(5577 row(s) affected)
Table 'messages_Ind'. Scan count 1, logical reads 2719, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(5577 row(s) affected)
Table 'messages_CvdtInd'. Scan count 1, logical reads 120, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
שליפה 1 (ללא אינדקס) ביצעה כצפוי Table Scan, אבל גם שליפה 3 (Non Covered Index) בחרה בדרך זו כדי להימנע מביצוע 5577 Look Ups. אינדקס שאינו Covered מועיל כשנשלפות מעט שורות, או כשה-Select הוא על עמודות הנכללות בו. אם נבצע את שליפה 3 האחרונה אבל נפנה רק לשתי העמודות שבאינדקס- הוא יבצע Index Scan יעיל כמו בשליפה 2 ושליפה 3.
Select message_id,
language_id
From messages_Ind
Where message_id Between 15000 And 16000;
(5577 row(s) affected)
Table 'messages_Ind'. 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.
(1 row(s) affected)
שליפה 2 (Clustered Index ) ושליפה 3 (Covered Index) מבצעות גם כאן Index Seek כמו קודם.
מה יקרה אם החיפוש יהיה על עמודה שאינה מאונדקסת או על עמודה משנית באינדקס?
Select *
From messages
Where language_id=1040;
Select *
From messages_ClstInd
Where language_id=1040;
Select *
From messages_Ind
Where language_id=1040;
Select *
From messages_CvdtInd
Where language_id=1040;
(8866 row(s) affected)
Table 'messages'. Scan count 1, logical reads 2719, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(8866 row(s) affected)
Table 'messages_ClstInd'. Scan count 1, logical reads 2726, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(8866 row(s) affected)
Table 'messages_Ind'. Scan count 1, logical reads 2719, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(8866 row(s) affected)
Table 'messages_CvdtInd'. Scan count 1, logical reads 2719, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
במקרה זה המערכת בחרה בכל ארבעת המקרים לבצע Scan מלא. כדאי לשים לב ש-Table Scan ו-Clustered Index Scan הם היינו הך במקרה זה מכיוון ש-Clustered Index אינו אינדקס הנבנה על הטבלה, אלא הטבלה עצמה; ולכן Clustered Index Scan אינו נחשב ל-"שימוש באינדקס", בניגוד ל-Clustered Index Seek.
האם המערכת ויתרה על השימוש באינדקסים בגלל ש-language_id הוא עמודה משנית באינדקס או בגלל שהשליפה מחזירה מספר רב של שורות (8866)? נעשה נסיון פשוט: ניצור טבלה נוספת, נבנה עליה אינדקס פשוט כמו זה של שליפה 2 אבל עם סדר עמודות הפוך, ונבצע עליו את השליפה הראשונה שמחזירה מספר מועט של שורות אבל פונה כעת לעמודה המשנית בטבלה:
Select *
Into messages_Ind2
From sys.messages;
Create Unique Index Idx_messages_Ind2 On messages_Ind2(language_id,message_id);
Go
Select *
From messages_Ind2
Where message_id=2786;
(11 row(s) affected)
Table 'messages_Ind2'. Scan count 1, logical reads 218, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
הפעם המערכת כן מבצעת Index Scan וניגשת ל-message_id הרלוונטי בכל language_id בנפרד, ואת המידע החסר על שתי העמודות החסרות היא משלימה על ידי Look Up. כלומר- גם בשליפה עם תנאי על עמודה שאינה ראשית באינדקס- יתכן והמערכת תחליט להשתמש בכל זאת באינדקס, בעיקר עם הסלקטיביות גבוהה (צפויים לחזור מעט נתונים יחסית).
דבר נוסף שכדאי לשים אליו לב הוא שהמערכת ממליצה להוסיף אינדקס על message_id ומבטיחה שיפור נאה בביצועים..
את קבצי ה-Execution Plan ניתן להוריד דרך צילומי המסך שבפוסט.
נניח שאנחנו מחפשים עובד שיש לו גם הכשרה ב-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
More Posts
Next page »