en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
דצמ11

Written by: ronen ariely
11/12/2010 22:21 RssIcon

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

·         Exists

·         Top 1

·         In

איזה שיטה יעילה יותר והאם נעשה בהם שימוש "חכם" זהה, כשהדעת נותנת שלאחר שנמצאה התאמה אחת המערכת תפסיק לחפש ולא תמשיך לבדוק לחינם את כל הטבלה שבצד הרבים. על כך לא היה חילוקי דעות, אך האם הביצוע יהיה זהה בכל שיטה? כדאי לבדוק!

והעיקר המסקנה... האם הדרך הנכונה לעשות זאת היא בעזרת Exists או In ?!?

ניתן את הפידבק לגרי רשף שעשה את תחילת הבדיקה עבורנו בבלוג שהעלה בתאריך 08/12/2010

את הבלוג ניתן למצוא בקישור הבא

הבדיקה שהועלתה בבלוג הקודם ומובאת כאן בהמשך מראה מצב מסוים בו היעילות של השיטות השונות זהה כמעט לחלוטין (נמצאה עדיפות קטנה בזמנים לטובת שימוש ב EXIST בבדיקות שהועלו שם). המסקנה אליה הגיע גרי הייתה: אין תגליות מרעישות והבדיקה מוכיחה שהמערכת בודקת בצורה חכמה.

אך האם בדיקה זו מספקת?!? האם מסקנה מבחינה מסוימת אכן מהווה תשובה לשאלה?!? התשובה היא שממש לא.

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

בלוג קצר זה מהווה המשך לדיון ולבלוג הקודם ומביא דוגמה מסוימת (ושוב אני מדגיש כי מדובר על דוגמה ספציפית) בה ניתן לראות בבירור הבדלים מאוד משמעותיים בשימוש ב EXIST לעומת שימוש ב IN.

המסקנה היחידה שאנו יכולים לראות כרגע היא שמעבר לכללי אצבע הרי שככל ששאילתה מורכבת יותר הרי שחובה עלינו לבדוק את השאילתה גם בIN וגם בEXISTS ולבחור את השיטה הטובה מהשניים ומדי פעם לבדוק שוב את הביצועים (יכול להיות שינוים כתוצאה משינוים סטטיסטיים).

 

בדיקה מהנה

* הדוגמה נכתבה עבור שימוש במסד הנתונים AdventureWorks 2008R2. יש לשים לב שאם עושים שימוש במסד נתונים של גרסאות קודמות יש לשנות מעט את הסקריפט בהתאם (שמות השדות בטבלאות שונו ולכן הסקריפט יעלה הודעת שגיאה אם יורץ בגרסה לא מתאימה).

** דוגמה זו נכתבה על ידי גיא פרייז, מרצה בקורסים של SQL למתקדמים

use AdventureWorks

go


Set Statistics Time On

set statistics io on

set ansi_nulls off

------------------------------------------------- IN

SELECT * FROM Sales.SalesPerson

WHERE SalesPersonID

      NOT IN(SELECT SalesPersonID

                  FROM Sales.SalesOrderHeader

                  GROUP BY SalesPersonID HAVING COUNT(*) > 150)

------------------------------------------------- IN

 

יש להריץ את השאילתה מספר פעמים על מנת לוודא שהנתונים נכנסו ל Cash

תוצאת הרצת השאילתה:

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 

(7 row(s) affected)

Table 'SalesPerson'. 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 'SalesOrderHeader'. Scan count 1, logical reads 57, 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 = 10 ms.

עתה נריץ קוד דומה עם שימוש ב EXIST

use AdventureWorks

go

 

Set Statistics Time On

set statistics io on

set ansi_nulls off

 

------------------------------------------------- EXISTS

SELECT * FROM Sales.SalesPerson s

WHERE

      NOT EXISTS(SELECT SalesPersonID

                        FROM Sales.SalesOrderHeader o

                        WHERE s.SalesPersonID = o.SalesPersonID

                        GROUP BY SalesPersonID HAVING COUNT(*) > 150)

------------------------------------------------- EXISTS

שוב לא נשכח להריץ מספר פעמים ותוצאת הרצת השאילתה:

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 

(7 row(s) affected)

Table 'SalesOrderHeader'. Scan count 17, logical reads 41, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesPerson'. 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.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 1 ms.

 

וניתן לראות הבדלים משמעותיים מאוד של פי 10 לטובת השימוש ב EXIST (במסד נתונים חי נמצאו הבדלים של מעל דקה לעומת 3 שניות של הרצת שאילתות דומות)

 

* יש לציין שוב שמדובר באופטימייזר של השרת שבונה את תוכנית ההרצה ויכולים להיות מצבים בהם ההבדלים יהיו גדולים יותר לכאן או לכאן או שלא יהיו הבדלים כמעט (כמו במקרה שנראה בבלוג הקודם)

 ** קוד עבור גרסת AdventureWorks2008 (לא נבדק על ידי)

use AdventureWorks2008

go

set statistics Time On

set statistics io on

set ansi_nulls off

SELECT * FROM Sales.SalesPerson WHERE BusinessEntityID NOT IN

(SELECT SalesPersonID FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID HAVING COUNT(*) > 150)


SELECT * FROM Sales.SalesPerson s WHERE NOT EXISTS

(SELECT SalesPersonID FROM Sales.SalesOrderHeader o

WHERE s.BusinessEntityID = o.SalesPersonID

GROUP BY SalesPersonID HAVING COUNT(*) > 150)

 

 

 

 

דוגמת הקוד בבדיקות הקודמות:

יש לשים לב שבדוגמה זו נלקחה טבלה פשוטה מאוד עם נתונים מספריים בלבד. בטבלה נרשמו כמליון רשומות עם 2 טורים המכילים מספרים מ 1 ל 999999. הטבלה נבנתה במיוחד לצורך הבדיקה.

Use qq;

Go

 

If Object_Id('T_Misparim1') Is Not Null Drop Table T_Misparim1;

Go

 

With T As

      (Select 1 Mispar

      Union All

      Select Mispar+1

      From T

      Where Mispar<1000000)

Select *

Into T_Misparim1

From T

option (MaxRecursion 0);

Go

 

Create Unique Clustered Index Idx_T_Misparim1 On T_Misparim1(Mispar);

Go

 

Alter Table T_Misparim1 Add Bdika Int;

Go

 

Update T_Misparim1

Set Bdika=Case When Mispar=999999 Then -1 Else Mispar End

Go

 

---------------------------------------------------- USING IN

Set Statistics Time On

Set Statistics IO On

Print '1-------------'

Select 1 N

Where 1 In (Select Sqrt(Bdika) From T_Misparim1)

Print

'500-------------'

Select 500 N

Where 500 In (Select Sqrt(Bdika) From T_Misparim1)

Print '1000--------'

Select 1000 N

Where 1000 In (Select Sqrt(Bdika) From T_Misparim1)

Print '-------'

Set Statistics IO Off

Set Statistics Time Off

--------------------------------------------------------USING EXIST

Set Statistics Time On

Set Statistics IO On

Print '1-----------------'

Select 1 N

Where Exists (Select * From T_Misparim1 Where Sqrt(Bdika)=1)

Print '500---------------'

Select 500 N

Where Exists (Select * From T_Misparim1 Where Sqrt(Bdika)=500)

Print '1000--------------'

Select 1000 N

Where Exists (Select * From T_Misparim1 Where Sqrt(Bdika)=1000)

Print '------------------'

Set Statistics IO Off

Set Statistics Time Off

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