מגבלות של פונקציות חלון

יום שבת, אפריל 19, 2014

פונקציות החלון שהתווספו ל-SQL Server ב”שתי פעימות” – בגרסת 2005 ובגרסת 2012 הוסיפו יכולות רבות לשפת TSQL וסייעו לסגור את הפערים מול שפות התכנות הפרוצדורליות.SQL כשפה דקלרטיבית שונה במהותה משפות התכנות הפרוצדורליות (C, Java, Basic לסוגיהם) בכך שאנחנו “אומרים” מה אנחנו רוצים (Select .. From .. Where) ולא איך לבצע את הפעולה. כמובן שזה מקל בביצוע משימות DML שגרתיות, אך מכביד בביצוע פעולות לא שגרתיות שבהם עלינו לחפש פטנטים יצירתיים כיצד להשתמש בפקודות הקיימות כך שנקבל את מבוקשנו; ולעיתים זה כרוך לא רק בפירוטכניקה וירטואוזית בכתיבת הקוד, אלא גם בביצועים גרועים.הנה למשל בעייה טיפוסית של חישוב סכום מצטבר לסט...
אין תגובות

שימוש בפונקציות חלון לשרשור ערכים מטבלה

יום חמישי, מרץ 13, 2014

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

שימוש בפונקציות חלון מתקדמות לשיפור דרסטי בביצועים

יום רביעי, אוגוסט 21, 2013

צצה בעייה כדלקמן: רוצים לשלוף את השורה הראשונה מכל יום, מטבלה הממויינת לפי עמודת התאריך. מכיוון שהטבלה ממויינת, לכאורה אין צורך למיין אותה שוב, אבל כדי לחלק את הטבלה ל-Partitions על פי ימים שלמים (אינני מתכוון לחלוקה פיזית אלא לפרמטר בפונקציות החלון) – יש צורך להפעיל פונקציה על התאריך, וכתוצאה מכך המערכת אינה משתמשת באינדקס, למרות שהדעת נותנת שהסדר לא צריך להשתנות ושניתן להפעיל "שיקול דעת" במסגרת האופטימייזר. דוגמה להבהרת הבעייה, ניצור טבלה עם כ-250,000שורות (טבלת sys.messages בגרסת 2012), ונחשב לה באופן מלאכותי עמודת תאריך שתשמש כ-Clustered Index (כלומר – תישמר ממויינת פיזית על פיו): ...
אין תגובות

CTE רקורסיבי ופונקציות החלון

יום שני, יולי 22, 2013

CTE רקורסיבי הוא הכלי החביב עלי ביותר מתוך כל מה ש-SQL Server וחבורתו מציעים לנו: לא High Availability, לא File Stream, לא Variable Tables, ואפילו לא Service Broker. יתכן שאם תופיע מודעת דרושים בה נדרש מומחה ל-CTE רקורסיבי שיכתוב רקורסיות מהבוקר עד הערב – אני אתקשר ואשאל בנימוס כמה הם רוצים שאשלם להם כל חודש תמורת הזכות.. למרבה הצער השימוש בהם אינו נפוץ, הם מתאימים לבעיות מאוד ספציפיות, ועיקר שמושן הוא באתגרים טכניים שאני מאוד מחבב; אך כמו כל מיני חידות הגיון בסגנון של איך להעביר זאב, כבשה ואבטיח בסירה מבלי שהכבשה תתנגוס באבטיח ומבלי שהזאב...
אין תגובות

ממוצעים נעים

יום שבת, אוגוסט 11, 2012

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

דוגמה ליתרונות ולחסרונות של פונקציות החלון

יום שני, יוני 4, 2012

נתקלתי באחד הפורומים בשאלה הבאה (השואל תרגם שאלה אמיתית לדוגמה טכנית ואני תרגמתי אותה חזרה לדוגמה "הגיונית"): נתון קוד של אובייקט בדטבייס, ויש לשלוף את כל האובייקטים שהם מאותו סוג ומאותה סכימה כמוהו. כלומר- אם נתון המספר 1234 שהוא הקוד של טבלה מסכימה sales, יש לשלוף את פרטי כל הטבלאות מסכימה Sales. ניצור קודם כל העתק של sys.objects כדי שנעבוד מול טבלה אמיתית ולא מול view של המערכת: Use tempdb; Go   If Object_ID('T_Objects','U') Is Not...
אין תגובות

חישוב סכום מצטבר תוך פניה אחת לטבלה

יום שישי, מרץ 16, 2012

האם ניתן לחשב סכום מצטבר (Running Sum) תוך פניה אחת לטבלה וללא שימוש בפונקציות החלון החדשות שבגרסת 2012? בפוסט מתחילת החודש טיפלתי בנושא חישובי מלאי- חישובים שכרוכים בחישוב סכומים מצטברים, והראיתי כיצד השימוש בפונקציות החלון החדשות של גרסת 2012 מייעלות את החישובים בצורה משמעותית, בעיקר מפני שמתבצע Scan אחד על הטבלה. בפוסט אחר ישן יותר הראיתי כיצד ניתן לבצע Lag & Lead תוך פניה אחת לטבלה (כלומר- ללא Join עצמי או שאילתת משנה הפונה לטבלה) וללא שימוש בפונקציות החלון החדשות של גרסת 2012; ותהיתי אם ניתן לחזור על התרגיל הזה גם כאן. ...
אין תגובות

חישובי מלאי

יום שבת, מרץ 3, 2012

ההכרזה הקרובה על SQL Server 2012 היא הזדמנות לבחון את כוחן של פונקציות החלון החדשות בחישובי מלאי. מהם חישובי מלאי? מערכת מלאי אוספת מידע על פריטים שונים- כמה נכנס למחסן, כמה יצא; וכפועל יוצא של זה- כמה יש. כניסות למחסן יכולות להיות סחורה חדשה שנקנתה, מוצרים שהושלמו ונכנסו למחסן תוצרת גמורה, סחורה שחזרה מהלקוח ועוד. יציאות מהמחסן יכולות להיות סחורה שנשלחה ללקוח, חומרי גלם שהוחזרו לספק, אובדן, גניבה ועוד. מערכת מלאי אמיתי אמורה לכלול קודים לגבי כל אחת מהתנועות האלו, אבל אנחנו נסתפק ב-1 (כניסה) ו-2 (יציאה). ...
אין תגובות

ביצוע Count Distinct ללא תמיכה מתאימה

יום שישי, יולי 22, 2011

נשאלתי בפורום בסיסי נתונים בתפוז כיצד לבצע Count Distinct באקסס, כלי שאינו תומך באופציה זו. אנחנו אנשי SQL Server יכולים להריץ בקלות שאילתה בסגנון של- Select Schema_id, Count(Distinct Type) Count_Dist_Type From sys.objects Group By Schema_id; שאילתה הסופרת כמה סוגים שונים של אובייקטים יש בכל סכימה (למשל- בסכימה 4=sys יש שני סוגים- INTERNAL_TABLE, SYSTEM_TABLE). אם בבוקר אחד נגלה שמישהו הזיז את ה-Count distinct שלנו וכעת עלינו...
אין תגובות

ביצוע Lag (או Lead) תוך פניה אחת לטבלה

יום ראשון, יולי 17, 2011

פונקציות Lag ו-Lead עדיין אינן קיימות ב-SQL Server, אם כי ב-Oracle הן כבר אזרחיות ותיקות: הן מאפשרות לפנות מתוך רשומה לזו שלפניה (Lag) או לזו שאחריה (Lead), פעולה שהיא חיונית בשליפות המתארות סדרות נתונים לאורך זמן (שערי חליפין, סיכומים תקופתיים..) בהן אנו נדרשים להציג את השינוי באחוזים לעומת התקופה הקודמת. פעולה די טריוויאלית בסביבה עסקית, אולם בכלים הקיימים כיום ב-TSQL - יש לבצע שליפות מסובכות יחסית ולא כל כך יעילות הכוללות מספר פניות לטבלה (למשל- Join של הטבלה עם עצמה כך שלכל רשומה מהטבלה האחת מותאמת הרשומה הקודמת מהטבלה השנייה). עסקתי בנושא הזה מספר פעמים בעבר: ...
אין תגובות