Temporal Tables

13/07/2015

אין תגובות

גרסת 2016 בדרך אלינו, וזה הזמן להתחיל לבדוק מאילו חידושים אנחנו צפויים להינות (לפחות אלו שיתקינו את הגרסה שתצא במהלך 2016, וכמובן הבלוגרים הבלתי נלאים).
אז In Memory Tables זה כבר פאסה, 2016 זה 2014 החדש, ובפתח כבר מתדפקות Temporal Tables..
האמת שזה לא שוס כזה גדול, ולא כל חידוש הוא מהפיכה (In Memory Tables כן היו שוס וכן היו מהפיכה), אבל לכל זמן ועת לכל חפץ, וגם לזה נמצא שימוש הולם בבוא העת.
נעזוב את המליצות וניגש לעניין: Temporal Tables זוהי אופציה שמאפשרת לשמור גרסאות קודמות של הטבלה, לבדוק מה היא כללה בתאריך מסויים או בתקופה מסויימת וכו’. מזכיר קצת את ה-CDC מבחינת הפונקציונליות, אבל הרבה יותר נוח ושונה לחלוטין.
השיטה: יוצרים טבלה הכוללת שתי עמודות תאריך מסוג Date2 המציינות את תחילת התקופה וסוף התקופה (התקופה בה השורה בתוקף), וטבלת היסטווריה בעלת אותו מבנה. אנחנו מכניסים נתונים לטבלה, והם אוטומטית מקבלים על ידי המערכת את תאריכי התקופה בין מועד ההכנסה לסוף העולם (שעל פי נבואות מיקרוסופט יקרה ב-31/12/9999, אחת חלקי עשרה מיליון של שניה לפני חצות; כלומר: 9999-12-31 23:59:59.9999999). מרגע זה, בכל פעם שנשנה איזו שורה בטבלה, השורה המעודכנת תישאר בטבלה כשתאריך ההתחלה הוא מועד העדכון (תאריך הסיום תמיד יהיה סוף העולם), והגרסה הקודמת תעבור לטבלת ההיסטוריה עם תאריך ההתחלה שלפני העדכון ותאריך הסיום שהוא מועד העדכון (כלומר- שני התאריכים הם היסטוריים).
איך המערכת עושה זאת? היא יוצרת טריגרים? לא: יש מנגנון שאינו חשוף למשתמש שעושה זאת בצורה יעילה יותר (כך לדברי מיקרוסופט).
איך המערכת יודעת אילו עמודות הן עמודות התוקף ומהי טבלה ההיסטוריה? על כל זה יש להצהיר בעת הקמת הטבלה.
כיצד מוודאים שהתאריכים בעמודות התוקף נכונים? זה באחריות המערכת, והמשתמש אינו יכול לשנות. חשוב לשים לזה לב, כי בניגוד לטבלאות כמו Slowly Changing Dimension Tables, איננו קובעים ביזמתנו מה התוקף של כל שורה, והמועד ב-Temporal Tables הוא מועד העדכון. המונחים המקצועיים המבקשים הם System Time (הכלי הזה) לעומת Application Time (הזמן נקבע על ידי האפליקציה, קרי- המשתמש) שכרגע אינו נתמך.

לעבודה: ניצור מערכת כזאת שתתבסס, כרגיל כאן בבלוג, על נתוני טבלת המערכת sys.messages:

If    (Select history_table_id From sys.tables Where schema_id=Schema_ID('dbo') And name='T_Messages') Is Not Null

    Alter Table T_Messages Set (System_Versioning=Off);

 

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

Go

 

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

Go

 

Create Table T_Messages_History (message_id Int Not Null, 

                                language_id Int Not Null,

                                severity Int,

                                is_event_logged Bit,

                                text NVarchar(Max),

                                FromTime DateTime2 Not Null, 

                                ToTime DateTime2 Not Null,

                                Index Idx_T_Messages_History Clustered(FromTime,ToTime,language_id,message_id));

 

Create Table T_Messages(message_id Int Not Null, 

                        language_id Int Not Null,

                        severity Int,

                        is_event_logged Bit,

                        text NVarchar(Max),

                        FromTime DateTime2 Generated Always As Row Start Not Null, 

                        ToTime DateTime2 Generated Always As Row End Not Null,

                        Constraint PK_T_Messages Primary Key(language_id,message_id), 

                        Index Idx_T_Messages Clustered(FromTime,ToTime,language_id,message_id),

                        Period For System_Time(FromTime,ToTime))

            With (System_Versioning=On 

                (History_Table=dbo.T_Messages_History, Data_Consistency_Check=On));

מה יש לנו כאן?

  • חדי העין ישימו לב שלפני פקודות ה-Drop Table בהתחלה שנועדו לוודא שהטבלאות אינן קיימות, יש פקודה מופנית שמנטרלת משהו שנקרא System_Versioning, שזו אותה הגדרת מערכת שמופיעה בהמשך ביצירת הטבלה T_Messages, ושאם לא ננטרל אותה – לא ניתן יהיה לבצע Drop לטבלאות!
  • אין חובה להגדיר מפתחות ו-Clustered Indexes, ומה שאני הוספתי – זו “הצעת הגשה”; כלומר – מפתחות ואינדקסים מומלצים בנסיבות הנתונות.
  • לטבלת ההיסטוריה (שאותה יש להגדיר ראשונה!) אסור להגדיר מפתח.
  • הגדרת טבלת T_Messages כוללת הגדרה מהן עמודות התאריך של תחילת התקופה וסוף התקופה, וקישור לטבלת ההיסטוריה T_Messages_History.
  • בתצוגה הגרפית ב-SSMS, הטבלאות יופיעו כך:
    image

וכעת נמלא אותה ונבצע סדרת שינויים, וכדי להקל על הבדיקות – נמתין דקה בין פעולה לפעולה, וניעזר בפקודה .. Set RowCount כדי לקבוע כמה שורות ישתנו (זה לא קשור לנושא ורק נועד לבדוק כמה שינינו בכל פעם):

If    (Select history_table_id From sys.tables Where schema_id=Schema_ID('dbo') And name='T_Messages') Is Not Null

    Alter Table T_Messages Set (System_Versioning=Off);

 

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

Go

 

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

Go

 

Create Table T_Messages_History (message_id Int Not Null, 

                                language_id Int Not Null,

                                severity Int,

                                is_event_logged Bit,

                                text NVarchar(Max),

                                FromTime DateTime2 Not Null, 

                                ToTime DateTime2 Not Null,

                                Index Idx_T_Messages_History Clustered(FromTime,ToTime,language_id,message_id));

 

Create Table T_Messages(message_id Int Not Null, 

                        language_id Int Not Null,

                        severity Int,

                        is_event_logged Bit,

                        text NVarchar(Max),

                        FromTime DateTime2 Generated Always As Row Start Not Null, 

                        ToTime DateTime2 Generated Always As Row End Not Null,

                        Constraint PK_T_Messages Primary Key(language_id,message_id), 

                        Index Idx_T_Messages Clustered(FromTime,ToTime,language_id,message_id),

                        Period For System_Time(FromTime,ToTime))

            With (System_Versioning=On 

                (History_Table=dbo.T_Messages_History, Data_Consistency_Check=On));

 

Set        RowCount 100000;

Insert 

Into    T_Messages(message_id,language_id,severity,is_event_logged,text)

Select    message_id,language_id,severity,is_event_logged,Null

From    sys.messages

Where    language_id<>1044;

 

WaitFor Delay '00:01';

 

Set        RowCount 1;

Update    T_Messages

Set        text=Concat(text+'; ',GetDate());

 

WaitFor Delay '00:01';

 

Set        RowCount 10;

Delete

From    T_Messages

Where    language_id<>1033;

 

WaitFor Delay '00:01';

 

Set        RowCount 100;

Insert 

Into    T_Messages(message_id,language_id,severity,is_event_logged,text)

Select    message_id,language_id,severity,is_event_logged,Null

From    sys.messages

Where    language_id=1044;

 

WaitFor Delay '00:01';

 

Set        RowCount 1000;

Update    T_Messages

Set        text=Concat(text+'; ',GetDate());

 

WaitFor Delay '00:01';

 

Set        RowCount 10000;

Delete

From    T_Messages;

 

Set        RowCount 0;

את 5 הדקות ניצלנו לשתיית קפה או לעדכון סטאטוס בפייסבוק, ונחזור לעבודה.
לבצע שליפה * Select מטבלה ללא תנאי Where או Top נחשב להרגל מזיק, אבל אני בעוונותי עושה זאת מדי פעם, כמו במקרה זה:

Select    *

From    T_Messages;

 

Select    *

From    T_Messages_History;

 

Select    *

From    T_Messages

For    System_Time Contained In ('0001-01-01','9999-12-31 23:59:59.9999999');

image

כדאי כבר לקחת בחשבון שהתאריכים והשעות ב-2 העמודות הימניות בכל השליפות מוזנים על ידי המערכת בנקודת הזמן בה הפעולה התבצעה, ולכן מי שיריץ אצלו – יקבל תאריכים ושעות אחרים.
השליפה הראשונה של השורות שבתוקף, ולכן לכולן תאריך פקיעה בסוף העולם.
השליפה השנייה של השורות ההיסטוריות, ולכן לכולן תאריכי פקיעה היסטוריים.
השליפה השלישית היא כבר חידוש: היא אמנם פונה לטבלה השוטפת, אך כוללת אופרטור חדש שמוסיף גם את טבלת ההיסטוריה, ובמקרה זה ההגדרה היא – כל השורות שהיו בתוקף בין התאריכים המצויינים; ומכיוון שהתאריכים הם של בריאת העולם וסוף העולם, יחזרו לנו כל השורות, הווה אומר: Union All של שתי השליפות הראשונות (ניתן לוודא זאת על פי מספר השורות בשלושת הסטים). כדאי לשים לב שכל ערך יחודי במפתח יכול לחזור מספר פעמים, כמספר השינויים:

Select    *

From    T_Messages

For    System_Time Contained In ('0001-01-01','9999-12-31 23:59:59.9999999')

Where    language_id=1030

        And message_id=21;

image

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

כאן הגענו ללב העניין: כדי לתחקר את הטבלאות ולראות מה היה בתוקף מתי, ניתן להימנע מתחקור שתי הטבלאות ושימוש ב-Union All, ולהיעזר במקום באופרטורים החדשים שנועדו לכך.
הראשון שהיכרנו הוא For System_Time Contained In בעזרתו מחפשים שורות שהיו בתוקף בתוך התחום המצויין (כלומר- התחילו לא לפני, וסיימו לא אחרי).
אופציה נוספת – המצב שהיה בתוקף בנקודת זמן מסויימת, למשל:

Select    *

From    T_Messages

For        System_Time As Of '2015-07-12 13:15';

במקרה זה קיבלנו 99,990 שורות שנותרו לאחר שהכנסנו 100,000, שינינו אחת (לא השפיע על מספר השורות), ומחקנו 10.
כי לבחון את יעילות האינדקסים שהוגדרו בהתחלה, נריץ שוב את זו האחרונה, ובהמשך אליה שליפה ממוקדת שתפנה למפתח ספציפי:

Select    *

From    T_Messages

For        System_Time As Of '2015-07-12 13:15';

 

Select    *

From    T_Messages

For        System_Time As Of '2015-07-12 13:15'

Where    language_id=1030

        And message_id=29275;

image
שתי השליפות מאוד יעילות: כל אחת מהן פונה לשתי הטבלאות, משתמשות ב-Seek ומבצעת Union All עם התוצאות.
השליפה הראשונה הכללית משתמשת ב-Clustered Indexes,
והשניה הממוקדת ב-Non Clustered Indexes.
במקרה זה כיסיתי את כל האופציות

שתי אופציות נוספות לפילטור (בנוסף ל-Contained ו- As Of) הן Between ו-From-To:

Select    *

From    T_Messages

For    System_Time Between '2015-07-12 13:15' And '2015-07-12 13:17';

כל השורות שהיו בתוקף ולו בחלק מהתקופה שמתחילה בתאריך הראשון ומסתיימת בשני.

וגם:

Select    *

From    T_Messages

For    System_Time From '2015-07-12 13:15' To '2015-07-12 13:17';

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

מבחינת הביצועים: המערכת מתרגמת את התנאים באופרטורים השונים של For System_Time לתנאים המתבקשים של גדול\קטן\קטן-שווה וכו’; ומוסיפה גם את התנאי FromTime<>ToTime. במקרים מסויימים יש לתנאי הזה השפעה זניחה יחסית על הביצועים.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

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