הבלוג של גרי רשף

05/10/2010

פונקציות חלון: יתרונות וחסרונות

Filed under: Uncategorized — תגיות: , , , , — גרי רשף @ 08:36

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

הדוגמאות שלהלן הן ברובן טכניות ואין מאחוריהן שום הגיון עיסקי, והן יעשו שימוש בטבלה AdventureWorks.Sales.SalesOrderDetail שיש בה מעל 120,000 שורות (כלומר- מספיק "בשר"), כאשר השאילתות הורצו על גרסת 2005.

נתבונן בשתי השליפות הבאות וב-Execution Plans שלהן- הן מחזירות בדיוק אותה תוצאה, אלא שהראשונה משתמשת בפונקציית חלון אלגנטית והשניה בשאילתת משנה מסורבלת:

Select    *,

        Min(ProductId) Over(Partition By SalesOrderId) X

From    AdventureWorks.Sales.SalesOrderDetail;

Select    *,

        (Select Min(ProductId) From AdventureWorks.Sales.SalesOrderDetail SOD1 Where SOD1.SalesOrderId=SOD.SalesOrderId) X

From    AdventureWorks.Sales.SalesOrderDetail SOD;

clip_image002

ניתן להוריד את הקובץ, לשנות את הסיומת ל-sqlplan, ולפתוח ב-SSMS.
הפלא ופלא: השליפה השניה שכתובה באופן מסורבל ומבצעת שני Clustered Index Scan על הטבלה, יותר יעילה מהשליפה היעודית הראשונה שמבצעת Clustered Index Scan אחד אך מסתבכת בשני Nested Loops בהמשך.
הפלט של Statistics IO:

(121317 row(s) affected)

Table 'Worktable'. Scan count 3, logical reads 368530, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderDetail'. Scan count 1, logical reads 1241, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(121317 row(s) affected)

Table 'SalesOrderDetail'. Scan count 2, logical reads 2482, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

בשאילתה הראשונה התבצע Scan אחד עם 1241 קריאות לעומת השאילתה השניה בה התבצעו שני Scan עם 2482 קריאות (כפול); אבל כל זה הוא כסף קטן לעומת ההתעסקות עם הטבלה הזמנית שהמערכת יצרה- 3 Scans ומאות אלפי קריאות לוגיות.
הביצועים המסורבלים של פונקציית החלון מוזרים מאוד- לא ניתן היה לבצע זאת יותר ביעילות?

תוצאות דומות מתקבלות גם עבור הפונקציות Max, Avg, Sum, Count.

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

כדאי להעמיד בשלב זה את הדברים בפרופורציות הנכונות: לא הכל גרוע ולפונקציות החלון יש יתרון עצום, בעיקר ביכולת למספר שורות בעזרת Row_Number, Rank, Dense_Rank.

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

Select    *,

        Min(ProductId) Over(Partition By SalesOrderId) X1,

        Max(ProductId) Over(Partition By SalesOrderId) X2,

        Count(ProductId) Over(Partition By SalesOrderId) X3,

        Avg(ProductId) Over(Partition By SalesOrderId) X4

From    AdventureWorks.Sales.SalesOrderDetail;

Select    *,

        (Select Min(ProductId) From AdventureWorks.Sales.SalesOrderDetail SOD1 Where SOD1.SalesOrderId=SOD.SalesOrderId) X1,

        (Select Max(ProductId) From AdventureWorks.Sales.SalesOrderDetail SOD1 Where SOD1.SalesOrderId=SOD.SalesOrderId) X2,

        (Select Count(ProductId) From AdventureWorks.Sales.SalesOrderDetail SOD1 Where SOD1.SalesOrderId=SOD.SalesOrderId) X3,

        (Select Avg(ProductId) From AdventureWorks.Sales.SalesOrderDetail SOD1 Where SOD1.SalesOrderId=SOD.SalesOrderId) X4

From    AdventureWorks.Sales.SalesOrderDetail SOD;

Select    SOD.*,

        SOD1.X1,

        SOD1.X2,

        SOD1.X3,

        SOD1.X4

From    AdventureWorks.Sales.SalesOrderDetail SOD

Inner Join (Select SalesOrderId,

                Min(ProductId) X1,

                Max(ProductId) X2,

                Count(ProductId) X3,

                Avg(ProductId) X4

        From AdventureWorks.Sales.SalesOrderDetail SOD1

        Group By SalesOrderId) SOD1

        On SOD.SalesOrderId=SOD1.SalesOrderId;

clip_image004

ניתן להוריד את הקובץ, לשנות את הסיומת ל-sqlplan, ולפתוח ב-SSMS.
השימוש בפונקציות החלון באמת עדיף על הפעלה של מספר שאילתות משנה, אבל הבכורה שייכת עדיין לשאילתת משנה בודדת.
הפלט של Statistics IO:

(121317 row(s) affected)

Table 'Worktable'. Scan count 3, logical reads 368530, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderDetail'. Scan count 1, logical reads 1241, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(121317 row(s) affected)

Table 'SalesOrderDetail'. Scan count 5, logical reads 6205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(121317 row(s) affected)

Table 'SalesOrderDetail'. Scan count 2, logical reads 2482, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

מנגד, מה יקרה אם נרצה להפעיל כל פונקציית חלון בעזרת Partition אחר (שבמונחי שאילתת משנה פירושו Group By אחר)?

Select    *,

        Min(ProductId) Over(Partition By SalesOrderId) X1,

        Max(ProductId) Over(Partition By SalesOrderDetailId) X2,

        Count(ProductId) Over(Partition By OrderQty) X3,

        Avg(ProductId) Over(Partition By ModifiedDate) X4

From    AdventureWorks.Sales.SalesOrderDetail;

Select    *,

        (Select Min(ProductId) From AdventureWorks.Sales.SalesOrderDetail SOD1 Where SOD1.SalesOrderId=SOD.SalesOrderId) X1,

        (Select Max(ProductId) From AdventureWorks.Sales.SalesOrderDetail SOD1 Where SOD1.SalesOrderDetailId=SOD.SalesOrderDetailId) X2,

        (Select Count(ProductId) From AdventureWorks.Sales.SalesOrderDetail SOD1 Where SOD1.OrderQty=SOD.OrderQty) X3,

        (Select Avg(ProductId) From AdventureWorks.Sales.SalesOrderDetail SOD1 Where SOD1.ModifiedDate=SOD.ModifiedDate) X4

From    AdventureWorks.Sales.SalesOrderDetail SOD;

clip_image006

ניתן להוריד את הקובץ, לשנות את הסיומת ל-sqlplan, ולפתוח ב-SSMS.
לא יאומן כי יסופר: השליפה עם פונקציות החלון מבצעת Clustered Index Scan בודד לעומת חמישה בשליפה עם שאילתות המשנה, ובכל זאת השניה יעילה פי שניים ממנה. מה שקורה זה שבשליפה הראשונה באמת מתבצע Scan בודד והתוצאות הרלוונטיות נאגרות בטבלאות זמניות (Table Spool) אבל ה-Join שלהן מול השליפה הראשית מאוד לא יעיל.
הפלט של Statistics IO:

(121317 row(s) affected)

Table 'SalesOrderDetail'. Scan count 3, logical reads 1360, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 24, logical reads 1653510, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(121317 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderDetail'. Scan count 5, logical reads 5193, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

ושוב- המון התעסקות עם הטבלה הזמנית (24 Scans) בפונקציות החלון.

לסיכום:

  1. פונקציות החלון נותנות פתרון מצויין בכל מה שקשור למספור שורות.
  2. בפעולות אחרות (Sum, Max, Min, Coun, Avg..) עדיף להשתמש בשאילתות משנה למרות הסירבול ולהינות מביצועים עדיפים.
  3. לא ציינתי אבל פונקציית החלון Count אינה מסוגלת לבצע (Count(Distinct.., ושאילתות משנה כן.
מודעות פרסומת

3 תגובות »

  1. […] שהופיעו פונקציות החלון – הן לא פעם יעילות יותר. כך גם ב-פונקציות חלון: יתרונות וחסרונות בה מתברר שהביצועים שלהן […]

    פינגבאק של ביצוע Lag (או Lead) תוך פניה אחת לטבלה « הבלוג של גרי רשף — 17/07/2011 @ 19:43

  2. אי אפשר לראות את התמונות – אתה יכול להדביק ברזולוציה יותר טובה?
    ותצרף גם STATISTICS IO.
    בכל מקרה זה מוכיח שלא כל הנוצץ זהב…

    תגובה של מאיר דודאי — 05/10/2010 @ 17:15

    • צודק!
      צילומי המסך מקושרים כעת לקבצים הגרפיים המקוריים והוספתי קישור לקבצי ה-Execution Plan עצמם;
      וצירפתי את פלטי ה-Statistics IO עם הערות.

      תגובה של גרי רשף — 06/10/2010 @ 10:42


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

%d בלוגרים אהבו את זה: