TSQL challenge

אתגר רבותי אתגר,

החודש (מאי 2017) שלחתי אתגר לצוות SQL Server בנאיה, האתגר גרף מספר תגובות מעניינות ולכן החלטתי לשתף את הקהל הרחב –

לפני זמן לא רב, שאל אותי חבר שאלה על SQL שהם ניסו לבצע בחברה,

החבר הינו מפתח ותחילה ניסה הוא לכתוב את הקוד. לאחר מספר ניסיונות העביר המפתח את הבעיה לDBA והוא ניסה את מזלו גם כן.

האתר ניראה לי חביב מאוד ובעל מספר גישות אפשריות להגיע לפתרון אלגנטי,

האתר נכתב כאילו הוא ראיון עבודה – האם הייתם עוברים אותו בהנחה שקיבלת את האתגר לפתור בבית.

מבחן הכניסה –

לארגון קיים תהליך שטוען נתונים לטבלה כל 15 דקות.

הנתונים לא חשובים כל כך לאתגר. אך, ניתן להניח שכל 15 דקות נכנסים כמות בין 100 ל5000 שורות במספרים עגולים (כלפי מאה, כלומר, 100,200,300....1000,1100 וכו,)

קיים תהליך ETL שטוען את הנתונים בBatch-ים של המספר העליון הקרוב ביום ל1000.

לדוגמה –

כאשר יופעל התהליך הוא יטען

בסבב הראשון 1100 שורות – 200 + 300 + 600.

בסבב השני יטענו 1200 שורות – 500 + 700

ההנחה של המפתח היא –

הזמן הממוצע לסבב ידוע – X

כמה סבבים יהיה עלי להריץ?

ניתן להיעזר בסקריפט הבא לטובת בניית התשתית האחדה לאתגר –

שים לב!

הנחת הבסיס הוא שהפתרון הינו קבוע בזמן, כלומר שורות לא ממשיכות להיכנס לטבלה מעבר לנתונים שהוצגו בשאלה.
השורות לפני הפתרון יכילו – (נא לא לנסות לפתור את האתגר על שרתי הייצור J)

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

השורה לאחר סעיף 2 תהיה –

DECLARE @StartTime datetime = getdate();

הפתרון אמור להיות שורה אחת בתבנית הבאה –

SELECT '<< YOUR NAME>>' [DBA],<<YOUR SOLUTION>> [Result], DATEDIFF(MICROSECOND,@StartTime,getdate()) [Duration]

אם ברצונך לנסות את מזלך עצור את הקריאה כאן וכתוב את הפתרון שלך לאתגר.

Try your best and write your code here

דרך השוואת הפתרונות -

בדיקת זמנים ע"פ עמודת ה Duration.
הפתרונות שהגיעו לתשובה הנכונה – 11 יעברו לבדיקות נוספות.

בדיקות נוספות:

העמסה של טבלת העבודה ב2048 ערכים –

הרצת הסקריפטים יחד עם Profiler ומדידת :

Duration
IO – Read\Write
CPU

שמח להציג חלק מייצג מהפתרונות והדעות של החברים בצוות –

פתרון (Common Table Expressions(CTE של מקסים שמידט –

CTE הינו אפשרות כתיבה חדשה שנוספה בSQL Server 2005 שמאפשר לייצר אובייקטים תוך כדי השליפה.

בשאילתה של מקסים המימוש הוא לטובת רקורסיה.

כאשר הקוד מחולק ל3 חלקים.

החלק הראשון המכונה - "העוגן" ומייצג את השליפה הראשונה לטובת קביעת התחלת הרקורסיה.

החלק השני - ניתן להבחין בו ע"פ השאילתה שנמצאת לאחר הUNION ALL, ומייצג את הכניסה לשאילתה שוב יחד עם השורות שהושגו בשליפה הראשונה והכניסות הקודמות לקריאה הנוכחית - ניתן להבחין בכניסה פנימה ע"י שליפה בשם הCTE בתוך החלק השני.

החלק השלישי - מציג את השליפה האחרונה מתוך הנתונים שקיבלנו בחלקים הקודמים.

Python בתוך SQL Server 2017 פתרון של איתן ליפובצקי

פתרון פשוט ומקסים שמציג את היכולת החדשה של SQL, הפתרון לא הכי מהיר, מכוון שנדרש לפנות למנוע שמריץ Python.

פתרון CURSOR והגיגיו של MVP גרי רשף(t|b)-

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

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

בסה"כ הפתרון צריך להיות פשוט ויעיל, כי לו היינו פותרים את הבעיה בעזרת שפה פרוצדורלית (Python, Java, C) - היינו עוברים פעם אחת בלולאה על כל הנתונים (תוך סכימה ובדיקה של הסכום המצטבר) וזהו.

SQL היא שפה דקלרטיבית (אנחנו כותבים *מה* אנחנו רוצים ולא *איך* לפתור את הבעיה) וזה מקור כוחה וגם חולשתה.

מה עושים? הפתרון היעיל במקרה זה הוא להשתמש ב-Cursor, אלא שאת כולנו חינכו בקורס המבוא ב"נאיה קולג'" ש-Cursor זה לא טוב, ו-Set Based זה כן טוב. זה כמובן לא תמיד כך. הרבה בעיות מהסוג הזה הסתדרו לאחר שב-2012 התווספו פונקציות החלון החדשות (למשל- חישוב סכום מצטבר), אלא שהבעיה הספציפית שלנו אינה נפתרת כך מכיוון שלפונקציות החלון אין דרך "לראות" את התוצאה שלה עצמה בשורה הקודמת (ניתן "לראות" את השדורה הקודמת, אך לא את התוצאה של החישוב שהתבצע שם) ולכן זה לא עובד.

הייתה לנו בעיה דומה במקום עבודתי הנוכחי: כשסכום העסקאות חוצה רף מינימלי יש לבצע פעולת גידור (בגדול- בדיוק הבעיה ששרון העלה).

ניסיתי לפתור בעזרת פונקציות חלון ולא הצלחתי.

בצר לי פניתי לגורו אב"ג (איציק בן גן t|b), גם הוא ניסה את כוחו אך לשווא.

לפני כמה חודשים הוא העביר לנו הרצאה בנאיה, וסיפר לי שכשהוא מטיף בשבחי פתרונות Set Based, הוא מקפיד לציין שיש בעיות שניתן לפתור אותן ביעילות רק בעזרת Cursor, ומביא את הדוגמה של מקום עבודתי להמחשה.

עד כאן קצת CTE רקורסיבי, שימוש ב-Cursor ופתרונות Set Based; על קצה המזלג.

הפתרון של גרי -

הפתרון הטוב ביותר של אורי כליל החורש

אורי פתר את האתגר ע"י שימוש ב2 משתנים ויצירת תלות בין השניים.

תוצאות ההרצה -