אפטימיזציה לבעיית Top

25/07/2014

אין תגובות

נתונה לנו בעייה פחות או יותר כזו: יש למצוא את 30 המכירות הראשונות היום של פריטים X,Y,Z;
או אולי את 10 הקפיצות לרוחק הטובות ביותר של הספורטאיות A,B,C,D,E וכו’.
לא מדובר ב-30 המכירות הראשונות של כל אחד מהפריטים (90 בסה”כ), וגם לא ב-10 המכירות הראשונות של כל אחד (שיסתכמו בסופו של דבר ל-30); אלא ב-30 של כולם ביחד ללא כל התחייבות לחלוקה הפנימית.

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

Select    *

Into    #T_Messages

From    sys.messages;

 

Select * From #T_Messages;

image

וניצור גם טבלת עזר קטנה עם כמה מספרי language_id (יש כמה עשרות):

Create Table #T_Languages (language_id Int Primary Key);

Truncate Table #T_Languages;

Insert Into #T_Languages Select 1042 ;

Insert Into #T_Languages Select 1028 ;

 

Select * From #T_Languages;

image

הבעייה עצמה אינה מסובכת: ניתן להשתמש ב-Join בין הטבלאות, באופרטור In, אך עדיף על פי כללי ה-Best Practice להיעזר ב-Exists (במקרה מסויים זה לא צפויים הבדלים בין שלוש השיטות):

Declare    @T Int=10;

 

Select    Top (@T) language_id,

        message_id

From    #T_Messages M

Where    Exists (Select    *

                From    #T_Languages L

                Where    L.language_id=M.language_id)

Order By message_id;

image

מכיוון שלא אינדקסנו את טבלת T_Messages#, הביצועים אינם משובחים: יש לבצע Scan מלא על הטבלה כדי למצוא את כל השורות הרלוונטיות, ולמיין אותן כדי לקבל את 10 הראשונות:

image

האינדקס המתבקש הוא קודם כל על language_id כדי למצוא את השורות הרלוונטיות, ולאחר מכן על message_id בשביל המיון (מכיוון שמדובר בו על מספר language_id ולא על אחד – נצטרך בכל מקרה למיין, אך נוסיף את message_id כי יחד עם language_id הם מהווים מפתח ראשי או Unique Index):

Alter Table #T_Messages Add Constraint PK_#T_Messages Primary Key Clustered(language_id,message_id);

Go

image

התוצאה כעת נראית הרבה יותר טוב.
שימו לב שהפעולה היקרה ביותר כעת היא המיון, ואיל דרך להיפטר ממנו מכיוון שאת השורות ששלפנו מהטבלה יש למיין בשביל למצוא את 10 הראשונות. מספר השורות שמויינו (שמיוצגות אנאלוגית על ידי החץ העבה מימין לפעולת ה-Sort) הוא במקרה הזה 21,084. לשם מה יש צורך בכל כך הרבה שורות (למעלה מ-10,000 מכל אחד משני ה-language_id)? הרי עבור 10 שורות ראשוננות מספיק אם נשלוף את 10 השורות הראשונות מכל language_id כי כל מה שלאחר מכן – מיותר מלכתחילה.
לשם כך ניעזר באופרטור Apply:

Declare    @T Int=10;

 

Select    Top (@T) language_id,

        message_id

From    #T_Messages M

Where    Exists (Select    *

                From    #T_Languages L

                Where    L.language_id=M.language_id)

Order By message_id;

 

Select    Top (@T) M.language_id,

        message_id

From    #T_Languages L

Cross Apply (Select    Top (@T) language_id,

                    message_id

            From    #T_Messages M1

            Where    M1.language_id=L.language_id

            Order By M1.message_id) M

Order By M.message_id;

image

אה וואלה: שיפור דרמטי בביצועים. פעולת שליפת הוזלה מכיוון ששולפים רק 10 שורות מכל language_id ולא את כל השורות (אין צורך למיין – האינדקס מותאם בדיוק לשם כך), וכך גם פעולת המיון של 20 השורות הוזלה פלאים לעומת מיון של יותר מ-20,000 במקרה הראשון (המחירים שרואים בצילום המסך הם במחירים יחסיים לאותה שאילתה עצמה ולכן לא ניתן להשוות את ה-21% ב-Seek הראשון הם הרבה הרבה יותר יקרים מאשר ה-29% שבשני).

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

כתיבת תגובה

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