content top
שימוש בפונקציות חלון מתקדמות לשיפור דרסטי בביצועים

שימוש בפונקציות חלון מתקדמות לשיפור דרסטי בביצועים

צצה בעייה כדלקמן: רוצים לשלוף את השורה הראשונה מכל יום, מטבלה הממויינת לפי עמודת התאריך. מכיוון שהטבלה ממויינת, לכאורה אין צורך למיין אותה שוב, אבל כדי לחלק את הטבלה ל-Partitions על פי ימים שלמים (אינני מתכוון לחלוקה פיזית אלא לפרמטר בפונקציות החלון) – יש צורך להפעיל פונקציה על התאריך, וכתוצאה מכך המערכת אינה משתמשת באינדקס, למרות שהדעת נותנת שהסדר לא צריך להשתנות ושניתן להפעיל "שיקול דעת" במסגרת האופטימייזר.
דוגמה להבהרת הבעייה, ניצור טבלה עם כ-250,000שורות (טבלת sys.messages בגרסת 2012), ונחשב לה באופן מלאכותי עמודת תאריך שתשמש כ-Clustered Index (כלומר – תישמר ממויינת פיזית על פיו):

If Object_ID('T_Messages','U') Is Not Null Drop Table T_Messages;

Select  DateAdd(Second,Cast(is_event_logged As Int),DateAdd(Minute,Severity,DateAdd(Hour,language_id,(DateAdd(Day,message_id,Cast('19000101' As DateTime)))))) Taarih,

        *

Into    T_Messages

From    sys.messages;


Create  Clustered Index Idx_T_Messages On T_Messages(Taarih);

Select  *

From    T_Messages

Order By Taarih;

clip_image002

מי שיעיין ב-Execution Plan של ה-Select בסוף יראה שהשליפה אינה כוללת מיון מכיוון שהמערכת "יודעת" שהטבלה כבר ממויינת לפי עמודת Taarih ולכן אינה טורחת למיין לחינם.

כיצד נשלוף את שורה הראשונה מכל יום (הראשונה מ-03/05/1900, הראשונה מ-06/03/1900 וכו')? להלן מספר אפשרויות העושות שימוש ב-Row_Number תוך ביצוע Partition לפי התאריך השלם ומיון לפי התאריך, ושולפות את השורה הראשונה בכל Partition.

Partition לפי שנה, חודש ויום (בחודש):

With T As

(Select    Row_Number() Over(Partition By Year(Taarih),Month(Taarih),Day(Taarih) Order By Taarih) Nm,

           *

From       T_Messages)

Select     *

From       T

Where      Nm=1;

Partition לפי מספר היום (כלומר- מספר הימים השלמים שעברו מאז "תחילת הספירה" של מיקרוסופט):

With T As

(Select    Row_Number() Over(Partition By DateDiff(Day,0,Taarih) Order By Taarih) Nm,

           *

From       T_Messages)

Select     *

From       T

Where      Nm=1;

Partition לפי מעבר (Cast) לסוג נתון Date שכולל תאריכים שלמים בלבד:

With T As

(Select    Row_Number() Over(Partition By Cast(Taarih As Date) Order By Taarih) Nm,

           *

From       T_Messages)

Select     *

From       T

Where      Nm=1;

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

With T As

(Select    Lag(Taarih,1,Null) Over(Order By Taarih) TaarihKodem,

           *

From       T_Messages)

Select     *

From       T

Where      IsNull(DateDiff(Day,Taarih,TaarihKodem),-1)<>0;

clip_image004

שני דברים בולטים בהשוואה בין ארבעת ה-Execution Plans:

1. לשלוש השליפות הראשונות העושות שימוש ב-Row Number אותו Execution Plan בדיוק, כלומר- שיטת חישוב ה-Partition אינה משמעותית; ובשלושתן עיקר העלות היא המיון של הנתונים עבור ה-Partition למרות שהמיון מיותר כי הנתונים כבר ממויינים, אך המערכת אינה "מבינה" למרבה הצער שהמיון לפי Taarih או לפי (Cast(Taarih As Date – או כל אופציה אחרת – אינה משנה את סדר הנתונים. מבחינתה, אם הפעלנו פונקציה על העמודה – היא לא תשתמש באינדקס (כלומר- היא כן תשלוף נתונים מה-Clustered Index ותבצע עליו Scan, אבל תתעקש למיין מחדש).

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

אם זה לא שיפור בביצועים, שיפור בביצועים מהו?


5 תגובות על “שימוש בפונקציות חלון מתקדמות לשיפור דרסטי בביצועים”

  1. הי גרי,

    אחלה פוסט! נהניתי לקרוא.
    אני רוצה להציע עוד דרך לבצע את השליפה הזאת – באמצעות טבלת עזר של תאריכים ו-CROSS APPLY.

    הנה הקוד:

    CREATE TABLE
    dbo.Dates
    (
    DateValue DATETIME NOT NULL ,

    CONSTRAINT
    pk_Dates_c_DateValue
    PRIMARY KEY CLUSTERED
    (DateValue ASC)
    );
    GO

    INSERT INTO
    dbo.Dates
    (
    DateValue
    )
    SELECT DISTINCT
    DateValue = CAST (CAST (Taarih AS DATE) AS DATETIME)
    FROM
    dbo.T_Messages
    ORDER BY
    DateValue ASC;
    GO

    SELECT
    *
    FROM
    dbo.Dates AS Dates
    CROSS APPLY
    (
    SELECT TOP (1)
    *
    FROM
    dbo.T_Messages AS T_Messages
    WHERE
    T_Messages.Taarih >= Dates.DateValue
    ORDER BY
    T_Messages.Taarih ASC
    )
    AS
    FirstRowPerDate;
    GO

    אצלי על המחשב השאילתה הזאת עלתה חצי מהשאילתה שמשתמשת ב-LAG (לפחות לפי ה-Cost), וזמן הריצה שלה היה בערך שליש. מצד שני, יש לה הרבה יותר Logical Reads.
    מידת השיפור לעומת השאילתה שמשתמשת ב-Lag תלויה בהתפלגות הנתונים בטבלה, ולכן צריך לבדוק כל מקרה לגופו.

    גיא

  2. מאת דוד יצחק:

    אחלא מאמר !
    מנסיון שלי וכמו שגיא אמר מנסיון שלי תלוי בפילג הנתונים ואם אן עכי קיצון( Skewed Data) .
    להשלמת היריעה אני מפנה אתכם ל 3 מאמרים שלי בנושא אחד מהם הוא ב- SQL Serve Magazine:

    http://www.ildba.co.il/magazine/analytic-functions-sqlserver2012-part1/

    http://www.ildba.co.il/magazine/analytic-functions-sqlserver2012-part2/

    August 21, 2012 09:38 AM

    Take Advantage of SQL Server 2012's Window Functionality

    Examples of how to use the window ranking, distribution, aggregate, and offset functions

    D. Yitzhak

    SQL Server Pro

    InstantDoc ID #143309

    http://www.sqlmag.com/content1/topic/sql-server-2012-window-functionality-143309/catpath/sql-server-2012

    דוד יצחק DBA
    מנהל הפורום הצפוני של אורקל

  3. מאת ליאור קינג:

    עוד מאמר מעולה של גרי.
    גיא – LAG ו-LEAD יכולות להתייחס גם לשתי שורות אחורה/קדימה או לשלוש שורות (או מה ששמים בפרמטר) ואז זה כבר די מורכב לעשות עם CROSS APPLY.

    אני הייתי שמח אם בגרסה הבאה תהיה גם פונקציה שהוסיפו באורקל 11R2 שנקראת NTH_VALUE שהיא כמו FIRST_VALUE אבל מאפשרת לקבוע אם אתה רוצה את הערך השני או השלישי או ה-N-י (ניתן בפרמטר).

  4. מאת גרי רשף:

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

    אני משער שלו הבעייה היתה אמיתית ולא תיאורטית – הייתי הולך על הכיוון שלך.

כתיבת תגובה

האימייל לא יוצג באתר. (*) שדות חובה מסומנים

eighteen − ten =