הבלוג של גרי רשף

08/12/2010

כיצד מטופלים האופרטוריםIn & Exists?

Filed under: Uncategorized — תגיות: , , , — גרי רשף @ 22:37

בפורום בסיסי נתונים בתפוז תהינו האם האופרטורים 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 שלהם זהים:

clip_image002

לסיום כדאי לוודא שפקודות ה-Set Statistics נוטרלו (השגיאה היזומה מנעה מהריצה להגיע אליהן):

Set Statistics IO Off

Set Statistics Time Off

מודעות פרסומת

4 תגובות »

  1. בלוג המשך עם דוגמה בה נראים הבדלים משמעותיים ניתן לראות כאן:
    http://ariely.info/dnn/Blog/tabid/83/EntryId/27/-exist-in-top-1.aspx

    תגובה של רונן אריאלי — 11/12/2010 @ 22:30

    • ראשית – כל הכבוד על ההשקעה! הרבה יותר קל לכתוב תשובות בעל פה ולהסתמך על ידע אישי מאשר לבדוק ולהציג את התוצאות בצורה מסודרת.

      רציתי לכבד את הבלוג (שלך?) בתגובה שם אך לא מצאתי כיצד להגיב למרות שנרשמתי כחוק.

      במקרה זה נראה לי שפספסת: שתי השאילתות עושות דברים שונים ומחזירות תוצאות שונות.

      שאילתת ה-IN אינה מחזירה תוצאות וזה בגלל ערכי ה-Null שבטבלת ה-Headers אשר משבשות את השאילתה. עד כאן – אחת אפס לטובת ה-Exists שאינו נופל בגלל הבעייה הזו (הביאו את הדבר לתשומת ליבי בבלוג שלי).

      חוץ מזה- השאילתות אינן עושות אותו הדבר: שאילתת ה-In סורקת פעם אחת את הטבלה ומבצעת Group By לפי SalesPersonID (כולל ערכי Null),

      ואילו שאילתת ה-Exists מבצעת Seek עבור כל אחד מהערכים בטבלת Sales Person שזה לכאורה בזבזני אבל בגלל שיש אינדקס המאפשר seek על הטבלה ובגלל הכמות הגדולה של ערכי Null שהיא נמנעת מהם – יוצא הפסדה בשכרה.

      1. אם מראש היינו מגבילים את ה-In ל-Is Not Null (בשאילתת המשנה) או מתנים את השאילתה הפנימית בחיצונית כמו ב-Exists התוצאות היו אחרות.

      2. אצלי זמן הריצה של שתי האפשרויות היה זהה, ה-Execution Plan של ה-Exist טיפה יותר יעיל, וה-Scan & Logical Reads אינם חד משמעיים מהסיבות שנמנו קודם.

      3. בסה"כ מדובר כאן במקרה מאוד ספציפי בגלל כמות ה-nulls & קיום האינדקס & השאילתה המורכבת, בשעה שראוי לבדוק מקרים כלליים ופשוטים; ולהפתעתי יש שם הבדלים בביצועים גם כשה-Execution Plans זהים מבחינת המבנה, ואני מנסה לבדוק את זה.

      תגובה של גרי רשף — 12/12/2010 @ 09:33

  2. צריך להזהר באיך משתמשים באופרטורים האלה ומה התוצאות שלהם :
    JOIN vs IN vs EXISTS – the logical difference
    http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx
    אני חושב שהנושא עלה בעבר ב sqlserver.co.il

    תגובה של פלג — 08/12/2010 @ 22:53

    • תודה על ההפניה- באמת יש בעייה ב-Not In כשיש NULL-ים,
      ובנוסף- Not Exists יעיל אז יותר ולא כל כך ברור לי למה.
      אבדוק.

      תגובה של גרי רשף — 09/12/2010 @ 08:38


RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: