none
hash join RRS feed

  • דיון כללי

  •  שלום,

     נתונה לי השאילתה הבאה:

    select

    * from usagc.dbo.data_accounts da

    left

    join usagc.dbo.data_users as us

    on

    da.userid=us.id

    על שתי הטבלאות יש אינקדס על העמודה שעליה מתבצע ה  left  join   ומשום מה  האופטימייזר בוחר לעשות hash join ולא  merge join

    פיזור הנתונים על העמודות הללו הוא די יוניקי ( אבל לא יוניקי לגמרי)

    יש רעיונות תודה שרון.

     

    יום שלישי 03 מאי 2011 09:19

כל התגובות

  • הי,

    תחליף את ה- left join  ב- inner join ותראה מה מתבצע.

    יש פילטר (where) בשאילתא?

    תוודא ששני ה- data types ב- Join columns זהים.

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


    אסף שלם
    יום שלישי 03 מאי 2011 10:38
  •   אסף שלום,

    תראה מה עשיתי   פישטתי את השאילתה:

    select

    da.userid,us.id from usagc.dbo.data_accounts da

    inner

    join usagc.dbo.data_users as us

    on

    da.userid=us.id

    בנוסף זה אותו DATA TYPE   אז מדוע עכשיו עדיין זה hash join?

     מה צריך לקרות כדי שזה יהיה  merge join  ?

     תודה מראש שרון

    יום שלישי 03 מאי 2011 11:42
  • הי שרון,

    1. אני מניח שה- index מסוג nonecluseterd, האם זה נכון?

    2. במידה ונקודה 1 נכונה, האם קיים clustered index על הטבלה?

     

    צרפתי דוגמא כאשר שדות ה- Join מאונדקסים כ- Clustered.

    תעיף מבט אני כבר יסביר את הלוגיקה

    Create table dbo.UsersData (RowId int not null identity(1,1), UserId int not null, UserData char(300))
    Create table dbo.UsersAdditionalData (RowId int not null identity(1,1), UserId int not null, UserAdditionalData char(1000))
    
    Declare @Counter int
    Set @Counter = 1
    
    While @Counter <= 100000
    Begin
    	
    	Insert dbo.UsersData ( UserId)
    	Select @Counter
    	
    	if (@Counter % 4) = 0
    		insert dbo.UsersAdditionalData (UserId)
    		Select @Counter	
    
    	Set @Counter = @Counter + 1
    End 
    Go
    
    Create clustered index idx_UsersData_nc_UserId
    On dbo.UsersData(UserId)
    Go
    
    Create clustered index idx_UsersAdditionalData_nc_UserId
    On dbo.UsersAdditionalData(UserId)
    Go
    
    -- Hash Join 
    Select UD.UserId
    from dbo.UsersData UD
    Inner Join dbo.UsersAdditionalData UAD On UAD.UserId = UD.UserId
    
    -- Merge Join 
    Select top 10 UD.UserId
    from dbo.UsersData UD
    Inner Join dbo.UsersAdditionalData UAD On UAD.UserId = UD.UserId
    
    -- Merge Join 
    Select *
    from dbo.UsersData UD
    Inner Join dbo.UsersAdditionalData UAD On UAD.UserId = UD.UserId
    
    
    
    
    

     

     


    אסף שלם
    יום שלישי 03 מאי 2011 12:14
  • הי שרון,

    עכשיו צירפתי דוגמא כאשר שדות ה- join מאונדקסים nonclustered ואין על הטבלה Clustered Key.

    בהמשך אני יסביר את הלוגיקה, תעיף מבט.

    Create table dbo.UsersData (RowId int not null identity(1,1), UserId int not null, UserData char(300))
    Create table dbo.UsersAdditionalData (RowId int not null identity(1,1), UserId int not null, UserAdditionalData char(1000))
    
    Declare @Counter int
    Set @Counter = 1
    
    While @Counter <= 100000
    Begin
    	
    	Insert dbo.UsersData ( UserId)
    	Select @Counter
    	
    	if (@Counter % 4) = 0
    		insert dbo.UsersAdditionalData (UserId)
    		Select @Counter	
    
    	Set @Counter = @Counter + 1
    End 
    Go
    
    Create nonclustered index idx_UsersData_nc_UserId
    On dbo.UsersData(UserId)
    Go
    
    Create nonclustered index idx_UsersAdditionalData_nc_UserId
    On dbo.UsersAdditionalData(UserId)
    Go
    
    -- Hash Join 
    Select UD.UserId
    from dbo.UsersData UD
    Inner Join dbo.UsersAdditionalData UAD On UAD.UserId = UD.UserId
    
    -- Merge Join 
    Select top 10 UD.UserId
    from dbo.UsersData UD
    Inner Join dbo.UsersAdditionalData UAD On UAD.UserId = UD.UserId
    
    -- Nested Join 
    Select *
    from dbo.UsersData UD
    Inner Join dbo.UsersAdditionalData UAD On UAD.UserId = UD.UserId

    אסף שלם
    יום שלישי 03 מאי 2011 12:18
  •  אסף שלום,

    1) האינדקס הוא אכן מסוג NONCLUSTER INDEX

    2) קיים CLUSTER INDEX   על הטבלה אבל ב select  שלי אני בוחר רק את העמודות המאונדקסות בלבד : ( כלומר הם ממוינות כבר)

    הנה אסף :

    select

    da.userid,us.id from usagc.dbo.data_accounts da

    left

    join usagc.dbo.data_users as us

    on

    da.userid=us.id

    פה אתה רואה ש select  שנעשה על עמודות שיש עליהם אינדקס והם ממוינות בנוסף ב select list  אני מבקש בresault set  את שלי העמודות הללו.

    עגיין הוא עושה hash join ולא merge  join  וזה שוט מעצבן ולא ברור לי :)

     

     

    יום שלישי 03 מאי 2011 12:50
  • הי שרון,

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

    "המנוע" מקבל שאילתא, ובמידה ואין לו תוכנית ביצוע ב- cache הוא ייצר אחד כזו.

    איך הוא מייצר, בגדול:

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

    תנאים מקדימים ל- merge Join:

    1. שני המקורות ממויינים.

    2. אותו data type.

    בינתיים אתה עומד בקריטריונים.

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

    אבל מה, במידה ותעמוד על האופרטור index scan בתוכנית הביצוע ,קליק ימני -> מאפיינים, תראה שהמאפיין order מופיע כ- false ולכן מתבצע hash ולא Merge.

    למה זה קורה? כאשר אתה רוצה מידה "מלא" ל- sql engine יש הרבה דרכים לסרוק את המידה ולכן לא בטוח שהוא יסרוק ע"פ סדר.

    אם תוסיף top 10 לדוגמא תראה שיהיה merge join וגם המאפיין order יהיה true.

    תריץ את הדוגמאות שסיפקתי בפוסטים הקודמים ונראה לי שתבין את הלוגיקה, בגדול.

    בהצלחה

     

     


    אסף שלם
    יום שלישי 03 מאי 2011 13:35
  • אסף קודם כל תודה על התגובה.

    ההסבר שלך היה מצויין והביר לי המון דברים שלא ידעתי.

    תודה שרון.

    יום שלישי 03 מאי 2011 14:35
  • שלום חברים,

    בדיון הזה קראתי לא מעט דברים שנראה לי שצריך להעמיד על דיוקם:

    1. שרון: " ומשום מה האופטימייזר בוחר לעשות hash join ולא merge join"

    > אין מספיק נתונים על האינדקסים, הנתונים, מבנה הטבלה ואפילו לא צרפת את ה PLAN עצמו כך שדי בלתי אפשרי לענות לך תשובה מושכלת. 

       האם ניסית להריץ עם HINT של MERGE וראית שזה באמת רץ יותר מהר?

    " אסף קודם כל תודה על התגובה. ההסבר שלך היה מצויין והביר לי המון דברים שלא ידעתי. תודה שרון."

    > אם התשובה של אסף, שהשקיע  לא מעט זמן וטרח לענות לך, לחפש ברשת ולהביא דוגמאות, עזרה לך כפי שאתה אומר - אז למה אתה לא גומל לו במינימום של סימון תשובותיו כמועילות?

    -----------

    2. אסף: "הי שרון, קודם כל נתחיל בזה שלא בטוח שנקבל את ה- plan האידיאלי גם במידה וסיפקנו את כל הפרמטרים הדרושים ליצירת ה- plan."

    > מה הכוונה "סיפקנו את כל הפרמטרים הדרושים ליצירת ה PLAN"? אתה משגר רק שאילתא למיטב ידיעתי. HINT לא ראיתי כאן.

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

    > לא מדויק. פרק הזמן והמשאבים שיוקצו לבניית ה PLAN תלויים במורכבות ובעלות המשוערכת של השאילתה. שאילתא כבדה ומורכבת תקבל יותר זמן אופטמיזציה משאילתא קטנה ופשוטה.

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

    > מה ההבדל בין "אופטימלית" ל "יעילה"?

    "1. שני המקורות ממויינים. 2. אותו data type."

    > לא מדויק: המקורות לא חייבים להיות ממוינים, המנוע יודע לבצע מיון לטובת MERGE. גם ה DATA TYPES לא חייבים להיות זהים, הם צריכים להיות מאותה משפחה.

    "אבל מה, במידה ותעמוד על האופרטור index scan בתוכנית הביצוע ,קליק ימני -> מאפיינים, תראה שהמאפיין order מופיע כ- false ולכן מתבצע hash ולא Merge."

    > לא מדויק: ה ORDERED SCAN לא קובע את האופרטור אלא להיפך... ב HASH פשוט אין צורך בסריקה ממוינת כמו ב MERGE אך הבחירה היא תמיד על פי האופרטור היעיל יותר. 

    HTH

     


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--
    יום שלישי 03 מאי 2011 19:25
  • הי,

    אנסה לדייק יותר ובאמת תודה על ההערות.

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

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

    3. לגבי המיון, ההנחה שלי לא נכונה ותודה על התיקון.

    שוב תודות וכמו שנאמר "מכל מלמדי ..."

    לילה טוב,

     

     


    אסף שלם
    יום שלישי 03 מאי 2011 21:56
  •  עמי שלום,

    אני תמיד גומל לאסף בסימונים אבל רק בסופו של הדיון אבל תמיד גומל לו קודם במילת תודה.

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

    מקבל את ההערה על כך שלא סיפקתי מספיק נתונים להבא אני אפיק את הלקח הנ"ל.

    כמו כן לגבי התשובות  שאסף הביא לי חשוב לי להבהיר:

    התשובות שלו אומנם לא היו מדוייקות כמו  שלך  אבל בהחלט נותנות לי  קווי מחשבה ופותחות  לי אופקים  שלא היו ידועים לי.

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

    תודה רבה

     

     

     

    יום רביעי 04 מאי 2011 06:38
  • הי,

    לגבי תוכניות ביצוע אפשר להשתמש באחת מהפקודות:

    set showplan_text on שמציגה את ה- plan בצורה טקסטואלית וקריאה.

    או

    set showplan_xml on שמציגה את ה- plan בצורת xml וע"י שמירת ה- xml אפשר לפתוח בצורה גרפית.

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

    יום טוב,

     


    אסף שלם
    יום רביעי 04 מאי 2011 06:50