פונקציות תאריך הן פונקציות כדוגמת DateAdd, DateDiff, DatePart ונגזרות שלהן, כלומר - פונקציות שמופעלות על תאריכים בגלל היחודיות של התאריכים, שלכאורה נראים כסוג של מספרים, אבל מתבצעים עליהם חישובים ויש להם מאפיינים שאינם קיימים באריתמטיקה.
את הטעויות ניתן לתאר בקצרה כשימוש בעמודות מאונדקסות בפונקציות תאריך, דבר המונע שימוש באינדקס, וכתוצאה מכך מתבצע scan על הטבלה.
לצורך הדוגמאות הכינותי מראש את הטבלה הבאה, כולל עמודת תאריך ו-Clustered Index עליה:
Drop Table If Exists T_Messages;
Select *,
DateAdd(Day,2*severity,DateAdd(Hour,message_id,DateAdd(Day,language_id,DateAdd(Year,-14,Cast(GetDate() As SmallDateTime))))) [DateTime]
Into T_Messages
From sys.messages
Order By [DateTime];
Create Clustered Index Idx_T_Messages
On T_Messages([DateTime])
With(OnLine=On,Data_Compression=Page);
וכעת אנחנו מחפשים את השורות שהתאריך שלהן חל בחודש הנוכחי. יש כאלה שפותרים זאת כך - השורות שהשנה שלהן היא השנה הנוכחית, והחודש שלהן הוא החודש הנוכחי, למשל אופן הבא:
Select *
From T_Messages
Where Year([DateTime])=Year(GetDate())
And Month([DateTime])=Month(GetDate());
הניסוח ה-SQL-י די ברור ואינני מזלזל בזה, אבל המערכת תבצע scan על הטבלה ולא תוכל להשתמש באינדקס.
הדרך הנכונה היא לחפש את השורות שהתאריך שלהן גדול שווה לתחילת החודש הנוכחי, וקטן מתחילת החודש הבא.
כדי למצוא את תחילת החודש הנוכחי נחשב בעזרת DateDiff כמה חודשים חלפו מתחילת הספירה ע"פ מיקרוסופט (תאריך 0 = 01/01/1900), ואת התוצאה נוסיף בחודשים - שוב לתאריך ה-0. כלומר- אם חלפו 99 חודשים מאז ה-0 (לא כולל שארית), הרי אם נוסיף לתאריך ה-0 99 חודשים - נגיע לתחילת החודש הזה. והפתרון:
Select *
From T_Messages
Where [DateTime]>=DateAdd(Month,DateDiff(Month,0,GetDate()),0)
And [DateTime]<DateAdd(Month,DateDiff(Month,0,GetDate())+1,0);
יש להודות שה-SQL קצת פחות ברור, גם בגלל החישוב המורכב עם DateAdd & DateDiff, וגם בגלל שהחישוב מתבצע פעמיים בשינוי מינורי.
ניתן לנסח את זה באופן קצת יותר אלגנטי בעזרת Outer Apply כך:
Select *
From T_Messages T
Outer Apply (Select DateAdd(Month,DateDiff(Month,0,GetDate()),0) BOM) OA --BOM=Beginning of month
החישוב של תחילת החודש מתבצע פעם אחת, וב-Where נעשה עליו חישוב משלים של הוספת חודש כדי לקבל את תחילת החודש הבא.
מה השיפור בביצועים?
נעיין ב-Execution Plan וב-IO Statistics:
גם התמחור של ה-Execution Plan ב-% וגם מספר הדפים שנקראו ממחישים את ההשפעה הדרמטית על הביצועים.
במקרה ספציפי זה, בו אנחנו רוצים לקבל נתונים מתחילת החודש ועד סוף החודש (החודש הזה, החודש הקודם, החודש של פרמטר שהועבר לפרוצדורה) ניתן להשתמש בפונקצייה EOM (קיצור של End Of Month) וממנה לגזור על פי הצורך את תחילת החודש, אבל זה יעבוד אם נרצה את כל השורות בשנה מסויימת או בחודש מסויים מכיוון שאין פונקציות שמחזירות סוף יום או סוף שנה.
כעת אנחנו רוצים לקבל את כל השורות מהיום.
טעות שנתקלתי בה גם לגבי מקרה זה וגם לגבי הדוגמה הקודמת הוא להפוך את התאריך למחרוזת הכוללת את השנה, החודש והיום (במקרה הזה) או השנה והחודש (בדוגמה הקודמת), למשל:
Select *
From T_Messages
Where Format([DateTime],'yyyyMMdd')=Format(GetDate(),'yyyyMMdd');
יש שתי דרכים נכונות לעשת זאת ולהימנע מ-scan על כל הטבלה:
אפשרות אחת היא כמו בדוגמה הקודמת, אלא שנחשב את התאריכים של תחילת היום ותחילת היום הבא, ונחפש את כל מה שבינהם;
והאפשרות השנייה היא להפוך את התאריך מ-DateTime ל-Date, שינוי שאינו מונע שימוש באינדקס:
Select *
From T_Messages
Where Cast([DateTime] As Date)=Cast(GetDate() As Date);
הערה: פעולת ה-Cast as Date לא תמנע את השימוש באינדקס, אבל כן תמנע את השימוש בסטטיסטיקה, וכתוצאה מכך המערכת עלולה לבחור ב-Plan לא אופטימלי.
ולבסוף: אנחנו מחפשים את כל השורות שהתאריך שלהן הוא במהלך 21 הימים האחרונים ואילך.
פתרון לא יעיל הוא לחשב את מספר הימים מהתאריך בטבלה להיום, ולכתוב בתנאי שהוא קטן או שווה ל-21:
Select *
From T_Messages
Where DateDiff(Day,[DateTime],GetDate())<=21;
וכך גם להוסיף 21 יום לתריך שבטבלה ולכתוב בתנאי שזה גדול או שווה ל-GetDate:
Select *
From T_Messages
Where DateAdd(Day,21,[DateTime])>=GetDate();
הדוגמה האחרונה נותנת רמז עבה מה צריך להיות הניסוח הנכון של השאילתה - במקום להוסיף 21 לתאריך בטבלה, נוריד 21 יום מ-GetDate:
Select *
From T_Messages
Where [DateTime]>=DateAdd(Day,-21,GetDate());
באופן דומה לכל הדוגמאות עד כאן ניתן לשלוף את כל השורות מהשנה שעברה, מלפני היום, מהשבוע הקודם או משבעת הימים האחרונים וכו'.
ודוגמה אחרונה - פילטר על פי הגיל.
אנחנו מעוניינים לשלוף את כל השורות שחלפו מעל 10 שנים מאז (לו היה מדובר בתאריכי לידה - היינו אומרים שגילם מעל 10). חישוב הגיל נעשה על פי ההפרש ביו שנת הלידה לשנה הנוכחית, כך שאם מישהו נולד ב-31/12/2020, הרי למחרת ב-01/01/2021 הוא בן שנה, ולחילופין - מי שנולד ב-01/01/2020 - הרי ב-31/12/2020 הוא עדיין בן 0.
והשליפה האינטואיטיבית אך השגויה:
Select *,
DateDiff(Year,[DateTime],GetDate()) DateDiffYears
From T_Messages
Where DateDiff(Year,[DateTime],GetDate())>10;
מכיוון שמתבצע חישוב על העמודה המאונדקסת DateTime - לא ניתן לנצל את האינדקס ומתבצע scan על הטבלה.
הפתרון אינו [DateTime] < DateAdd(Year,-10,GetDate()) מכיוון שאנחנו רוצים לחשב הפרש בשנים עגולות, ולכן נשתמש בפטנט מהדוגמה הראשונה, אבל הפעם עם הפרש השנים (ולא החודשים):
Select *,
DateDiff(Year,[DateTime],GetDate()) DateDiffYears
From T_Messages
Where [DateTime]<DateAdd(Year,DateDiff(Year,0,GetDate())-10,0);
יש להדגיש שהבעיה אינה הביטוי שבפסוקית ה-Select שהוא רק חישוב, אלא התנאי שב-Where.
האם ניתן לפתור בצורה כזו את כל הבעיות ולהבטיח שימוש יעיל באינדקס? התשובה היא לא.
להלן מספר דוגמאות שלי אין פתרונות עבורם המאפשרים שימוש יעיל באינדקס, ולהלן מספר דוגמאות לבעיות פתוחות:
שליפה הכוללת השוואה בין שתי עמודות, מכיוון שבמקרה זה - אם נחלץ עמודה אחת מפונקציית התאריך - השנייה תישאר בפנים, וגם אם זו שחילצנו היא המאונדקסת - בצד השני לא נקבל ערך קבוע, אלא ערך שישתנה משורה לשורה, ולכן המערכת תצטרך לבצע scan ולבדוק את כל השורות.
באופן דומה לנ"ל - שליפה הכוללת חישוב המתבסס על עמודת תאריך ועמודה המציינת מספר ימים. למשל: לכל הלוואה יש תאריך פרעון ומספר ימי חסד שיש להמתין לפני שפונים ללווה, ואנחנו רוצים לשלוף את כל ההלוואות שהתאריך + מספר ימי החסד קטן מהיום. לא משנה את מי נמקם באיזה צד של ההשוואה ב-Where, המערכת תבצע scan על הטבלה כדי לבדוק כל שורה בנפרד. כמובן שאין הכוונה למקרה בו מספר ימי החסד זהה לכולם, אלא כשלכל הלוואה מספר שונה.
שליפה הכוללת תנאי על חלק מהיממה או מהשנה וכו'. למשל - כל השורות שהן בין חצות ל-6:00 בבוקר, או כל השורות שהן בחודש ינואר (לא משנה השנה). הבעייה כאן היא שמדובר בהרבה טווחי תאריכים (ינואר השנה, ינואר אשתקד, וכו') ולא באחד כמו בדוגמאות שהעליתי.
הבעיות הנ"ל פתירות בשיטות "לא קונבנציונליות" כמו עמודה מחושבת או Indexed View, שאינני עוסק בהם כאן.
לבסוף כדאי להדגיש: השימושיות של האינדקס על עמודת התאריך באה לידי ביטוי בפסוקיות ה-Where ה-Join On, ה-Order By, ובמקרים מסויימים גם ה-Group By ופונקציות חלון; ובמקרים האלו יש לשים לב שמאפשרים למערכת להשתמש בו. לעומת זאת ב-Select לא אמורה להיות כל בעיה, ולכן אם יש בשאילתה העמודה מחושבת הכוללת עמודת תאריך ופונקציית תאריך - לא אמורה להיות כל בעיה עם זה.