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

17/06/2010

האופרטורים All, Any

Filed under: Uncategorized — תגיות: , , , — גרי רשף @ 12:52

שני האופרטורים הנ"ל הם בבחינת Nice to have: ניתן להסתדר בלעדיהם, אבל תמיד טוב להכיר אופציות נוספות, מה גם שלרוב הם מבטיחים ביצועים לא פחות טובים מהחלופות, ובסוף אף אציג דוגמה לכך.
האופרטורים הנ"ל בדומה לאופרטורים כמו Exists ו-In משווים סט אחד לשני ומציגים מהראשון רק את מה שעומד בתנאי, כאשר All מחייב שהתנאי יהיה נכון לכל המקרים בסט השני, ו-Any מחייב שהתנאי יהיה נכון לפחות למקרה אחד (הערה- האופרטור Any זהה ל-Some).
למשל- להלן טבלת תלמידים וטבלת ציונים:

Create Table T_Talmidim(Shem Varchar(Max));

Go

 

Insert Into T_Talmidim

Select    'Adi'

Union All

Select    'Beni'

Union All

Select    'carmel'

Union All

Select    'Dana'

Union All

Select    'Eli';

Go

 

Create Table T_Tziunim(Shem Varchar(Max),

                    Miktzoa Varchar(Max),

                    Tziun Int);

Go

 

Insert Into T_Tziunim

Select    'Adi','Math',60

Union All

Select    'Adi','Literature',40

Union All

Select    'Beni','Literature',80

Union All

Select    'Dana','Math',75

Union All

Select    'Dana','Literature',85

Union All

Select    'Dana','Economics',85

Union All

Select    'Eli','Literature',35

Union All

Select    'Eli','Economics',45;

Go

ניתן לראות שעדי הצליחה בחלק מהמקצועות ובחלק נכשלה,

בני הצליח במקצוע היחיד שלו,

כרמל אין לה אף ציון,

דנה הצליחה בכולם,

ואלי נכשל בכולם.

אנחנו מעוניינים לקבל את רשימת התלמידים שהצליחו בכל המקצועות (כלומר- קיבלו ציון מ-55 ומעלה), ולשם כך נשתמש באופרטור All:

Select    *

From    T_Talmidim Tl

Where    55<=All(Select Tziun

            From    T_Tziunim Tz

            Where    Tz.Shem=Tl.Shem);

את השליפה הנ"ל ניתן לכתוב גם ללא שימוש ב-All, מספר אפשרויות לדוגמה:

Select    *

From    T_Talmidim Tl

Where    Not Exists (Select    *

                    From    T_Tziunim Tz

                    Where    Tz.Shem=Tl.Shem

                            And Tz.Tziun<55);    

 

Select    *

From    T_Talmidim

Except

Select    Shem

From    T_Tziunim

Where    Tziun<55;

השוואה מבחינת ה-Execution Plan מלמדת של-All יש ביצועים לא פחות טובים משל השאר (All>= אם ליישם את הנושא הנדון..), אם כי מדובר בטבלאות קטנות מאוד ונטולות אינדקסים.

כדאי לשים לב שאנחנו מקבלים ברשימה גם את כרמל (מכיוון שכל הציונים שלה מעל 55..), אבל בשליפה הבאה שלכאורה זהה לנ"ל היא לא תופיע, וכדאי להיות ער למקרי קצה כאלה:

Select    *

From    T_Talmidim Tl

Where    55<=(Select    Min(Tziun)

            From    T_Tziunim Tz

            Where    Tz.Shem=Tl.Shem);

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

Select    *

From    T_Talmidim Tl

Where    55>Any(Select Tziun

            From    T_Tziunim Tz

            Where    Tz.Shem=Tl.Shem);

את השליפה הנ"ל ניתן לכתוב גם ללא שימוש ב-Any, מספר אפשרויות לדוגמה:

Select    *

From    T_Talmidim Tl

Where    55>(Select    Min(Tziun)

            From    T_Tziunim Tz

            Where    Tz.Shem=Tl.Shem);

 

Select    *

From    T_Talmidim Tl

Where    Exists (Select    *

                From    T_Tziunim Tz

                Where    Tz.Shem=Tl.Shem

                        And Tz.Tziun<55);    

 

Select    *

From    T_Talmidim Tl

Where    Shem In (Select    Shem

                From    T_Tziunim

                Where    Tziun<55);

גם במקרה זה ה-Execution Plan מגיב בחיוב לשימוש באופרטור.

אפשר לראות ששני האופרטורים תחליפיים, שכן כל אחד יכול להיות מוחלף על ידי האחר בצירוף Not והיפוך ההשוואה, כלומר- שני אלה זהים (וגם ה-Execution Plan שלהם זהה):

Select    *

From    T_Talmidim Tl

Where    Not 55>Any(Select Tziun

            From    T_Tziunim Tz

            Where    Tz.Shem=Tl.Shem);

            

Select    *

From    T_Talmidim Tl

Where    55<=All(Select Tziun

            From    T_Tziunim Tz

            Where    Tz.Shem=Tl.Shem);

וגם שני אלה זהים:

Select    *

From    T_Talmidim Tl

Where    55>Any(Select Tziun

            From    T_Tziunim Tz

            Where    Tz.Shem=Tl.Shem);

 

Select    *

From    T_Talmidim Tl

Where    Not 55<=All(Select Tziun

            From    T_Tziunim Tz

            Where    Tz.Shem=Tl.Shem);

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

ויש לשלוף את כל השורות בהן מופיעות כל המילים מתוך רשימה מסויימת,

ואף לא אחת מהמילים מרשימה אחרת..

מדובר עם כך בשני אתגרים שחוברו להם יחדיו.

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

If Object_ID('Keys') Is Not Null Drop Table Keys;

Go

 

Create Table Keys(Word Varchar(Max));

Go

 

Insert Into Keys Values ('Mother')

Insert Into Keys Values ('Father')

Insert Into Keys Values ('Son')

Insert Into Keys Values ('Daughter')

 

If Object_ID('WordsToExclude') Is Not Null Drop Table WordsToExclude;

Go

 

Create Table WordsToExclude(Word Varchar(Max));

Go

 

Insert Into WordsToExclude Values ('Exclude')

Insert Into WordsToExclude Values ('Stepdaughter')

 

If Object_ID('RealTest') Is Not Null Drop Table RealTest;

Go

 

Create Table RealTest(Id Int Identity(1,1) NOT NULL,

                    Searched Varchar(200) NULL,

                    Constraint PK_RealTest Primary Key Clustered(Id ASC)

        With (PAD_INDEX  = Off, 

            STATISTICS_NORECOMPUTE  = Off, 

            IGNORE_DUP_KEY = Off, 

            ALLOW_ROW_LOCKS  = On, 

            ALLOW_PAGE_LOCKS  = On) On [PRIMARY]) On [PRIMARY]

GO

 

Set ANSI_PADDING Off

GO

 

Create NonClustered Index IX_RealTest On dbo.RealTest(Searched ASC)

        With (PAD_INDEX  = Off, 

            STATISTICS_NORECOMPUTE  = Off, 

            SORT_IN_TEMPDB = Off, 

            IGNORE_DUP_KEY = Off, 

            DROP_EXISTING = Off, 

            ONLINE = Off, 

            ALLOW_ROW_LOCKS  = On, 

            ALLOW_PAGE_LOCKS  = On) On [PRIMARY]

GO

 

Declare @I Int = 1

Set NoCount On

While @I<10000

Begin

Insert Into RealTest  Values ('Mother Father Daughter Son iteration' + Cast(@i As Varchar(10)))

Insert Into RealTest Values ('Mother Daughter Son iteration' + Cast(@i As Varchar(10)))

Insert Into RealTest Values ('Mother Son iteration' + Cast(@i As Varchar(10)))

Insert Into RealTest Values ('Daughter Son')

Insert Into RealTest Values ('Mother Father Son')

Insert Into RealTest Values ('Son Daughter Father')

Insert Into RealTest Values ('Mother Son')

Insert Into RealTest Values ('Other Word')

Insert Into RealTest Values ('Mother Father Daughter Brother Son')

Insert Into RealTest Values ('Exclude Mother Father Daughter Brother Son Orphan')

Insert Into RealTest Values ('Exclude Mother Father Daughter Brother Son Orphan')

Insert Into RealTest Values ('MotherFatherDaughterBrotherSon')

Insert Into RealTest Values ('Exclude Mother Father Daughter Son Stepdaughter')

Insert Into RealTest Values ('Brother Mother Father Daughter Son Stepdaughter')

Insert Into RealTest Values ('Mother Son And Stepson and Daughter and Father and Grandfather')

Insert Into RealTest Values ('Daughter Son Family')

Insert Into RealTest Values ('Mother Brother Father Daughter Son Orphan')

Insert Into RealTest Values ('Son or Daughter or Father')

Insert Into RealTest Values ('Mother And Son')

Insert Into RealTest Values ('Other Word One More')

 

Set @i = @i +1

End

במאמר הם מציגים מספר פתרונות, ולפי בדיקת זמן ה-CPU בוחרים את המוצלח שבהם:

Set Statistics Time Off

 

Select    Count(*)

        /*m.Id, 

        m.Searched*/

From    RealTest m

Where    not exists (Select * From WordsToExclude W Where ' ' + m.Searched + ' ' like '% ' + W.Word + ' %')

        and 1 = ALL ( Select Case When ' ' + m.Searched + ' ' like '% ' + k.Word + ' %' Then 1 Else 0 End From Keys k)

 

Set Statistics Time Off

פקודות Set Statistics נועדו לאפשר מדידת זמן ה-CPU,

והשליפה רק סופרת את מספר הרשומות מכיוון שהצגת עשרות אלפי רשומות על המסך אין לה ולא כלום עם יעילות השליפה והיא עלולה לקחת זמן רב בגלל תעבורת הרשת.

ניתן לראות שהשתמשו בשליפה פעם אחת ב-Exists כדי לסנן את השורות שלא מופיעות בהן אף לא אחת מהמילים האסורות שבטבלה WordsToExclude,

ופעם אחת ב-All כדי לסנן את השורות שמופיעות בהן כל המילים הנדרשות שבטבלה Keys.

ניסיתי לשכתב את השאילתה כך שתכלול (1) רק תנאי Exists או (2) רק תנאי All:

--(1)

Select    Count(*)

        /*m.Id, 

        m.Searched*/

From    RealTest m

Where    not exists (Select * 

                    From WordsToExclude W 

                    Where ' ' + m.Searched + ' ' like '% ' + W.Word + ' %')

        And not exists (Select *

                        From    Keys k

                        Where    ' ' + m.Searched + ' ' Not like '% ' + k.Word + ' %');

 

--(2)

Select Count(*)

        /*m.Id, 

        m.Searched*/

From    RealTest m

Where    0 = All (Select Case When ' ' + m.Searched + ' ' like '% ' + w.Word + ' %' Then 1 Else 0 End From WordsToExclude W)

        and 1 = ALL ( Select Case When ' ' + m.Searched + ' ' like '% ' + k.Word + ' %' Then 1 Else 0 End From Keys k);

השליפה הראשונה רצה מעט פחות טוב מהשליפה שלהם,

והשניה מעט יותר טוב משלהם;

ובסה"כ השימוש ב-All הוביל לתוצאה הטובה עד כה.

אגב – מבחינת ה-Execution Plan השליפה השניה רצה פי שניים יותר טוב משתי האחרות (למרות שזה לא בא לידי ביטוי בזמן הריצה).

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

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

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

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