none
תוכנית פעולה לא ברורה

    Question

  • אשמח לעזרתכם בנושא ביצועים , השאילתה הבאה מבצעת join בין שתי טבלאות ו left outer join על טבלה נוספת , שמנו לב כי תנאי ה ORגורם לאופטימיזר לבצע scan על האינדקס במקום seek  .
     
    סקריפט יצירת האובייקטים מצורף .
     
    זו השאילתה הבעייתית :
     
    select EventPerson.DUID from EventPerson  inner join
    Base  on EventPerson.DUID=base.ID left outer  join EventPerson788
    on EventPerson.DUID=EventPerson788.sourceid
    where (EventPerson.PersonInvolvedID=37909 or
    EventPerson788.LinkedSuspectID=37909) AND BASE.IsActive=1
    את המזהה אתה יכול להחליף בכל מספר מטבלת EventPerson , אתם לא באמת חייבים לחולל 100 מיליון שורות J .
     
    בכל מקרה על סטים קטנים של מידע , הוא מבצע Seek על טבלת EventPerson מבצע NL  אל מול טבלת BASE וממשיך עם סט התוצאות שיצאו לו בחיתוך לטבלת EventPerson788 (היא ריקה ) , שמנו לב שתנאי הOR  גורם לו לבצע index scan על טבלת eventperson ומשםclustered index scan על טבלת ה base ( בסביבת הבדיקות שלנו היא מכילה כ 75 מיליון שורות ) . כאשר מריצים כל תנאי בנפרד זה עובד מהר .
     
    אלו הפעולות שבצענו :
     
    א.       עדכון סטט.
    ב.       בניית כל האינדקסים .
    ג.        עבודה עם Hints על forceseek,maxdop,force order .
    ד.        אם מוסיפים את התנאי ה dummy הבא : and EventPerson788.LinkedSuspectID*0=0 הוא מתנהג תקין אבל אז לא חוזר דבר כי ה left join מחזיר null והתוצאה של המכפלה לא אז ירדנו מזה .
     
     
    אני חשבתי אולי בכיוון שהאופטימייזר הגיע למסקנה שהוא חצה את ה Tipping point וזה השלב שבו הוא מחליט לבצע scan במקום seek ( בגלל כמות הדפים וגודל השורה ) .
     

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

    מצב סקריפט יצירת האובייקטים. 

    CREATE DATABASE TEST
    GO 
    USE TEST 
    GO
    ALTER DATABASE TEST SET RECOVERY SIMPLE
    GO 

    --Create Tables 
    CREATE TABLE [dbo].[BASE](
    [ID] [bigint] NOT NULL,
    [IsActive] BIT
    PRIMARY KEY CLUSTERED 
    (
    [ID] ASC
    )
    )ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[EventPerson](
    [DUID] [bigint] NOT NULL,
    [PersonInvolvedID] [bigint] NULL,
    PRIMARY KEY CLUSTERED 
    (
    [DUID] ASC
    )
    ) ON [PRIMARY]


    GO

    CREATE NONCLUSTERED INDEX [EventPerson_IDX] ON [dbo].[EventPerson]
    (
    [PersonInvolvedID] ASC
    )




    CREATE TABLE [dbo].[EventPerson788](
    [EntryID] [bigint] NOT NULL,
    [LinkedSuspectID] [bigint] NULL,
    [sourceid] [bigint] NULL,
    PRIMARY KEY CLUSTERED 
    ([EntryID] ASC)

    )
     ON [PRIMARY]

    GO
    ALTER TABLE [dbo].[EventPerson788]  WITH CHECK ADD  CONSTRAINT [FK7A34153D3720F84A] FOREIGN KEY([sourceid])
    REFERENCES [dbo].[EventPerson] ([DUID])
    GO

    ALTER TABLE [dbo].[EventPerson788] CHECK CONSTRAINT [FK7A34153D3720F84A]
    GO

    CREATE NONCLUSTERED INDEX [EventPerson788_IDX] ON [dbo].[EventPerson788]
    (
    [LinkedSuspectID] ASC
    )
    GO


    --POPOLATE BASE TABLE 
    DECLARE @I BIGINT=1 
    WHILE (@I<=10000000) 
    BEGIN
    begin transaction
    INSERT INTO BASE(ID) VALUES(@I) 
    SET @I+=1
    if (@I%10000=0 )
    begin
        commit;
    end;
    END

    go




    --POPOLATE EventPerson TABLE 
    DECLARE @I BIGINT=1 
    WHILE (@I<=500000) 
    BEGIN
    BEGIN TRANSACTION
    INSERT INTO EventPerson(DUID,PersonInvolvedID) VALUES(@I,(SELECT TOP 1 ID FROM BASE ORDER BY NEWID())) 
    SET @I+=1
    IF(@I%10000=0 )
      COMMIT TRANSACTION ;
    END

    GO 



    • Edited by DBANinja Sunday, August 11, 2013 7:42 PM
    Sunday, August 11, 2013 7:41 PM

Answers

  • הי נינג'ה,

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

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

    בכל אופן, הבעיה במקרה שלך באמת נעוצה ב-"OR". באופן כללי, ה-Optimizer לא יודע להתמודד כל-כך טוב עם OR. בהרבה מקרים שכתוב של השאילתה באמצעות UNION יכול להביא לשיפור ניכר בביצועים, אבל צריך לבדוק - כל מקרה לגופו. במקרה שלך, לדעתי השאילתה הבאה תעבוד הרבה יותר טוב מהשאילתה שאתה הצגת:

    SELECT
    	EventPerson.DUID
    FROM
    	dbo.EventPerson
    INNER JOIN
    	dbo.Base
    ON
    	EventPerson.DUID = Base.ID
    WHERE
    	EventPerson.PersonInvolvedID = 37909
    AND
    	Base.IsActive = 1
    
    UNION
    
    SELECT
    	EventPerson.DUID
    FROM
    	dbo.EventPerson
    INNER JOIN
    	dbo.Base
    ON
    	EventPerson.DUID = Base.ID
    INNER JOIN
    	dbo.EventPerson788
    ON
    	EventPerson.DUID = EventPerson788.SourceID
    WHERE
    	EventPerson788.LinkedSuspectID = 37909
    AND
    	Base.IsActive = 1;
    

    מכיוון שאתה שולף רק את העמודה "DUID", אני מניח שאתה לא מעוניין לקבל כפילויות. לכן השתמשתי ב-UNION. גם בשאילתה המקורית שלך אתה עלול לקבל כפילויות, דרך אגב. אם לא אכפת לך אם תהיינה כפילויות או לא, אז כדאי להפוך את ה-UNION ל-UNION ALL, ובכך לחסוך את פעולת המיון. אם חשוב לך לקבל את הכפילויות, אז צריך להוסיף לחלק הראשון של השאילתה (לפני ה-UNION ALL) את ה-LEFT JOIN לטבלה "dbo.EventPerson788".

    בהצלחה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • Proposed as answer by Ivan Radchenko Wednesday, August 14, 2013 4:22 AM
    • Marked as answer by DBANinja Friday, August 16, 2013 10:33 PM
    Tuesday, August 13, 2013 7:30 PM
    Moderator
  • חזרתי אחרי עיון כללי בדיון ובשאילתות ובעיקר ב DDL+DML.

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

    * כל שאלה יש להתחיל כאמור מ DDL+DML.

    נקודה 1: שימו לב שהעברתי את את החלק של יצירת האינדקסים לסיום ה DDL ויש לזה סיבה חשובה מעשית! ההפרדה בין DDL לבין ה DML יכולה להיות נוחה לרישום אבל מאוד לא יעילה ברך כלל. אנחנו רוצים בעוד רגע למלא את הטבלאות באלפי רשומות אם לא כמה מליוני רשומות. אינדקסים, מפתחות חיצוניים ואלמנטים מסוג זה מהווים גורם מפריע להכנסת נתונים!

    כדאי מאוד לחשוב כשניגשים לבעיה כזו לקפוץ רגע אל ה DML ולהכניס את הנתונים תחילה ורק אז להמשיך עם ה DDL!

    /************************************************************************************************ START DDL */ 
    CREATE TABLE [dbo].[BASE](
    	[ID] [BIGINT] NOT NULL
    	, [IsActive] BIT NULL
    	-- היה חסר פסיק לכתיבה נכונה
    	, PRIMARY KEY CLUSTERED ([ID] ASC)
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[EventPerson](
    	[DUID] [BIGINT] NOT NULL
    	,[PersonInvolvedID] [BIGINT] NULL
    	,PRIMARY KEY CLUSTERED ([DUID] ASC)
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[EventPerson788](
    	[EntryID] [BIGINT] NOT NULL
    	, [LinkedSuspectID] [BIGINT] NULL
    	, [sourceid] [BIGINT] NULL
    	, PRIMARY KEY CLUSTERED ([EntryID] ASC)
    ) ON [PRIMARY]
    GO
    
    CREATE NONCLUSTERED INDEX [EventPerson_IDX] ON [dbo].[EventPerson](
    	[PersonInvolvedID] ASC
    )
    
    ALTER TABLE [dbo].[EventPerson788] WITH CHECK 
    	ADD CONSTRAINT [FK7A34153D3720F84A] FOREIGN KEY([sourceid]) REFERENCES [dbo].[EventPerson] ([DUID])
    GO
    
    ALTER TABLE [dbo].[EventPerson788] CHECK CONSTRAINT [FK7A34153D3720F84A]
    GO
    
    CREATE NONCLUSTERED INDEX [EventPerson788_IDX] ON [dbo].[EventPerson788]([LinkedSuspectID] ASC)
    GO
    /************************************************************************************************ END DDL */

    נעבור אל ה DML והכנסת הנתונים (כאמור שכדאי לבצע בשלב באמצע ה DDL בדרך כלל). אני רושם הרבה מאוד הערות חשובות מאוד לדעתי בתוך הקוד עצמו. אני ממליץ לעבור על החלק הבא לעומק!

    /************************************************************************************************ START DML */
    -- הוספת ההגדרה הקטנה הזו יכולה לעשות הבדל של מאות אחוזים בזמן ההרצה!
    -- בזמן שהשרת נתונים מאפשר לנו להכניס מליוני רשומות בשנייה תאורטית
    -- הרי שיש לנו מגבלות אחרות בדרך כלל כמו מהירות הכתיבה לדיסק
    -- האם על כל זה אנחנו רוצים להוסיף מגבלות של הצגת נתונים בממשק ה
    -- SSMS
    -- הכתיבה למסך לוקחת הרבה יותר זמן מהכתיבה לדיסק!
    SET NOCOUNT ON
    GO
    
    -- השאילתה המקורית עושה שימוש בלולאות. נסו לוותר על לולאות ככל הניתן
    -- האם צריך לחלק את הפעולה לאלפים?!?
    -- זה זיזול מוחלט בשרת :-) תסמכו עליו שאין לו בעיה להכניס מליוני רשומות
    -- אם היה צורך לבצע השהייה כדי לתת למערכת זמן לבצע דברים אחרים אז מה טוב, אם היה צורך בפעולות נוספות ושחרור נעילות אז מה טוב
    -- אבל זה פעולות בשרת פיתוח לצרכי בדיקה
    DECLARE @I BIGINT=1 
    WHILE (@I<=10000000) 
    BEGIN
    	BEGIN TRANSACTION
    		INSERT INTO BASE(ID) VALUES(@I) 
    		SET @I+=1
    		
    		IF (@I%10000 = 0 )
    		BEGIN
    			COMMIT;
    		END;
    END
    GO
    
    -- הרבה יותר טוב לבצע פשוט
    insert into BASE(ID)
    select top 10000000 row_number() over(order by (SELECT NULL)) as N
    from   master..spt_values t1 
           cross join master..spt_values t2
    GO
    
    -- השאילתה המקורית: ראו הערות קודמות
    DECLARE @I BIGINT=1 
    WHILE (@I<=500000) 
    BEGIN
    	BEGIN TRANSACTION
    	INSERT INTO EventPerson(DUID,PersonInvolvedID) VALUES(@I,(SELECT TOP 1 ID FROM BASE ORDER BY NEWID())) 
    	SET @I+=1
    	IF(@I%10000=0 ) COMMIT TRANSACTION ;
    END
    GO 
    
    -- כאן נוסף לנו בעיה חדשה: שימו לב לשאילתה הפנימית שיש לנו
    SELECT TOP 1 ID FROM BASE ORDER BY NEWID()
    -- למה לבחור נתון מטבלה כשכל מה שאנחנו צריכים זה נתון רנדומלי?
    -- אנחנו יודעים שהטבלה הראשונה מלאה בנתונים מסודרים 1-10000000 אחרי הכל אנחנו הכנסנו אותם כרגע
    -- יותר מכך השאיתה הזו מנחייבת סידור הנתונים מחדש בכל פעם ומעבר על כל הנתונים בטבלה
    -- אתם מוזמנים להשוות את מהירות ההרצה של השאילתות הבאות ולהבין את הבעיה.
    -- ובוא נזכור ששאילתות אלו בעוד רגע ירוצו בלולאה כמה אלפי פעמים
    SELECT TOP 1 ID FROM BASE ORDER BY NEWID()
    SELECT ABS(CAST(NEWID() AS binary(9)) %10000000) + 1
    
    -- נכניס את הנתונים אם כן בעזרת השאילתה המשופרת הבא:
    insert into EventPerson(DUID,PersonInvolvedID)
    select top 500000 
    	row_number() over(order by (SELECT NULL)) as N
    	, ABS(CAST(NEWID() AS binary(9)) %10000000) + 1
    from   master..spt_values t1 
           cross join master..spt_values t2
    GO
    
    -- מבט מהיר על האילתה הסופית מעלה את הבעיה בנתונים שלנו
    -- כמובן שעדיף היה מראש להכניס את הנתונים אבל רק בשלב זה שמתי לב לכך ולכן נוסים אותם
    -- we need some data with BASE.IsActive=1 to check the query !
    update BASE set BASE.IsActive = 1
    where BASE.ID % 5 = 0
    GO
    
    -- נבדוק אחוז מהנתונים לדוגמה:
    -- אני לא בודק את כל הנתונים כי ההצגה שלהם על המסך לוקחת הרבה זמן
    -- ולא השאילתה עצמה בהכרח
    select * from [dbo].[BASE] tablesample(1 percent)
    select * from [dbo].[EventPerson] tablesample(1 percent)
    
    /************************************************************************************************ END DML */

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

    select
    	EventPerson.PersonInvolvedID 
    from EventPerson
    inner join Base on EventPerson.DUID = base.ID 
    where BASE.IsActive=1

    אצלי מצאתי שרשומה EventPerson.PersonInvolvedID = 5851466 נמצאת מתאימה לבדיקת השאילתה. לכן עתה אני יכול לחזור לשאילתלה המקורית של שואל השאלה ולבדוק אותה:

    select top 1000 
    	EventPerson.DUID 
    from EventPerson
    inner join Base on EventPerson.DUID = base.ID 
    left outer join EventPerson788 on EventPerson.DUID = EventPerson788.sourceid
    where (EventPerson.PersonInvolvedID = 5851466 or EventPerson788.LinkedSuspectID = 5851466) AND BASE.IsActive=1
    GO

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

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

    select * from EventPerson
    where EventPerson.PersonInvolvedID = 5851466
    GO
    select * from EventPerson788
    where EventPerson788.LinkedSuspectID = 5851466
    GO


    השאילתה הראשונה ניתן לראות שיש שימוש ב "Index Seek" אבל בשאילתה השנייה רואים שימוש ב "Clustered Index Scan". הסיבה אינה קשורה כבר לפיצול השאילתה אלא פשוט לנתונים שיש בטבלה עצמה! אני מנסה להבהיר ששאילתה שטובה במסד נתונים אחד אפילו אם הוא זהה למסד הנתונים השני לא בהכרח תיתן אותה תוצאה ולא תמיד מה שטוב מסד נתונים אחד ייה גם טוב באחר!

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

    insert EventPerson788 ([EntryID],[LinkedSuspectID],[sourceid]) values (1,5851466,1)
    insert into EventPerson788 ([EntryID],[LinkedSuspectID])
    select top 500000 
    	row_number() over(order by (SELECT NULL)) + 1 as N
    	, ABS(CAST(NEWID() AS binary(9)) %10000000) + 1
    from   master..spt_values t1 
           cross join master..spt_values t2
    GO

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

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

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

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

    select * from EventPerson788
    where EventPerson788.LinkedSuspectID = 5851466
    GO

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

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

    * לגבי השאילתה הסופית כדאי כאמור לקרוא עתה את החלק שכתב גיא ואז להגיע למיטביות של השאילתה אצלכם או לספק גם DML שמתאים לבעיה שרוצים לפתור ולא רק DDL, כי השאילתה שתתאים ל DML אחד לא תתאים בהכרח לאחר, ולכן אני תמיד מבקש DDL+DML :-)

    קריאה מהנה :-)


    signature

    • Marked as answer by DBANinja Friday, August 16, 2013 10:33 PM
    Thursday, August 15, 2013 8:27 AM
    Moderator

All replies

  • היי נינג'ה,

    יש הבדל בביצועים אם אתה מעביר את תנאי ה-Or לחלק של ה-On ב-Join?


    My Blog

    Sunday, August 11, 2013 9:32 PM
  •  היי ,

    אתה יכול קצת יותר לפרט , לשים את מקטע הקוד שהתכוונת ?

    תודה .

    Monday, August 12, 2013 6:49 AM
  • כשכותבים left join, יש הבדל לוגי אם כותבים את התנאי ב-Where או ב-On, וזה יכול להשפיע על התוצאות שנקבל ועל ה-Execution Plan.

    מה קורה אם השאילתא נראית ככה?

    select EventPerson.DUID 
    from EventPerson  inner join Base  
    on EventPerson.DUID=base.ID 
    left outer  join EventPerson788
    on EventPerson.DUID=EventPerson788.sourceid
    AND 
       (EventPerson.PersonInvolvedID=37909 
        or EventPerson788.LinkedSuspectID=37909) 
    AND BASE.IsActive=1


    My Blog

    Monday, August 12, 2013 6:57 AM
  • היי מתן,

    הרעיון שלך הוא מצוין, אבל השאילתה שלך לא מחזירה את אותו הדבר. להלן שאילתה שמחזירה את התוצאה הרצויה ואכן מקבלת execution plan יעיל:

    select EventPerson.DUID from EventPerson  
    inner join Base  
    on EventPerson.DUID=base.ID 
    left outer  join EventPerson788
    on EventPerson.DUID=EventPerson788.sourceid
    AND EventPerson788.LinkedSuspectID=37909
    WHERE EventPerson.PersonInvolvedID=37909 
    AND BASE.IsActive=1





    Monday, August 12, 2013 12:07 PM
  • האמת שעכשיו שאני חושב על זה יותר לעומק, הרעיון שלך, מתן, כבר לא נראה לי כל כך יפה. :) שאילתה שלי היא גם בעיתית: היא עובדת תקין כל עוד טבלת EventPerson778 ריקה, כי בסופו של דבר היא תחזיר רק את הרשומות שעונות על התנאי הבא:
    EventPerson.PersonInvolvedID=37909 

    כלומר, היא הופכת את ה-or המקורי ל-and.

    שורה תחתונה, אני לא רואה דרך להכניס את ה-or לתוך ה-join בכלל.




    Monday, August 12, 2013 12:40 PM
  • היי איוון, מה שחשוב להבין זה את הסדר הלוגי של עיבוד שאילתא ולפי זה לכתוב את הפתרון. כשכותבים left join, קודם מחושב ה-set שעונה לתנאי, ורק עליו מבוצע ה-Where. לפחות אצלי לא נכנסו ערכים לעמודת PersonInvolvedId ולכן למרות שיש שורות שחזרו מה-Join, ברגע שנשאל עליה ב-Where כולן ייפסלו. לדעתי שני הפתרונות קבילים - פשוט צריך להחליט מה נכון למקרה הספציפי עבור נינג'ה. לגבי ה-Plan, הוא מבצע Merge Join ולכן מחליט שיותר זול לו לעשות Clustered Index Scan. ברגע שמכריחים אותו לעשות Loop Join, מתבצע Index Seek.

    My Blog

    Monday, August 12, 2013 1:21 PM
  • היי ,

    תודה רבה על המענה והרעיונות , בכל מקרה בשני הפתרונות הסריקה שולחת את האפליקציה ל TimeOut  , מה שעוד לא ברור לי זה מדוע הוא מחיליט לבצע סריקה כאשר מתווסף ה Left Join הרי הוא יודע שהטבלה ריקה , אז מדוע הוא מתעקש לסרוק ?

    תודה .

    Monday, August 12, 2013 2:27 PM
  • מתן,

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

    לגבי ה-join:

    אני לא מקבל merge join בין EventPerson לבין BASE, אלא זה תמיד nested loops, כנראה כי לא הכנסתי את כל ה-500000 רשומות ל-EventPerson, אז אין מספיק רשומות על מנת שה-merge join יהיה עדיף. בכל זאת, אני כן רואה פעם scan ופעם seek, לכן אני חושב שאתה טועה כשאתה אומר ש"הוא מבצע Merge Join ולכן מחליט שיותר זול לו לעשות Clustered Index Scan. ברגע שמכריחים אותו לעשות Loop Join, מתבצע Index Seek."

    אתה צודק לגבי ה-where, אבל חלקית. כאשר יש שם or, אז נכון, ה-where מתבצע אחרי ה-join והוא פוסל את כל התוצאות שחזרו מה-join, אבל כאשר אין or (כמו בשאלתה שלי), אז ה-where מתייחס רק ל-EventPerson ול-BASE ולכן הוא מבוצע לפני ה-left join. אני רואה את זה לפי כמות שורות שחזרו מה-join בין EventPerson לבין BASE: בשאילתה שלי חוזרת רק שורה 1.

    נינג'ה,

    כשאתה מדבר על scan במקום seek, אתה מתכוון לטבלת EventPerson, נכון? אם כך, left outer join הוא לא המקור לבעיה, ה-or זה מה שגורם לבעיה. תריץ את השאילתה שלי ותראה. לכן זה לא נכון להגיד ש"הוא מחליט לבצע סריקה כאשר מתווסף ה Left Join".

    שורה תחתונה:

    לדעתי, הבעיה נובעת מה-or. האופטימייזר בוחר ב-scan ולא ב-seek כי הוא חושב שהוא צריך את כל הרשומות מ-EventPerson. הוא לא באמת צריך אותם כי טבלת EventPerson788 ריקה, וה-or לא משחק פה תפקיד. למה הוא לא לוקח את זה בחשבון, אין לי מושג. כנראה, הוא לא מספיק חכם...


    Tuesday, August 13, 2013 6:40 AM
  • טוב, כדי לעשות סדר, אני מציע שניקח צעד אחורה.

    נינג'ה, תוכל בבקשה להגיד לנו מה עדיין לא ברור עבור איזו טבלה ואנסה לעזור?


    My Blog

    Tuesday, August 13, 2013 11:35 AM
  • הי נינג'ה,

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

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

    בכל אופן, הבעיה במקרה שלך באמת נעוצה ב-"OR". באופן כללי, ה-Optimizer לא יודע להתמודד כל-כך טוב עם OR. בהרבה מקרים שכתוב של השאילתה באמצעות UNION יכול להביא לשיפור ניכר בביצועים, אבל צריך לבדוק - כל מקרה לגופו. במקרה שלך, לדעתי השאילתה הבאה תעבוד הרבה יותר טוב מהשאילתה שאתה הצגת:

    SELECT
    	EventPerson.DUID
    FROM
    	dbo.EventPerson
    INNER JOIN
    	dbo.Base
    ON
    	EventPerson.DUID = Base.ID
    WHERE
    	EventPerson.PersonInvolvedID = 37909
    AND
    	Base.IsActive = 1
    
    UNION
    
    SELECT
    	EventPerson.DUID
    FROM
    	dbo.EventPerson
    INNER JOIN
    	dbo.Base
    ON
    	EventPerson.DUID = Base.ID
    INNER JOIN
    	dbo.EventPerson788
    ON
    	EventPerson.DUID = EventPerson788.SourceID
    WHERE
    	EventPerson788.LinkedSuspectID = 37909
    AND
    	Base.IsActive = 1;
    

    מכיוון שאתה שולף רק את העמודה "DUID", אני מניח שאתה לא מעוניין לקבל כפילויות. לכן השתמשתי ב-UNION. גם בשאילתה המקורית שלך אתה עלול לקבל כפילויות, דרך אגב. אם לא אכפת לך אם תהיינה כפילויות או לא, אז כדאי להפוך את ה-UNION ל-UNION ALL, ובכך לחסוך את פעולת המיון. אם חשוב לך לקבל את הכפילויות, אז צריך להוסיף לחלק הראשון של השאילתה (לפני ה-UNION ALL) את ה-LEFT JOIN לטבלה "dbo.EventPerson788".

    בהצלחה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • Proposed as answer by Ivan Radchenko Wednesday, August 14, 2013 4:22 AM
    • Marked as answer by DBANinja Friday, August 16, 2013 10:33 PM
    Tuesday, August 13, 2013 7:30 PM
    Moderator
  • וואו גיא... רק נעלמתי לכמה ימים... יש לי כל כך הרבה מה להגיב כאן :-)

    לפני הכל כמו שאמרת: כל הכבוד לשואל השאלה שזכר לצרף DDL+DML (אם כי ה DML לא תואם לשאלה למשל במקרה של IsActive שתמיד NULL)

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

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


    signature

    Thursday, August 15, 2013 5:39 AM
    Moderator
  • חזרתי אחרי עיון כללי בדיון ובשאילתות ובעיקר ב DDL+DML.

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

    * כל שאלה יש להתחיל כאמור מ DDL+DML.

    נקודה 1: שימו לב שהעברתי את את החלק של יצירת האינדקסים לסיום ה DDL ויש לזה סיבה חשובה מעשית! ההפרדה בין DDL לבין ה DML יכולה להיות נוחה לרישום אבל מאוד לא יעילה ברך כלל. אנחנו רוצים בעוד רגע למלא את הטבלאות באלפי רשומות אם לא כמה מליוני רשומות. אינדקסים, מפתחות חיצוניים ואלמנטים מסוג זה מהווים גורם מפריע להכנסת נתונים!

    כדאי מאוד לחשוב כשניגשים לבעיה כזו לקפוץ רגע אל ה DML ולהכניס את הנתונים תחילה ורק אז להמשיך עם ה DDL!

    /************************************************************************************************ START DDL */ 
    CREATE TABLE [dbo].[BASE](
    	[ID] [BIGINT] NOT NULL
    	, [IsActive] BIT NULL
    	-- היה חסר פסיק לכתיבה נכונה
    	, PRIMARY KEY CLUSTERED ([ID] ASC)
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[EventPerson](
    	[DUID] [BIGINT] NOT NULL
    	,[PersonInvolvedID] [BIGINT] NULL
    	,PRIMARY KEY CLUSTERED ([DUID] ASC)
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[EventPerson788](
    	[EntryID] [BIGINT] NOT NULL
    	, [LinkedSuspectID] [BIGINT] NULL
    	, [sourceid] [BIGINT] NULL
    	, PRIMARY KEY CLUSTERED ([EntryID] ASC)
    ) ON [PRIMARY]
    GO
    
    CREATE NONCLUSTERED INDEX [EventPerson_IDX] ON [dbo].[EventPerson](
    	[PersonInvolvedID] ASC
    )
    
    ALTER TABLE [dbo].[EventPerson788] WITH CHECK 
    	ADD CONSTRAINT [FK7A34153D3720F84A] FOREIGN KEY([sourceid]) REFERENCES [dbo].[EventPerson] ([DUID])
    GO
    
    ALTER TABLE [dbo].[EventPerson788] CHECK CONSTRAINT [FK7A34153D3720F84A]
    GO
    
    CREATE NONCLUSTERED INDEX [EventPerson788_IDX] ON [dbo].[EventPerson788]([LinkedSuspectID] ASC)
    GO
    /************************************************************************************************ END DDL */

    נעבור אל ה DML והכנסת הנתונים (כאמור שכדאי לבצע בשלב באמצע ה DDL בדרך כלל). אני רושם הרבה מאוד הערות חשובות מאוד לדעתי בתוך הקוד עצמו. אני ממליץ לעבור על החלק הבא לעומק!

    /************************************************************************************************ START DML */
    -- הוספת ההגדרה הקטנה הזו יכולה לעשות הבדל של מאות אחוזים בזמן ההרצה!
    -- בזמן שהשרת נתונים מאפשר לנו להכניס מליוני רשומות בשנייה תאורטית
    -- הרי שיש לנו מגבלות אחרות בדרך כלל כמו מהירות הכתיבה לדיסק
    -- האם על כל זה אנחנו רוצים להוסיף מגבלות של הצגת נתונים בממשק ה
    -- SSMS
    -- הכתיבה למסך לוקחת הרבה יותר זמן מהכתיבה לדיסק!
    SET NOCOUNT ON
    GO
    
    -- השאילתה המקורית עושה שימוש בלולאות. נסו לוותר על לולאות ככל הניתן
    -- האם צריך לחלק את הפעולה לאלפים?!?
    -- זה זיזול מוחלט בשרת :-) תסמכו עליו שאין לו בעיה להכניס מליוני רשומות
    -- אם היה צורך לבצע השהייה כדי לתת למערכת זמן לבצע דברים אחרים אז מה טוב, אם היה צורך בפעולות נוספות ושחרור נעילות אז מה טוב
    -- אבל זה פעולות בשרת פיתוח לצרכי בדיקה
    DECLARE @I BIGINT=1 
    WHILE (@I<=10000000) 
    BEGIN
    	BEGIN TRANSACTION
    		INSERT INTO BASE(ID) VALUES(@I) 
    		SET @I+=1
    		
    		IF (@I%10000 = 0 )
    		BEGIN
    			COMMIT;
    		END;
    END
    GO
    
    -- הרבה יותר טוב לבצע פשוט
    insert into BASE(ID)
    select top 10000000 row_number() over(order by (SELECT NULL)) as N
    from   master..spt_values t1 
           cross join master..spt_values t2
    GO
    
    -- השאילתה המקורית: ראו הערות קודמות
    DECLARE @I BIGINT=1 
    WHILE (@I<=500000) 
    BEGIN
    	BEGIN TRANSACTION
    	INSERT INTO EventPerson(DUID,PersonInvolvedID) VALUES(@I,(SELECT TOP 1 ID FROM BASE ORDER BY NEWID())) 
    	SET @I+=1
    	IF(@I%10000=0 ) COMMIT TRANSACTION ;
    END
    GO 
    
    -- כאן נוסף לנו בעיה חדשה: שימו לב לשאילתה הפנימית שיש לנו
    SELECT TOP 1 ID FROM BASE ORDER BY NEWID()
    -- למה לבחור נתון מטבלה כשכל מה שאנחנו צריכים זה נתון רנדומלי?
    -- אנחנו יודעים שהטבלה הראשונה מלאה בנתונים מסודרים 1-10000000 אחרי הכל אנחנו הכנסנו אותם כרגע
    -- יותר מכך השאיתה הזו מנחייבת סידור הנתונים מחדש בכל פעם ומעבר על כל הנתונים בטבלה
    -- אתם מוזמנים להשוות את מהירות ההרצה של השאילתות הבאות ולהבין את הבעיה.
    -- ובוא נזכור ששאילתות אלו בעוד רגע ירוצו בלולאה כמה אלפי פעמים
    SELECT TOP 1 ID FROM BASE ORDER BY NEWID()
    SELECT ABS(CAST(NEWID() AS binary(9)) %10000000) + 1
    
    -- נכניס את הנתונים אם כן בעזרת השאילתה המשופרת הבא:
    insert into EventPerson(DUID,PersonInvolvedID)
    select top 500000 
    	row_number() over(order by (SELECT NULL)) as N
    	, ABS(CAST(NEWID() AS binary(9)) %10000000) + 1
    from   master..spt_values t1 
           cross join master..spt_values t2
    GO
    
    -- מבט מהיר על האילתה הסופית מעלה את הבעיה בנתונים שלנו
    -- כמובן שעדיף היה מראש להכניס את הנתונים אבל רק בשלב זה שמתי לב לכך ולכן נוסים אותם
    -- we need some data with BASE.IsActive=1 to check the query !
    update BASE set BASE.IsActive = 1
    where BASE.ID % 5 = 0
    GO
    
    -- נבדוק אחוז מהנתונים לדוגמה:
    -- אני לא בודק את כל הנתונים כי ההצגה שלהם על המסך לוקחת הרבה זמן
    -- ולא השאילתה עצמה בהכרח
    select * from [dbo].[BASE] tablesample(1 percent)
    select * from [dbo].[EventPerson] tablesample(1 percent)
    
    /************************************************************************************************ END DML */

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

    select
    	EventPerson.PersonInvolvedID 
    from EventPerson
    inner join Base on EventPerson.DUID = base.ID 
    where BASE.IsActive=1

    אצלי מצאתי שרשומה EventPerson.PersonInvolvedID = 5851466 נמצאת מתאימה לבדיקת השאילתה. לכן עתה אני יכול לחזור לשאילתלה המקורית של שואל השאלה ולבדוק אותה:

    select top 1000 
    	EventPerson.DUID 
    from EventPerson
    inner join Base on EventPerson.DUID = base.ID 
    left outer join EventPerson788 on EventPerson.DUID = EventPerson788.sourceid
    where (EventPerson.PersonInvolvedID = 5851466 or EventPerson788.LinkedSuspectID = 5851466) AND BASE.IsActive=1
    GO

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

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

    select * from EventPerson
    where EventPerson.PersonInvolvedID = 5851466
    GO
    select * from EventPerson788
    where EventPerson788.LinkedSuspectID = 5851466
    GO


    השאילתה הראשונה ניתן לראות שיש שימוש ב "Index Seek" אבל בשאילתה השנייה רואים שימוש ב "Clustered Index Scan". הסיבה אינה קשורה כבר לפיצול השאילתה אלא פשוט לנתונים שיש בטבלה עצמה! אני מנסה להבהיר ששאילתה שטובה במסד נתונים אחד אפילו אם הוא זהה למסד הנתונים השני לא בהכרח תיתן אותה תוצאה ולא תמיד מה שטוב מסד נתונים אחד ייה גם טוב באחר!

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

    insert EventPerson788 ([EntryID],[LinkedSuspectID],[sourceid]) values (1,5851466,1)
    insert into EventPerson788 ([EntryID],[LinkedSuspectID])
    select top 500000 
    	row_number() over(order by (SELECT NULL)) + 1 as N
    	, ABS(CAST(NEWID() AS binary(9)) %10000000) + 1
    from   master..spt_values t1 
           cross join master..spt_values t2
    GO

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

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

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

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

    select * from EventPerson788
    where EventPerson788.LinkedSuspectID = 5851466
    GO

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

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

    * לגבי השאילתה הסופית כדאי כאמור לקרוא עתה את החלק שכתב גיא ואז להגיע למיטביות של השאילתה אצלכם או לספק גם DML שמתאים לבעיה שרוצים לפתור ולא רק DDL, כי השאילתה שתתאים ל DML אחד לא תתאים בהכרח לאחר, ולכן אני תמיד מבקש DDL+DML :-)

    קריאה מהנה :-)


    signature

    • Marked as answer by DBANinja Friday, August 16, 2013 10:33 PM
    Thursday, August 15, 2013 8:27 AM
    Moderator
  • שלום , 

    תודה רבה על המענה המפורט והסקריפטים , מצטער על הסקריפט הלא מדוייק רציתי לפרסם מהר ... 

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

    דרך אגב פתרון נוסף שקיבלתי היה לכתוב 2 CTE , אחד ל EventPerson והשני ל EventPerson888 לשרשר אותם (סוג של union ) והמשך השאילתה עם תנאי ה join עליהם , גם היה יעיל , הבעיה היא מבחינת תשתית שקיימת באפליקציה ואחראית על חילול השאילתות ( nhibernate ) לא CTE או UNION נתמכים בתשתית הקיימת , כנראה שנפצל לשתי שאילתות . 

    תודה . 

    Friday, August 16, 2013 10:38 PM
  • בוק טוב

    nhibernate תומך בשימוש בפרוצדורות  וזה הדרך שניראית יותר מתאימה במקום לחלק ברמת האפליקציה ל 2 שאילתות.


    signature

    Saturday, August 17, 2013 6:38 AM
    Moderator