T-SQL Challenge (June 2017)
לא מזמן התבקשתי לכתוב קוד שיחזיר "מה הייתה כמות המשתמשים המקסימלית במהלך יום אתמול" וזאת כדי לוודא שהלקוח לא חורג ממכסת הרישיונות. למרבה הצער אין בדיקה און-ליין.
כל המידע הרלוונטי קיים בטבלת Audit שאוגרת המון פעילויות במערכת. בשלב ראשון חילצתי מתוכה וריכזתי את כל פעולות הכניסה והיציאה של כל המשתמשים מכל התחנות. לצורך ההפשטה נניח שקיבלנו טבלה שמנטרת את הכניסות והיציאות של כל משתמש מתחנה מסוימת במבנה הבא:
CREATE TABLE LoginEnter( [ID] INT NOT NULL IDENTITY(1,1), [user_id] INT NOT NULL, [station] [nvarchar] (255) NULL, [LoginTime] [datetime] NOT NULL, [LogoutTime] [datetime] NULL )
המטרה הסופית היא לקבל כמות משתמשים מקסימלית שהיו מחוברים למערכת בו-זמנית מתוך הטבלה.
נניח לדוגמא:
משתמש א עבד בין 12:00 ל 13:00
משתמש ב עבד בין 12:30 ל 13:00
משתמש ג עבד בין 12:56 ל 14:00
משתמש ד עבד בין 15:00 ל 16:00
התוצאה תהיה 3 כי בין השעות 12:56 ו 13:00 משתמשים א,ב ו-ג עבדו בו-זמנית
בשאר הזמן היו 2 משתמשים בו זמנית או פחות.
במילים אחרות אני אמור לסרוק את הטבלה ולתת מה היה מספר המשתמשים המקסימלי שהיה מחובר בו זמנית, לא משנה באיזו שעה או יום. לכן התוצאה של הסקריפט תהיה מספר בודד.
כיצד ניתן לגשת לבעיה:
ראשית כמובן קיימת הדרך האינטואיטיבית שבה עוברים פעמיים על הטבלה ובודקים לכל שורה את כמות השורות האחרות ש"חתכו" אותה, דהיינו מי מהמשתמשים היה כבר Logged-in כאשר X נכנס למערכת או מי עשה Logout בזמן ש X עדיין לא יצא.
כמובן שפתרון כזה הוא לא יעיל בעליל, בוודאי לא בטבלאות גדולות
בנוסף לכך, בהרבה מערכות אין זמני כניסה ויציאה למשתמש באותה שורה. יש טבלת Audit שמנטרת מלא פעילויות כולל פעולות כניסה ויציאה כך ששורת ה Login תופיע מספר שורות (או ליתר דיוק הרבה שורות...) לפני פעולת היציאה. למעשה כך גם היה אצלנו, ואני הפכתי את זה לשורה אחת רק "בשביל התרגיל"
מכאן הגיע הרעיון לנצל עובדה זו ולפתור את הבעיה בצורה מאד פשוטה:
נגדיר כל פעולת כניסה ב action_code=”Login”
נגדיר כל פעולת יציאה ב action_code=”Logout”
נגדיר מונה משתמשים בשם @max ונאפס אותו,
נגדיר מונה @max_of_max שיכיל את המספר המקסימלי ש @max הגיע אליו
כעת מה שנותר לנו זה לוודא שהטבלה ממוינת לפי זמן Login וכל מי שביצע Login נוסיף למונה @max=@max+1, וכל מיש ביצע Logout נפחית אחד - @max=@max-1
במקביל @max_of_max יקבל את הערך של @max רק אם @max>@max_of_max
בקוד זה ייראה כך:
שיטה זו מאד יעילה. אמנם בגלל מבנה הטבלה בהצגת הבעיה היה צורך לסרוק את הטבלה פעמיים, אולם במצב בו יש לנו טבלת פעילויות עם שורות נפרדות ל Login ו Logout מתבצעת סריקה אחת בלבד.
בשיטה זו יש חיסרון, שיש שימוש במשתנים (@max, @max_of_max) תוך כדי השינוי שלהם. מיקרוסופט לא מבטיחה שסדר ההצבה של המשתנים יישאר כך גם בעתיד, אם כי אני לא רואה סיבה שישנו את זה.
ב execution plan זה ייראה כך – פעמיים סריקת הטבלה ופעם אחת מיון:
בכל מקרה על אותו רעיון יש שיטה נוספת שהגה ידידי גרי רשף, וזה שימוש ב windows function, שיבצע סיכום של כל הכניסות (+1) והיציאות (-1) מהשורה הראשונה עד לשורה הנוכחית, ועל רשימת התוצאות הכוללות את סיכומי הביניים יתבצע חישוב מקסימום.
שיטה זו בטוחה יותר ונתמכת, אבל עם שני חסרונות. ראשית כמובן זה אפשרי רק מגרסת SQL server 2012, ובנוסף משלמים כאן מחיר מסוים בביצועים בזמן החישוב של ה windows function כפי שמשתקף ב execution plan. יש כאן סריקת הטבלה פעמיים (כמו בפתרון הראשון) אבל בנוסף יש סריקה נוספת בה לכל שורה מתבצע עוד מעבר על הטבלה במספר משתנה של שורות - מאפס עד לכמות השורות פחות אחד. למשל בשורה 20 יש סיכום של כל 19 השורות שקדמו לה. ולבסוף מתבצע MAX על התוצאות.
בקוד זה ייראה כך:
כך זה נראה ב execution plan:
סיכום
הצגתי במאמר, שתי שיטות לקבלת כמות מקסימלית של משתמשים במהלך יום מסוים.
השיטה הראשונה יעילה אבל לא נתמכת לכן נשתמש כאשר ביצועים הוא הפרמטר החשוב. במידה ונשתמש בה בסביבת ייצור נצטרך לבדוק אותה בכל שדרוג של SQL server שלא השתנה משהו בתהליך.
השיטה השנייה אולי פחות יעילה. אבל, נותנת לנו שקט נפשי J. כאשר כמות הרשומות לא גדולה או שביצועים אינם חשובים (נניח שמספיק לה לרוץ פעם אחת בשעות הלילה) נעדיף להשתמש בשיטה זו.
