לאחרונה נתקלתי בבעיית ביצועים בעת עבודה עם Entity Framework שנגרמה כתוצאה משימוש במתודה DbFunctions.TruncateTime.

לטובת הדוגמא, בואו נסתכל על טבלה בשם Posts במבנה כמו בתמונה מצד שמאל.
הטבלה מכילהה מס’ עמודות, ובין היתר עמודת CreationDate מסוג datetime (או datetime2, פחות רלוונטי למקרה שלנו) שכוללת התאריך והזמן של יצירת רשומה.
נניח גם שהעמודה הזאת היא ה- clustered index של הטבלה (מדובר ב- clustered index הגיוני).

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

SELECT Id, Title, CreationDate

FROM Posts

WHERE CAST(CreationDate as date) = '2016-06-25'

אם אנחנו עושים שימוש בשליפה הזאת,  נקבל את ה- execution plan הבא:

השליפה גם מסתיימת די מהר (150ms), דורשת מעט CPU (סהכ 31ms של CPU Time) ודורשת מעט מאד IO (סה”כ  190 logical reads), שזה כמובן מאד הגיוני – בסוף אנחנו ניגשים לטווח מאד ברור ב- clustered index שלנו.  כמובן, שהיינו יכולים לעשות את זה גם יותר מפורשות ולתת טווח זמנים (ואז היינו חוסכים לחלוטין את ה- nested loop), כמו למשל עם השאילתה הזאת:

SELECT Id, Title, CreationDate

FROM Posts

WHERE CreationDate >= '2016-06-25' AND CreationDate < '2016-06-26'

שאז ה- execution plan שלנו מורכב למעשה רק מאופרטור ה- Index Seek (לא נדרש לבצע כלל את ה- cast).

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

using (var data = new Entities())

{

    DateTime dt = new DateTime(2016, 6, 26);

    var posts = data.Posts.Where(k => DbFunctions.TruncateTime(k.CreationDate) == dt)

       .Select(k => new { k.Id, k.Title, k.CreationDate }).ToArray();

}

לפני שאני אראה לכם את השאילתה שנוצרת כתוצאה מהקוד הזה, אני אספר לכם שהיא לוקחת הרבה יותר זמן מהשאילתת SQL שהראיתי קודם (שנייה וחצי לעומת 150ms), דורשת הרבה יותר CPU Time (סה”כ 7860ms) והרבה יותר IO (40,615 logical reads).
אז איך נראית השאילתה שגורמת לזה?

exec sp_executesql N'SELECT 

    [Extent1].[Id] AS [Id], 

    [Extent1].[Title] AS [Title], 

    [Extent1].[CreationDate] AS [CreationDate]

    FROM (SELECT 

    [Posts].[Id] AS [Id], 

    [Posts].[AcceptedAnswerId] AS [AcceptedAnswerId], 

    [Posts].[AnswerCount] AS [AnswerCount], 

    [Posts].[ClosedDate] AS [ClosedDate], 

    [Posts].[CommentCount] AS [CommentCount], 

    [Posts].[CommunityOwnedDate] AS [CommunityOwnedDate], 

    [Posts].[CreationDate] AS [CreationDate], 

    [Posts].[FavoriteCount] AS [FavoriteCount], 

    [Posts].[LastActivityDate] AS [LastActivityDate], 

    [Posts].[LastEditDate] AS [LastEditDate], 

    [Posts].[LastEditorDisplayName] AS [LastEditorDisplayName], 

    [Posts].[LastEditorUserId] AS [LastEditorUserId], 

    [Posts].[OwnerUserId] AS [OwnerUserId], 

    [Posts].[ParentId] AS [ParentId], 

    [Posts].[PostTypeId] AS [PostTypeId], 

    [Posts].[Score] AS [Score], 

    [Posts].[Title] AS [Title], 

    [Posts].[ViewCount] AS [ViewCount]

    FROM [dbo].[Posts] AS [Posts]) AS [Extent1]

    WHERE (convert (datetime2, convert(varchar(255), [Extent1].[CreationDate], 102) ,  102)) = @p__linq__0',N'@p__linq__0 datetime2(7)',@p__linq__0='2016-06-25 00:00:00'

מה שמעניין פה זאת השורה האחרונה, של ה- WHERE. כדי להיפטר מאלמנט הזמן, ה- datetime מומר ל- string (מסוג varchar(255)) בפורמט שלא כולל את השעה (102) ולאחר מכן מומר בחזרה ל-datetime2 – ועל בסיס זה מתבצעת ההשוואה לתאריך שסיפקנו.

ההמרה הזאת, ל-string ואז בחזרה ל-datetime, למעשה דורשת מ- SQL Server לעבור על כל השורות בטבלה, לתרגם אותם ל-string ואז ל- datetime ולעשות את ההשוואה.  ה- CAST ל- date בדוגמא הראשונה הוא גם קריאה לפונקציה, אולם הוא מאפשר ל- SQL Server לעשות שימוש עדיין בידע המוקדם שיש לו (הפרמטר) על החלק בעץ של ה- clustered index שיהיה רלוונטי.
ה-SQL שנוצר כתוצאה מ- entity framework, לעומתו, דורש המרה של כל הערכים ל- string (ואז בחזרה ל- datetime) וביצוע ההשוואה על כולם – ולא מייצר execution plan שסורק רק את החלק הרלוונטי בעץ.  זה למעשה מה שאנחנו רואים ב- execution plan.
דבר כזה דורש מ- SQL Server לעשות יותר עבודה בביצוע השאילתה (גם מבחינת CPU וגם מבחינת IO): הוא נדרש לעבור על כל השורות  (לא יכול למעשה להשתמש במבניות של ה- clustered index). הוא גם לא יכול להשתמש בסטטיסטיקות וכו’ בצורה אופטימלית. בנוסף, למשל, גם אם הטבלה הייתה מפורטשת, שינוי כזה היה גורר מעבר על כל ה- partitions ולא מאפשר partition elimination  – או בקיצור, מימוש לא מוצלח כלל.

כלומר, מימוש לא מוצלח של המתודה TruncateTime ב- Entity Framework גורר את הבעייה הזאת. כמובן שניתן לעקוף אותה בקלות – מספיק שב-C# היינו משנים את הקוד לקוד שעושה את ההשוואה על בסיס גדול מ- וקטן מ-  במקום ע”י שימוש ב- TruncateTime, כדי שנקבל שאילתה ללא בעיית הביצועים הזאת.

אז מה המסקנה הכללית מהסיפור הזה? כשעובדים עם Entity Framework, או עם כל ORM אחר – צריך לזכור שזה מוצר שפותח בידי בני אדם, ולכן לפעמים יש בה באגים, שיכולים כמו במקרה הזה) לגרור בעיות ביצועים. כאשר בעיות כאלה קורות, אנחנו רוצים שיהיו לנו את הכלים לדבג ולפתור אותם. ולכן, השימוש ב- ORM צריך להיות בתור כלי שמקל את הפיתוח והעבודה מול דטאבייסים (והופך אותם ליותר מותאמים לעולם המושגים שמשמש אותנו כאשר אנחנו כותבים קוד כיום) – ולא כתחליף לצורך לדעת SQL, ולצורך לדעת לפתור בעיות ביצועים בסיסיות כאשר מפתחים מול דטאבייס.

בהצלחה!