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

21/06/2010

Execution Plan: ביצוע Join בין שתי טבלאות

Filed under: Uncategorized — תגיות: , , , , , — גרי רשף @ 09:40

הבנת Execution Plans (להלן EP) זו משימה די בסיסית של כל מפתח או DBA. אישית- עד היום אני די מתקשה עם הנושא, ובדיונים בהם מוצגים EP אני מתרשם שאינני היחיד שידיעותיו לוקות בחסר.
אנסה כתרגול לבחון EP במצבים שונים ולרדת לעומקם.

ניצור שתי טבלאות נטולות אינדקסים – T1 עם 1000 המספרים השלמים 1..1000 (ועוד שדה טקסטואלי), ו-T2 עם 500 המספרים הזוגיים 2..1000 (ועוד שדה מספרי ושדה טקסטואלי) – פעם עם I=1 ופעם עם I=2 (כלומר- בכל טבלה יש 1000 שורות):

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

Go


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

Go


With T As

(Select 1 N,

        Cast('1' As Char(4)) S

Union All

Select    N+1,

        Cast(N+1 As Char(4)) S

From    T

Where    N<1000)

Select    *

Into    T1

From    T

option (MaxRecursion 0);

Go


With T As

(Select 2 N,

        1 I,

        Cast('1_1' As Char(6)) S

Union All

Select    N+2,

        1 I,

        Cast(Cast(N+1 As Char)+'_'+Cast(I As Char) As Char(6)) S

From    T

Where    N<1000)

Select    *

Into    T2

From    T

option (MaxRecursion 0);

Go


Insert Into T2 Select N,2,S From T2;

Go

באופן סמוי יש מפתחות לטבלאות וגם יחס של 1:N בינהן כפי שיוגדר בהמשך, אך עד כה לא הוגדר דבר פורמלית.

כעת נבדוק לאילו משורות T1 יש N מתאים ב-T2 בשתי דרכים – In ו-Join:

--In: T1 => T2

SELECT    T1.*

FROM    T1

WHERE    N In (SELECT N

            FROM    T2);

GO

--Join: T1 => T2

SELECT    T1.*

FROM    T1

Inner Join T2

        On T1.N=T2.N;

Go

את ה-EP נקבל על ידי Ctrl L או לחיצה על האייקון המתאים:

image

ה-EP די דומה בשני המקרים: המערכת עוברת על שתי הטבלאות (בהיעדר Clustered Index מתבצע Scan על הטבלה),

התוצאות מושוות בדרך של Hash Match שזה אלגוריתם סודי למקרה בו הטבלאות לא ממויינות,

והתוצאות המתאימות מוצגות.

ההבדל היחידי הוא שבמקרה של In מתבצע Right Semi Join ולא Inner Join מכיוון שיש לחפש את ההתאמה הראשונה לכל שורה, ואם כן – להציג אותה, ולא את כל ההתאמות. זו גם הסיבה לכך ש-In יחזיר 500 שורות ו-Join יחזיר 1000 שורות.

אם נבדוק את המקרה ההפוך לאילו משורות T2 יש N מתאים ב-T1 – ה-EP יהיה דומה לנ"ל.

ניצור כעת Primary Key לכל אחת מהטבלאות, ונגדיר Foreign Key בינהן:

Alter Table T1 Alter Column N Int Not Null;

Go


Alter Table T1

Add CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED (N ASC);

Go


Alter Table T2 Alter Column N Int Not Null;

Go


Alter Table T2 Alter Column I Int Not Null;

Go


Alter Table T2

Add CONSTRAINT PK_T2 PRIMARY KEY CLUSTERED (N,I);

Go


ALTER TABLE T2  WITH CHECK ADD CONSTRAINT FK_T2_N FOREIGN KEY(N) REFERENCES T1(N)

GO

נציג שוב את ה-EP של השליפות במקרה הראשון:

image

כעת מתבצע Clustered Index Scan לטבלאות במקום Table Scan מכיוון שיש להן Clustered Index.

במקרה של In מתבצע Aggregate (כמו Distinct) לעמודה N: יש צורך במופע אחד של כל ערך,

ובשני המקרים מתבצע Merge Join – מעבר סינכרוני על שתי טבלאות ממוניינות תוך בדיקה ממה מופיע באחת ומה בשניה.

נבדוק כעת את המקרה ההפוך:

--In: T2 => T1

SELECT    T2.*

FROM    T2

WHERE    N In (SELECT N

            FROM    T1);

Go

--Join: T2 => T1

SELECT    T2.*

FROM    T2

Inner Join T1

        On T2.N=T1.N;

Go

ונקבל את ה-EP הבאים:

image

במקרה של In לא התבצעה כלל פניה ל-T1: בגלל קיומו של ה-Foreign Key המערכת יודעת שכל ערכי T2 קיימים ב-T1 ואין צורך לבדוק זאת.

במקרה של Join כן מתבצע Clustered Index Scan ל-T1 למרות שזה מיותר: לכאורה נתוני T1 היו דרושים להצגה בפלט, אבל ה-Select מתייחס רק ל-T2 ולא ל-T1, וגם לא צפויה הכפלה של הרשומות מכיוון שב-T1 יש Primary Key (שהוא Unique בהגדרה) על עמודה N.

בכל מקרה- קיומו של Foreign Key יכול לשפר פלאים שליפות!

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

4 תגובות »

  1. […] לרבים) בין טבלאות. קיומו מהווה אבטחת איכות לנתונים, ובפוסט קודם ציינתי יתרון נוסף שנגזר מכך: אם נתעקש לבדוק שהנתונים […]

    פינגבאק של השימוש ב-Foreign Key - גרי רשף — 19/01/2012 @ 19:15

  2. […] לרבים) בין טבלאות. קיומו מהווה אבטחת איכות לנתונים, ובפוסט קודם ציינתי יתרון נוסף שנגזר מכך: אם נתעקש לבדוק שהנתונים […]

    פינגבאק של השימוש ב-Foreign Key « הבלוג של גרי רשף — 01/07/2010 @ 21:03

  3. היי גרי,
    הבעיה שלכל דבר יש מחיר, והכל שאלה של מה רוצים לשלם.
    ההשלכות של ה Foreign Key, הן בעיקר בזמן ההכנסה(ירידה בביצועים), כי כל הכנסה מחייבת את ה SQL לבדוק מול הטבלה השניה שהערך קיים, אלא אם כן אתה מכיר דרך לשפר גם נושא זה?

    תגובה של פלג — 21/06/2010 @ 12:17

    • לדעתי המחיר של זה זניח: חיפוש ערך בעמודה שיש עליה Primary Key הוא מאוד מהיר, ויש לזה חשיבות מבחינת אבטחת איכות הנתונים.
      כלומר- בוודאי שיש לזה מחיר, אך הוא לא כל כך גדול למיטב ידיעתי.
      לא בדקתי את זה באופן מעשי, אבל אולי אנסה בהזדמנות; ואם יש למישהו נסיון בתחום- אשמח לשמוע.

      תגובה של גרי רשף — 21/06/2010 @ 13:22


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

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

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