בפורום בסיסי נתונים בתפוז תהינו האם האופרטורים In & Exists יעילים, והאם נעשה בהם שימוש "חכם".
כלומר- כשאנחנו מחפשים שורות בעלות Foreign Key בצד ה-1 שיש להן שורות תלויות בצד ה-N (הרבים) אפשר לבדוק בעזרת Join בין שתי הטבלאות, אלא שיש כאן בזבוז- המערכת תחזיר את כל ההתאמות של כל שורה (למשל עבור מוצר X את כל המכירות שלו..) כשכל מה שאנחנו רוצים זה לקבל תשובה של כן/לא..
הדרך הנכונה לעשות זאת היא בעזרת Exists או In, כשהדעת נותנת שלאחר שנמצאה התאמה אחת- המערכת תפסיק לחפש ולא תמשיך לבדוק לחינם את כל הטבלה שבצד הרבים; אך האם כך הם פני הדברים? כדאי לבדוק!
(כדי לא להחזיק אתכם במתח- אין תגליות מרעישות והבדיקה מוכיחה שהמערכת בודקת בצורה חכמה)
ניצור טבלת מספרים עד מיליון, עם Clustered Index כדי להבטיח שתישמר ממויינת:
Use tempdb;
Go
If Object_Id('T_Misparim') Is Not Null Drop Table T_Misparim;
Go
With T As
(Select 1 Mispar
Union All
Select Mispar+1
From T
Where Mispar<1000000)
Select *
Into T_Misparim
From T
option (MaxRecursion 0);
Go
Create Unique Clustered Index Idx_T_Misparim On T_Misparim(Mispar);
Go
כעת נוסיף עמודה נטולת אינדקס לטבלה, ונעדכן אותה כך שיופיעו בה המספרים כמו בעמודת המספרים הקודמת, חוץ מ-999999 שיוחלף בערך 1- (הסבר בהמשך):
Alter Table T_Misparim Add Bdika Int;
Go
Update T_Misparim
Set Bdika=Case When Mispar=999999 Then -1 Else Mispar End
Go
וכעת נריץ את הקוד הבא (ליתר בטחון אני מריץ פעמיים ומציג את נתוני הריצה השניה לאחר שהנתונים נטענו ל-Cash):
Set Statistics Time On
Set Statistics IO On
Print '1-----------------------------------------------------------------------------------------------------------------------------------------------------------'
Select 1 N
Where 1 In (Select Sqrt(Bdika) From T_Misparim)
Print
'500---------------------------------------------------------------------------------------------------------------------------------------------------------'
Select 500 N
Where 500 In (Select Sqrt(Bdika) From T_Misparim)
Print '1000--------------------------------------------------------------------------------------------------------------------------------------------------------'
Select 1000 N
Where 1000 In (Select Sqrt(Bdika) From T_Misparim)
Print '------------------------------------------------------------------------------------------------------------------------------------------------------------'
Set Statistics IO Off
Set Statistics Time Off
בשליפה הראשונה האופרטור In מחפש מספר שהשורש שלו הוא 1, הוא אמור למצוא אותו מיד בהתחלה (השימוש בשורש נועד למנוע מהמערכת להיעזר בסטטיסטיקות ואינדקסים על העמודה), ואנחנו רוצים לוודא שהחיפוש אמנם יסתיים מייד.
בשליפה השניה אמור לקחת לו הרבה יותר זמן למצוא את המספר 250,000 שהשורש שלו הוא 500.
בשליפה השלישית הוא אמור להיכשל מכיוון שלקראת הסוף הוא יפול כשינסה לחשב את שורש של -1 (זה יאשר את התחזית שלנו ששני החיפושים הקודמים לא יכשלו כי הם יעצרו לפני שיגיעו למספר השלילי).
התוצאות שקיבלתי:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
1————————————————————————————————————————————————–
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(1 row(s) affected)
Table 'T_Misparim'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
500———————————————————————————————————————————————-
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(1 row(s) affected)
Table 'T_Misparim'. Scan count 1, logical reads 812, physical reads 0, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 76 ms.
1000——————————————————————————————————————————————–
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Msg 3623, Level 16, State 1, Line 13
A domain error occurred.
אפשר לראות שהחיפוש הראשון היה מהיר משמעותית מהשני וצרך פחות משאבים, ומכאן שהוא הסתיים הרבה יותר מהר;
ורק החיפוש השלישי נכשל לאחר שהגיע למספר השלילי (שני הראשונים עצרו לפני כן).
אם נבצע אותה בדיקה על שליפות העושות שימוש באופרטור Exists נקבל תוצאות דומות:
Set Statistics Time On
Set Statistics IO On
Print '1-----------------------------------------------------------------------------------------------------------------------------------------------------------'
Select 1 N
Where Exists (Select * From T_Misparim Where Sqrt(Bdika)=1)
Print '500---------------------------------------------------------------------------------------------------------------------------------------------------------'
Select 500 N
Where Exists (Select * From T_Misparim Where Sqrt(Bdika)=500)
Print '1000--------------------------------------------------------------------------------------------------------------------------------------------------------'
Select 1000 N
Where Exists (Select * From T_Misparim Where Sqrt(Bdika)=1000)
Print '------------------------------------------------------------------------------------------------------------------------------------------------------------'
Set Statistics IO Off
Set Statistics Time Off
והתוצאות די דומות:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
1————————————————————————————————————————————————–
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(1 row(s) affected)
Table 'T_Misparim'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
500———————————————————————————————————————————————-
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(1 row(s) affected)
Table 'T_Misparim'. Scan count 1, logical reads 812, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 70 ms.
1000——————————————————————————————————————————————–
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Msg 3623, Level 16, State 1, Line 14
A domain error occurred.
יש כמה הבדלים לא משמעותיים בין ריצת ה-In וריצת ה-Exists, וה-Execution Plans שלהם זהים:
לסיום כדאי לוודא שפקודות ה-Set Statistics נוטרלו (השגיאה היזומה מנעה מהריצה להגיע אליהן):
Set Statistics IO Off
Set Statistics Time Off
הבלוג של גרי רשף 