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

17/07/2011

ביצוע Lag (או Lead) תוך פניה אחת לטבלה

Filed under: Uncategorized — תגיות: , , — גרי רשף @ 19:42

פונקציות Lag ו-Lead עדיין אינן קיימות ב-SQL Server, אם כי ב-Oracle הן כבר אזרחיות ותיקות: הן מאפשרות לפנות מתוך רשומה לזו שלפניה (Lag) או לזו שאחריה (Lead), פעולה שהיא חיונית בשליפות המתארות סדרות נתונים לאורך זמן (שערי חליפין, סיכומים תקופתיים..) בהן אנו נדרשים להציג את השינוי באחוזים לעומת התקופה הקודמת.

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

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

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

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

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

Use AdventureWorks;

With CR As

(Select Row_Number() Over(Partition By ToCurrencyCode Order By CurrencyRateDate) Mispar,

*

From Sales.CurrencyRate)

Select CR1.ToCurrencyCode,

CR1.AverageRate [Current],

CR2.AverageRate Previous

From CR CR1

Left Join CR CR2

On CR1.ToCurrencyCode=CR2.ToCurrencyCode

And CR1.Mispar=CR2.Mispar+1

Order By CR1.ToCurrencyCode,

CR1.Mispar;

clip_image002

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

וכעת ל"תרגיל" (כשהייתי ילד היו אומרים "קונץ"):

נמספר את הרשומות כמו בדוגמה הקודמת,

ונוסיף שתי עמודות – באחת נחלק את המספר ב-2,

ובשניה נחלק את (המספר ועוד 1) ב-2:

clip_image004

שני טורי החלוקה מאפשרים לחלק את הרשומות לצמדים-

הראשונה של מספר זוגי והאי זוגי שאחריו,

והשנייה של מספר אי זוגי והזוגי שאחריו.

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

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

With CR As

(Select Row_Number() Over(Partition By ToCurrencyCode Order By CurrencyRateDate) Mispar,

*

From Sales.CurrencyRate),

T As

(Select Mispar/2 Mispar1,

(Mispar+1)/2 Mispar2,

*

From CR)

Select ToCurrencyCode,

AverageRate [Current],

Case When Mispar1<>Mispar2 Then Max(Case When Mispar2=Mispar1 Then

AverageRate End) Over(Partition By ToCurrencyCode,Mispar1)

Else

Max(Case When Mispar2>Mispar1 Then AverageRate End) Over(Partition By ToCurrencyCode,Mispar2)

End Previous

From T

Order By ToCurrencyCode,

Mispar;

clip_image006

מה לגבי היעילות?

נתייעץ עם האופטימייזר ונבקש Execution Plan:

clip_image008

למרבה ההפתעה גם הצלחנו לפנות פעם אחת לטבלה (שימו לב שבשליפה הראשונה יש שני Clustered Index Scan ובשניה רק אחד),
והתוצאות טובות יותר (אם כי השליפה הראשונה לא התיימרה להיות היעילה ביותר האפשרית).

הערה תרגיל דומה נעשה בפוסט שיפור ביצועי שאילתות על ידי שימוש ב-Group By במקום ב-Join / Exists (פתרון אלטרנטיבי בעזרת Group By).

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

9 תגובות »

  1. […] שטרם התקינו את SQL 2012 בייצור (כלומר- כולם)? אפשר להיזכר בדרך מתוחכמת לביצוע Lag תוך ביצוע Scan אחד על הטבלה שניתן להשתמש בה מגרסת 2005 וצפונה, וליישם זאת בעזרתה.. […]

    פינגבאק של בדיקת טבלת Slowly Changing Dimension בעזרת פונקציית Lag - גרי רשף — 13/01/2012 @ 23:00

  2. […] שטרם התקינו את SQL 2012 בייצור (כלומר- כולם)? אפשר להיזכר בדרך מתוחכמת לביצוע Lag תוך ביצוע Scan אחד על הטבלה שניתן להשתמש בה מגרסת 2005 וצפונה, וליישם זאת בעזרתה.. […]

    פינגבאק של בדיקת טבלת Slowly Changing Dimension בעזרת פונקציית Lag « הבלוג של גרי רשף — 13/01/2012 @ 22:55

  3. […] שטרם התקינו את SQL 2012 בייצור (כלומר- כולם)? אפשר להיזכר בדרך מתוחכמת לביצוע Lag תוך ביצוע Scan אחד על הטבלה שניתן להשתמש בה מגרסת 2005 וצפונה, וליישם זאת בעזרתה.. Like […]

    פינגבאק של בדיקת טבלת Slowly Changing Dimension בעזרת פונקציית Lag « הבלוג של גרי רשף — 13/01/2012 @ 22:54

  4. צירפתי בתפוז תוכנית הרצה שאני קיבלתי ועליה דיברתי כאן (ושם)
    התוכנית מראה מצב בו השרת בחר לעבוד בצורה מקבילית וניצל כמה מעבדים
    אתה מוזמן לעשות בזה שימוש כאן אם אתה רוצה (אני לא יכול להעלות כאן קבצים ואין שום הגיון בפתיחת בלוג רק כדי לאוויר קובץ טקסט פשוט כשאין בו תוספות או תובנות חדשות)
    * מדובר בשרת 2008r2 כאמור

    תגובה של ronen — 20/07/2011 @ 15:46

    • שוב- תודה!
      חלק מהנושאים אינם מוכרים לי לעומקם ועלי לשבת על זה..

      תגובה של גרי רשף — 20/07/2011 @ 20:41

      • כתבתי כמה מילים על הנושא בתפוז כהתחלה כולל ה HINTS באמצעותו שולטים על המעבדים
        * אני אנסה להגיע לזה מחר אם אוכל, ואז ארשום מדריכון בבלוג מסודר

        תגובה של ronen — 21/07/2011 @ 08:33

  5. בהמשך לתגובה הקודמת (לצערי אי אפשר לערוץ ולהוסיף אז אני מוסיף כאן)

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

    לכן בדקתי שוב את התמונה שוב ונראה לי שאני לא רואה אצלך את החלק של ה Order By בתוכנית

    לכן:
    א. הגדרתי לעבוד עם מעבד אחד (בלי parallel query execution) בעזרת HINTS בשאילתה => עדיין לא קיבלתי את המצופה
    ב. הורדתי מהשאילתה את Order By בסיום שבכל מקרה היה החלק הכי כבד בכל השאילתה והנתונים של האחוזים הישתנו קיצונית כמצופה

    => עכשיו עם שינויים אלו תוכנית ההרצה הראשונה נראית כמו אצלך (שוב זה דיי בניחוש כי התמונה לא ברורה אבל נראה תואם מבחינת מבנה וכן מבחינת האחוזים)

    ** כשמורידים את השימוש ב order by הוא גם בנה תוכנית הרצה ללא parallel query execution ללא צורך ב HINTS. כך שסעיף א היה מיותר אבל נחוץ לבדיקה בדרך.

    תגובה של ronen — 18/07/2011 @ 07:53

  6. בדקתי כרגע את המצב אצלי בשרת 2008r2: תוכנית ההרצה שונה לחלוטין במקרה הראשון
    * אני מקווה שלא ביצעתי שינויים במסד AdventureWorks (אני מתקין אותו מחדש כל כמה זמן בגלל שהוא משמש אותנו למשחקים אחרי הכל)

    כאמור בדקתי את השאילתות בשרת 2008r2 וקיבלתי שיש שימוש ב parallel query execution במקרה הראשון
    ההבדל באחוזים רק גובר ועמד על 95% לשאילתה הראשונה לעומת 5% לשאילתה השניה (שתוכנית ההרצה נשארה זהה)

    תגובה של ronen — 18/07/2011 @ 06:18

    • תודה רבה רונן- אני צריך לבדוק את זה יותר לעמוק, ובעיקר את ה-Parallel Query Execution.
      בפורום צירפתי את קובץ ה-Execution Plan.

      תגובה של גרי רשף — 18/07/2011 @ 21:01


RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

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

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