Paging ללא הכלים של SQL 2012

20/03/2012

תגיות: , , ,
3 תגובות

גרסת 2012 הביאה עמה את היכולת לבצע Paging ביעילות, אך מה יעשו אלו שאינם מתכוונים לשדרג לגרסה החדשה בתקופה הקרובה?
המונח Paging מתייחס ליכולת לשלוף טווח של שורות לאו דווקא מתחילת הטבלה, כמקובל בחיפושים כדוגמת Google בהם מוצגות 50 תוצאות (נניח) ואנחנו יכולים לדפדף ל-50 התוצאות הבאות, ומשמעות הדבר שהמערכת שולפת את השורות 51-100.
בגרסת 2012 זה מתבצע בעזרת הפקודה שמבצעת כמתבקש Scan של 100 השורות הראשונות, ומתוכן היא בוחרת את 50 השורות "השניות".
כיצד ניתן לעשות זאת בכלים הותיקים והמוכרים?
ניצן להציע שלושה פתרונות (אם יש עוד- אשמח להכיר), ונניח שאנחנו מעוניינים ב-50 השורות "השלישיות", כלומר שורות 101-150:
1. שליפה ממויינת של 150 השורות הראשונות (כשאילתת משנה) ומתוכה שליפה של 50 השורות הראשונות במיון הפוך.
2. שליפה ממויינת של 150 השורות הראשונות וממנה להחסיר (על ידי Except) את 100 השורות הראשונות.
3. שליפה ממוספרת ועליה להפעיל תנאי שיחזיר את שורות 101-150.
ניצור קודם כל טבלה מתאימה ונאנדקס אותה:

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;

clip_image002

וכעת לשלוש השליפות:

Select Top (50) *

From   (Select Top (150) *

       From    Messages

       Order By language_id,

                message_id) M

Order By language_id Desc,

       message_id Desc;

 

Select  *

From    (Select Top (150) *

        From   Messages

        Order By language_id,

               message_id

        Except

        Select Top (100) *

        From   Messages

        Order By language_id,

               message_id) T;

 

Select *

From   (Select *,

               Row_Number() Over(Order By language_id,message_id) Mispar

       From Messages) T

Where  Mispar Between 51 And 100;

clip_image004

מסיבות לא ברורות המערכת אינה מקבלת את שאילתת ה-Except (השניה) כמו שהיא אלא רק כשהיא מופיעה כשאילתת משנה. מדוע? למיקרוסופט פתרונים..

כפי שניתן לראות- השימוש במספור שורות הוא היעיל ביותר, ובאופן חד משמעי.

השימוש ב-Except מעט יעיל יותר משימוש כפול ב-Top, אך זה תלוי במספר השורות המוחזרות: ככל שיוחזרו פחות שורות כך השימוש ב-Except יהיה יקר יותר כי פעולת החיסור תכלול יותר שורות.

כדאי להוסיף שתי הערות חשובות לגבי השימוש הכפול ב-Top:

1. שיטה זו היא היחידה שפועלת בגרסאות 2000 ומטה, מכיוון שהאופרטורים Row_Number ו-Except הצטרפו רק בגרסת 2005.

2. Top כפול עלול להחזיר תוצאות שגויות: אם יש למשל רק 80 שורות, ישלפו שורות 31-80 וזו כמובן שגיאה.

מה קורה כאשר רוצים לשלוף "עמודים" לפי קבוצות? (למשל- לכל Language_ID לשלוף את 50 השורות "השלישיות" לפי Message_ID)

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

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_image006

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

Select * 

From   Languages L 

Inner Join (Select *, 

                   Row_Number() Over(Partition By language_id Order By message_id) Mispar 

       From        Messages M1) M 

       On L.Lcid=M.language_id 

       And M.Mispar Between 101 And 150; 

 

Select * 

From   Languages L 

Cross Apply (Select *, 

                    Row_Number() Over(Order By message_id) Mispar 

            From    Messages M1 

            Where   M1.language_id=L.Lcid) M 

Where M.Mispar Between 101 And 150; 

 

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_image008

לקח לי קצת זמן למצוא את הדרך הנכונה לבצע את השליפה, וזאת לאחר שבנסיונות הראשונים השימוש ב-Row_Number התגלע כפחות יעיל למרות שציפיתי שימשיך להיות היעיל ביותר, ולבסוף נמצא הניסוח המתאים שמבצע Clustered Index Seek ממוקד (3) במקום Clustered Index Scan כבד (1), ואת הפילטר מבצע לפני ה-Join (3) ולא אחריו (2).

לענייננו- להלן שלוש השליפות להשוואה:

Select * 

From   Languages L 

Cross Apply (Select Top (50) * 

            From    (Select Top (150) * 

                    From    Messages M 

                    Where   M.language_id=L.Lcid 

                    Order By message_id) M 

            Order By message_id Desc) M; 

 

Select * 

From   Languages L 

Cross Apply (Select Top (150) * 

            From    Messages M1 

            Where   M1.language_id=L.Lcid 

            Order By message_id 

            Except 

            Select  Top (100) * 

            From    Messages M2 

            Where   M2.language_id=L.Lcid 

            Order By message_id) 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_image010

גם כאן השימוש ב-Row_Number הוא היעיל ביותר,

אחריו השימוש ב-Except,

ולבסוף ה-Top הכפול מגרסת 2000.

סיכום:

1. הכי טוב להשתמש בכלים של SQL Server 2012.

2. מי שאין לו- כנראה שהשימוש ב-Row_Number הוא היעיל ביותר, אך יש לבדוק זאת היטב כי זה תלוי באינדקסים ובמספר השורות.

3. מי שעדיין עובד עם גרסת 2000 ומסרב להתקדם- שישתמש בלית ברירה ב-Top כפול, אבל שיזכור שהתוצאות עלולות להיות שגויות!

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

כתיבת תגובה

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

3 תגובות

  1. פלג21/03/2012 ב 09:55

    האם זה לא מקביל באיזשהו מקום למה שכתבת כאן
    http://gerireshef.wordpress.com/2010/11/22/%D7%94%D7%A6%D7%92%D7%AA-%D7%93%D7%A4%D7%99%D7%9D-%D7%9E%D7%AA%D7%95%D7%9A-%D7%98%D7%91%D7%9C%D7%94-%D7%9E%D7%97%D7%99%D7%93%D7%95%D7%A9%D7%99-denali/
    כשחלק מהמסקנה במאמר הנ"ל שלא תמיד השימוש בכלים של 2012 נותנים את התוצאות הטובות ביותר?

    הגב
  2. גרי רשף21/03/2012 ב 20:45

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

    הגב
  3. גרי רשף21/03/2012 ב 20:56

    משה למפרט הגיב ומשום מה תגובתו נמחקה על ידי המערכת:

    כתבתי פעם על מה שקורה ב-MySQL כשעוברים עם LIMIT על שליפה במספרים גדולים (למשל מה שקורה כשגוגל מדפדף לדף 1000 בפורום).

    http://blogs.microsoft.co.il/blogs/moshel/archive/2011/02/01/mysql-limit-slow-large-limit-offsets.aspx

    מה קורה ב-SQL Server בגזרה הזו?

    (במיוחד בשליפה עם הרבה joinים, מהסוג שהוצגה אצלי)

    {עד כאן התגובה של משה}

    לא בדקתי, אבל אני נוטה להאמין שהמערכת מבצעת Scan עד לדף ה-1000, וזה לא יעיל במיוחד..

    הגב