Paging לפי קבוצות

19/04/2012

תגיות: , , ,
אין תגובות

פוסט זה עוסק בשליפה של שורות X-Y מקבוצות שונות בטבלה (למשל- מכירות 5-8 לפי הסכום של כל אחד מהמוכרים בארגון).
עסקתי מספר פעמים בעבר בנושא ה-Paging, בעיקר בעקבות הכלי היעודי לנושא שהוצג בגרסת 2012, והפעם מדובר בהרחבה והכללה של הנושא: במקום שורות 5-8 מטבלת המכירות, נרצה להציג את שורות 5-8 של כל אחד ואחד מהמוכרים בטבלת המכירות.
ניתן לראות את זה גם כהכללה של בעיית ה-Top למספר קבוצות: בעבר הראיתי כיצד להציג את X השורות הראשונות של כל אחת מהקבוצות, וכעת נראה את X-Y השורות החל משורה X וכלה בשורה Y (נו טוב- החל משורה X+1 וכלה בשורה Y אם לדייק).
לא יותר מאתגר טכני- אין לי משימה מעין זו במקום העבודה, אבל כמו הטף בבית- אני בודק גבולות..
ולענייננו: ניצור את הטבלאות המאונדקסות המסורתיות בעזרת sys.messages ו-sys.languages:

Use tempdb;

Go

 

If Object_ID('Messages','U') Is Not Null Drop Table Messages;

Go

 

Select  *

Into    Messages

From    sys.messages;

 

Alter Table Messages Alter Column language_id Int Not Null;

Go

 

Create Unique Clustered Index Idx_Messages On Messages(language_id,message_id);

Go

 

Select  *

From    Messages;

 

If Object_ID('Languages','U') Is Not Null Drop Table Languages;

Go

 

Select  *

Into    Languages

From    sys.syslanguages;

 

Alter Table Languages Add Constraint PK_Languages Primary Key Clustered (Lcid);

Go

 

Alter Table Messages Add Constraint FK_Languages_Messages Foreign Key(language_id) References Languages;

 

Select  *

From    Languages;

clip_image002

ולהלן שתי השליפות- הראשונה בעזרת אופציית ה-Paging של 2012, והשניה בשיטות "המסורתיות" בעזרת Row_Number שהתגלת כיעילה ביותר בפוסט הקודם בנושא, בשני המקרים של שורות 101-150:

Select *

From   Languages L

Cross Apply (Select *

            From    Messages M1

            Where   M1.language_id=L.Lcid

            Order By message_id

            Offset 100 Rows Fetch Next 50 Rows Only) M;

 

Select *

From   Languages L

Cross Apply (Select *

            From    (Select  *,

                             Row_Number() Over(Order By message_id) Mispar

                    From     Messages M1

                    Where    M1.language_id=L.Lcid) M

            Where   Mispar Between 101 And 150) M;

clip_image004

(1100 row(s) affected)

Table 'Messages'. Scan count 34, logical reads 238, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Languages'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

(1100 row(s) affected)

Table 'Messages'. Scan count 34, logical reads 232, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Languages'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

גם לפי ה-Execution Plan וגם לפי ה-IO Statistics – האפשרות השנייה "הוותיקה" היא היעילה יותר (פחות Logical Reads מ-Messages).

מה יקרה אם נחשוק דווקא בשורות 11-40 מכל קבוצה?

Select *

From   Languages L

Cross Apply (Select *

            From    Messages M1

            Where   M1.language_id=L.Lcid

            Order By message_id

            Offset 10 Rows Fetch Next 30 Rows Only) M;

 

Select *

From   Languages L

Cross Apply (Select *

            From    (Select *,

                            Row_Number() Over(Order By message_id) Mispar

                    From    Messages M1

                    Where   M1.language_id=L.Lcid) M

            Where   Mispar Between 11 And 40) M;

clip_image006

(660 row(s) affected)

Table 'Messages'. Scan count 34, logical reads 185, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Languages'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

(660 row(s) affected)

Table 'Messages'. Scan count 34, logical reads 179, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Languages'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

לפי ה-Execution Plan מדובר במהפך דרמטי והפעם ה-Paging של 2012 טוב פי שניים מהשימוש ב-Row_Number,

אלא שה-Statistics IO מציג תמונה הפוכה והפער לטובת ה-Row_Number נשמר ואולי אף גדל במעט.

הכיצד? מתברר שהיכולת של המערכת לנתח את השימוש בשאילתת המשנה עם ה-Row_Number מוגבלת, ובשני המקרים ה-Execution Plan שלה זהה והמערכת מתמחרת אותה ב-0.10 בערך ו"מצפה" ל-306 שורות. למעשה לא משנה אילו מספרים נכתוב בתנאי – ה-Execution Plan לא ישתנה.

אפשר להבין שכל עוד לא הרצנו בפועל את השאילתה- למערכת אין דרך לדעת (מראש) אילו ערכים יחזיר ה-Row_Number. נכון שבמקרה זה אנחנו יכולים להעריך זאת, אך לא תמיד זה כך.

מסקנה- ה-Execution Plan אינו תמיד אמין בניתוח שאילתות המתבססות על שאילתות משנה, וכדאי להיות ערים לנקודה זו.

אגב- גם לגבי ה-Paging של 2012 ההערכות של ה-Execution Plan אינן מדוייקות, אך לפחות יש קשר ישיר בינן למציאות: במקרה הראשון הציפיה הייתה ל-1700 שורות וחזרו 1100, ובמקרה השני הציפיה הייתה ל-1020 שורות וחזרו 660.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *