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

25/10/2011

השלמת ערכים חוזרים חסרים

Filed under: Uncategorized — גרי רשף @ 11:55

נתבונן בדוגמה הבאה:

If Object_Id('tempdb..#T','U') Is Not Null Drop Table #T;
Go

Create Table #T(ID Int Identity,
                Mahlaka Int,
                Oved Varchar(10));
Go

Insert
Into #T(Mahlaka, Oved)
Select 1010,'Zeev' Union All
Select Null,'Yael' Union All
Select Null,'Vered' Union All
Select 1020,'Uri' Union All
Select 1025,'Uria' Union All
Select 1030,'Tami' Union All
Select Null,'Sinay' Union All
Select Null,'Ravit' Union All
Select Null,'Peleg' Union All
Select Null,'Orit' Union All
Select 1040,'Nadav' Union All
Select Null,'Miryam' Union All
Select 1050,'Israel';
Go

Select *
From   #T
Order By ID;
Go

clip_image002

נתונה טבלה, בעמודה Mahlaka יש להשלים את הערכים החסרים מאלו המופיעים למעלה. למשל- ליד Yael ו-Vered צריך להופיע 1010.

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

הפתרון הראשון שעולה על הדעת הוא משהו בסגנון הזה:

Select  *,
        (Select Mahlaka
        From    #T T2
        Where   ID=(Select Max(ID)
                    From   #T T3
                    Where  T3.ID<=T1.ID
                           And T3.Mahlaka Is Not Null)) Mahlaka1
From    #T T1
Order By ID;
Go

clip_image004

ב-SQL-ית זו שליפה מסובכת למדי, אבל בעברית זה נשמע די הגיוני כשמנסים לנסח את הכלל: לכל רשומה יש להתאים את שם המחלקה של ה-ID המקסימלי מבין אלו שקטנים או שווים לזה שלה (של הרשומה) ואשר שם המחלקה שלו אינו Null.. (גם בעברית זה מסובך אבל מי שינסה לנסח זאת- יגיע בסוף למשהו כזה).

לחילופין ניתן לעשות זאת על ידי Join ובאותה לוגיקה:

Select  T1.ID,
        T2.Mahlaka,
        T1.Oved
From    #T T1
Inner join #T T2
        On T2.ID=(Select Max(ID)
 From    #T T3
 Where   T3.Mahlaka Is Not Null
                   And T3.ID<=T1.ID)
Order By T1.ID;

clip_image006

ובדרך שונה לחלוטין:

With T As
(Select *,
        ID-Row_Number() Over(Partition By Mahlaka Order By ID) N
From    #T),
S As
(Select *,
        Min(ID) Over(Partition By N,Mahlaka) M
From    T)
Select  S1.ID,
        S2.Mahlaka,
        S1.Oved
From    S S1
Inner Join S S2
        On (S1.M=S2.M And S1.Mahlaka Is Not Null And S2.Mahlaka Is Not Null)
            Or (S1.M=S2.M+1 And S1.Mahlaka Is Null And S2.Mahlaka Is Not Null)
Order By S1.ID;

בצילום המסך הראשון רואים את שליפת המשנה הפנימית: אני מחלק את הרשומות לקבוצות לפי ההפרש בין ה-ID שלה והמספור לפי חלוקה לפי מחלקות, וכך כל רשומה שיש לה מחלקה היא קבוצה בת רשומה אחת (למשל ID=5), וכל קבוצת רשומות ללא מחלקה השייכת למחלקה היא קבוצה (למשל ID=7,8,9). לשתי קבוצות אלו N=5 והן נבדלות במחלקה (לראשונה יש ולשניה אין).

בצילום המסך השני (בו ביצעתי שינוי קוסמטי בשליפה לצורך ההדגמה) אני מחשב לכל קבוצה כזו את ה-ID המינימלי שלה, וכך אוכל להתאים לכל קבוצת רשומות חסרות מחלקה את המחלקה של הקבוצה בת הרשומה האחת שלפניהן. למשל- ל-Sinal & Ravit & Peleg אוכל להתאים את 1030 של Tami מכיוון שה-M שלה (6) קטן ב-1 משלהם (7).

צילום המסך השלישי רואים את השליפה כולה בה התאמנו את Tami עם מחלקה 1030 ל-Tami & Sinal & Ravit & Peleg.

clip_image008

clip_image010

clip_image012

ולבסוף- האם גרסת Denali- SQL Server 2012- על שלל חידושיה יכולה לתרום לנו משהו?

הנה דוגמה מעט מסורבלת שאולי אמצא דרך להפוך אותה למעט יותר אלגנטית:

Select  ID,
        Stuff(Max(Iif(Mahlaka Is Not Null,Right(Replicate(0,10)+Cast(ID As Varchar(max)),10)+Cast(Mahlaka As Varchar(Max)),Null))
                Over(Order By ID Rows Between Unbounded Preceding And Current Row),1,10,'') Mahlaka,
        Oved
From    #T
Order By ID;

clip_image014

הסבר קצר: במקור זה נראה כך-

Select  ID,
        Mahlaka,
        Max(Iif(Mahlaka Is Not Null,ID,Null))
            Over(Order By ID Rows Between Unbounded Preceding And Current Row) MaxID,
        Oved
From    #T
Order By ID;

clip_image016

לכל רשומה חישבנו את המקסימום עד אליה של ה-ID מבין אלה שיש להם מחלקה. כך כל רשומה קיבלה בעמודה MaxID את ה-ID של העובד שהמחלקה שלו היא המתאימה לה.

כיצד נתאים לה את המחלקה עצמה? אפשרות אחת היא לבצע כעת Join בין השליפה הזו לבין הטבלה המקורית, והאפשרות שאני בחרתי היא לשרשר ל-ID את המחלקה כך שהמקסימלי ישאר מקסימלי, וממה שיתקבל לחלץ לבסוף את המחלקה.

הפכתי את ה-ID למחרוזת בת 10 תווים ושירשרתי אליה את המחלקה. הסדר הלקסיקלי ישאר במקרה זה זהה למקורי, ומהערך המקסימלי שיתקבל יש למחוק את עשרת התווים הראשונים (ה-ID) בעזרת הפונקציה Stuff.

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

איזו שליפה הכי יעילה?

בדקתי את ה-Execution Plan, לא רק שבדקתי את ה-Execution Plan אלא שבדקתי שוב לאחר שהוספתי לטבלה Clustered Index מתבקש על עמודת ה-ID, ולהשלמת התמונה מילאתי את הטבלה בכמות גדולה יותר של נתונים (כ-14000 שורות) ובדקתי שוב עם ובלי Clustered Index.

Insert
Into    #T(Mahlaka, Oved)
Select  Case When DateKey%5=0 Then Cast(Right('00'+Cast(CurrencyKey As Varchar),3)+Right('000'+Cast(DateKey As Varchar),4) As Int) Else Null End Mahlaka,
        Cast(EndOfDayRate As Varchar) Oved
From    AdventureWorksDWDenali.dbo.FactCurrencyRate;

(בגרסאות ישנות יותר שמות העמודות בטבלה הן DataID ו-CurrencyID)

יצירת Clustered Index:

Alter Table #T Add Constraint PK_#T Primary Key Clustered (ID);
Go

והתוצאות (קישור לקבצי ה-Execution Plan בתוך הטבלה):

שליפה 13 שורות 13 שורות 14000 שורות 14000 שורות
ללא אינדקס עם אינדקס ללא אינדקס עם אינדקס
1 16% 8% 76% 1%
2 16% 12% 7% 1%
3 50% 67% 12% 68%
4 17% 13% 5% 30%

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

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

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

אין לי הסבר לתופעה הזו, ואני חושד שיש איזה באג בתמחור שלה: השליפה הרביעית ממויינת לפי ה-Clustered Index שלה, וגם חישוב ה-Max במסגרת שבפונקציית החלון היא לפי סדר עולה של ה-ID; ומכאן שאין כלל צורך למיין!

מי שיעיין ב-Execution Plan של השליפה הראשונה יראה ששם אין שלב של Sort מכיוון שהסט ממויין מלכתחילה.

יש לי איפוא חשד שהשליפה הרביעית היא היעילה ביותר למרות "התוצאות הרשמיות".

מסקנות:

1. שווה לבדוק.

2. שליפה יותר מתוחכמת או יותר מקורית אינה בהכרח יותר יעילה.

3. אינדקסים, אינדקסים, אינדקסים..

4. כדאי לשים לב לפונקציות החלון החדשות של Denali.

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

Update T1
Set    Mahlaka=IsNull(Mahlaka,(Select Mahlaka
                              From   #T T2
                              Where  ID=(Select Max(ID)
                                        From    #T T3
                                        Where   T3.ID<=T1.ID
                                                And T3.Mahlaka Is Not Null)))
From   #T T1;
Go

Select *
From   #T
Order By ID;
Go

image

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

תגובה אחת »

  1. היי גרי,
    זה דרור אם לא זיהית,
    מאמר מאד מעניין

    תגובה של drorch11 — 03/01/2012 @ 15:20


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

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