SQL Server: סוגים שונים של לולאות

28/08/2012

אין תגובות

SQL Server נועד לטפל בסטים בעזרת פקודות DML, ולכן השימוש בלולאות אינו טבעי עבורו.
יכול להיות שהמערכת משתמשת בלולאות מאחורי הקלעים לשליפה או עדכון טבלאות, אך מבחינת המשתמש מתבצעות טרנזקציות על סטים.
עדיין, יש נסיבות בהן נצטרך להשתמש בלולאות. למשל- בפעולות תחזוקה שונות בהן אנחנו עובדים עם טבלאות מערכת (טבלת דטבייסים, טבלת אינדקסים) בהן כל שורה מייצגת אובייקט, ויש לבצע פעולה מסויימת על כל אובייקט. יתכנו גם מקרים נדירים בהם נצטרך להשתמש בלולאות לטיפול בטבלאות "רגילות", אך ככלל- יש להימנע עד כמה שאפשר מלהשתמש בלולאות ולהעדיף פעולות DML.

כשנוצר צורך מוצדק להשתמש בלולאה- באיזו אפשרות נבחר? אילו אפשרויות יש?
כעקרון אני יכול לחשוב על ארבע אפשרויות:
1. שימוש ב-Cursor.
2. שימוש בלולאת While.
3. שירשור פקודות SQL דינאמיות.
4. שימוש ברקורסיה.
אופציית הרקורסיה היא לפרוטוקול בלבד כי היא מוגבלת ל-32 רמות (בצד חסרונות נוספים), ולכן לא אתייחס אליה בהמשך.
השימוש בשירשור הוא האופציה החביבה עלי- קוד קצר, פשוט ואלגנטי:

Declare @SQL Varchar(Max);

Select  @SQL=IsNull(@SQL+Char(13),'')+'Backup Database '+name+' To Disk=''C:\Tmp\'+name+'.bak'';'

From    sys.databases;

Print   @SQL;

--Exec  @SQL;

clip_image002

אופציה זו מתאימה בעיקר לטיפול באובייקטים שונים של המערכת, וליצירת פקודות SQL מתאימות לכל אובייקט.

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

את עיקר הפוסט אני אקדיש להלן להשוואה בין Cursor לבין While מבחינת היכולות והביצועים.

כדאי להקדים ולציין שהאזהרות הרווחות מפני שימוש ב-Cursor אין פירושן שיש להעדיף שימוש ב-While, וכוונתן שיש להעדיף עד כמה שניתן עבודה SQL-ית מול סטים, אבל אם כבר נדרשים ללולאות- אין עדיפות נתונה מראש של האחת על השנייה.

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

ל-Cursor יש יכולות מגוונות, אם כי את רובן ניתן לחקות בעזרת While: תנועה קדימה ואחורה בסט הרשומות, אופציית Static (=טבלה זמנית) וכו'.

לענייננו- ניצור טבלה מספיק גדולה, וננסה לרוץ על כל השורות בשתי השיטות:

Use tempdb;

Go

 

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

Go

 

Select * Into T_Messages From sys.messages;

Go

 

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

Go

 

Select * From T_Messages;

clip_image004

נעבור כעת על כל השורות, פעם ב-Cursor ופעם בלולאת While, ונבדוק כמה זמן עבר:

Print Convert(Varchar(Max),GetDate(),114)

 

Declare @message_id Int,

        @language_id Int;

Declare Cr Cursor Fast_Forward For Select message_id,language_id From T_Messages Order By message_id,language_id For Read Only;

Open    Cr;

Fetch   Cr Into @message_id,

                @language_id;

While   @@Fetch_Status = 0

Begin

Fetch   Cr Into @message_id,

                @language_id;

End

Close   Cr;

Deallocate Cr;

Go

 

Print Convert(Varchar(Max),GetDate(),114)

 

Declare @message_id Int,

        @language_id Int;

Select  Top 1 @message_id=message_id, @language_id=language_id From T_Messages;

While @@RowCount>0

Begin

Select Top 1 @message_id=message_id, @language_id=language_id From T_Messages Where (@message_id=message_id And @language_id<language_id) Or @message_id<message_id;

End

Go

 

Print Convert(Varchar(Max),GetDate(),114)

clip_image006

הרצתי את הקוד עשר פעמים ובממוצע ה-Cursor נמשך 18 שניות וה-While 16 שניות, ובשל השונות הרבה בנתונים אני מעריך שהביצועים פחות או יותר זהים ובמקרה קיבלנו בעשר המדידות האלו יתרון קטן לטובת ה-While.

כדאי גם להוסיף שהריצות התבצעו "על ריק" והמערכת לא ביצעה דבר (גיבוי, תחזוקה, עדכון..), כך שבמציאות הריצות ימשכו שעה ארוכה ו-18 או 16 השניות יהיו זניחות.

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

ה-Cursor מחזיק מצביע/Pointer לשורה הנוכחית בה הוא מטפל, ובפקודת ה-Fetch הוא מפנה אותו לשורה הבאה וכך הלאה. הוא אינו צריך לחפש את השורה הבאה מההתחלה, אלא עובר אליה מהקודמת.

לולאת ה-While "זוכרת" את המפתח של השורה בה היא טיפלה (שני המשתנים @message_id, @language_id), ובעזרתו פונה לשורה הבאה. במקרה זה כשהמפתח הראשי קיים גם לוגית (לא אמורות להיות כפילויות) וגם פיזית (מוגדר Primary Key בפועל שהוא גם Clusterd Index שמאפשר לגשת לכל השורה)- המערכת לא צריכה "לחפש" את השורה אלא ניגשת אליה ישירות; אך מה קורה אם אין זה כך?

נסיר את ה-Primary Key:

Alter Table T_Messages Drop Constraint PK_T_Messages;

Go

ונריץ שוב את הקוד הקודם:

clip_image008

הפעם ה-Cursor נמשך 7 שניות, וה-While 15 דקות.. מטעמים ברורים לא הרצתי את הקוד 10 פעמים והסתפקתי בזה..

ההתנהגות של ה-Cursor לא השתנתה, ומבחינתו אין הבדל בין לנוע משורה לשורה ב-Heap או ב-Clustered Index.

לולאת ה-While, לעומת זאת, נדרשת הפעם לחפש בכל איטרציה את השורה לאורך כל הטבלה ולבצע Table Scan מלא; וגם זה כי שפר עליה מזלה ויש Primary Key לוגי: אם לא היה Primary Key כלל לא ניתן היה להשתמש ב-While בצורה זו (היה צורך למספר את השורות קודם כל).

עד כאן 1:2 לטובת ה-Cursor: תיקו כשיש Primary Key פיזי, ונצחון בנוק-אאוט כשאין.

Cursor יכול לא רק לעבור על השורות זו אחר זו, אלא גם לעדכן אותן. האם במקרה זה יהיה הבדל בין Cursor לבין While?

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

כדי לתת למשימה ניחוח מציאותי אשתמש בדוגמה מפוסט קודם בו חישבתי ממוצע נע אקספוננציאלי (EMA), חישוב שהתבצע ביעילות בעזרת Cursor, וכעת אנסה בשלוש הדרכים.

ראשית כל ניצור את הטבלה מחדש:

Use tempdb;

Go

 

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

Go

 

Select  language_id Share,

        Cast(message_id As DateTime) [Date],

        severity Rate

Into    T_Rates

From    sys.messages;

 

Alter Table T_Rates Alter Column Share Int not Null;

Go

 

Alter Table T_Rates Alter Column [Date] DateTime not Null;

Go

 

Alter Table T_Rates Add Constraint PK_T_Rates Primary Key Clustered (Share,[Date]);

Go

 

Alter Table T_Rates Add MyEMA Float;

Go

 

Select * From T_Rates;

clip_image010

נתחיל עם שירשור הערכים:

Declare @SQL Varchar(Max);

Select  @SQL='Declare @Share Int=0,

        @Date Date=''19000101'',

        @EMA Float;

Declare @I Int=3;

Declare @T Table(EMA Float);'

 

Select @SQL=@SQL+Char(13)+Char(13)+

'Select @EMA=EMA

From @T;

Delete From @T;

Insert

Into @T

Select MyEMA

From (Update T_Rates

Set MyEMA=2./(@I+1.)*Rate+(@I-1.)/(@I+1.)*IsNull(@EMA,Rate)

Output Inserted.MyEMA

Where Share='+Cast(Share As Varchar)+' And [Date]='''+Convert(Varchar(Max),[Date],112)+''') T;'

From T_Rates;

 

Print @SQL;

Exec(@SQL);

לא ארוך אבל מעט מסובך: הקוד הנ"ל יצור קוד דינמי מפלצתי הכולל כ-100,000 פקודות Update (בגרסת 2008 או R2), כשכל פעם הוא קולט את תוצאת החישוב בעזרת האופרטור Output ששולח אותה למשתנה טבלה, והיא משמשת לביצוע ה-Update הבא וחוזר חלילה.

פקודת Select @SQL הראשונה יוצרת את תחילת הקוד הדינמי עם הגדרת המשתנים ואיתחולם,

והשניה יוצרת את רבבות פקודות ה-Update – אחת לכל שורה בטבלה.

ניסיתי להריץ, ולאחר יותר מ-10 שעות הריצה נעצרה לאחר שלא נותר מספיק זכרון, ולאחר שהיא הצליחה לשרשר רק מחצית מהערכים:

clip_image012

לביצוע של הפקודות אפילו לא התקרבנו..

כדאי מאוד לזכור זאת כשמשתמשים בטכניקה הזו בטבלאות גדולות (מודה- אני מרבה להשתמש בה).

נפנה איפוא לשני המועמדים הרציניים יותר והקוד של שניהם להלן:

------------------------------------------------------------------------------------

Print Convert(Varchar(Max),GetDate(),114);

 

Declare @Share Int,

        @PrevShare Int,

        @Date DateTime,

        @Rate Int,

        @EMA Float,

        @I Int;

Select  @I=3;

Declare Cr Cursor For Select Share, [Date], Rate From T_Rates Order By Share, Date For Update Of MyEMA;

Open    Cr;

Fetch   Cr Into @Share, @Date, @Rate;

While   @@Fetch_Status = 0 

        Begin

        If @Share<>IsNull(@PrevShare,0)

        Select  @EMA=Null,

                @PrevShare=@Share;

                Select @EMA=2./(@I+1.)*@Rate+(@I-1.)/(@I+1.)*IsNull(@EMA,@Rate);

        Update  T_Rates 

        Set     MyEMA=@EMA 

        Where   Current Of Cr;

        Fetch   Cr Into @Share, @Date, @Rate;

        End

Close   Cr;

Deallocate Cr;

Go

------------------------------------------------------------------------------------

Print Convert(Varchar(Max),GetDate(),114);

------------------------------------------------------------------------------------

Declare @Share Int=0,

        @Date Date='19000101',

        @EMA Float;

Declare @I Int=3;

Declare @T Table(Share Int,

                [Date] Date,

                EMA Float);

While   @@RowCount<>0

        Begin

        Select @EMA=Case When @Share=Share Then EMA Else Null End,

               @Share=Share,

               @Date=[Date]

        From   @T;

        Delete From @T;

        Insert

        Into   @T

        Select Share,

               [Date],

               MyEMA

        From   (Update T

               Set     MyEMA=2./(@I+1.)*Rate+(@I-1.)/(@I+1.)*IsNull(@EMA,Rate)

               Output  Inserted.Share,

                       Inserted.[Date],

                       Inserted.MyEMA

               From    (Select  Top 1 *

                       From     T_Rates

                       Where    Share>@Share Or (Share=@Share And [Date]>@Date)

                       Order By Share,

                                [Date]) T) T;

        End

 

Print Convert(Varchar(Max),GetDate(),114);

------------------------------------------------------------------------------------

בחלק הראשון מופיע הקוד עם ה-Cursor כמו בפוסט הקודם,

ובחלק השני לולאת ה-While כאשר השיטה היא כמו בשירשור הערכים הנ"ל, עם ההתאמות המתבקשות, וכמובן- ללא השירשור האימתני.

הרצתי 10 פעמים, מדדתי זמנים, ה-Cursor רץ במשך כ-8 שניות וה-While במשך כ-9 שניות (בממוצע), ויש יתרון קל ולא משמעותי לטובת ה-Cursor (התוצאות מהירות משמעותית מהדוגמה בתחילת הפוסט מכיוון שהפעם הרצתי על שרת אחר).

כדאי לציין שאם נסיר את ה-Primary Key מהטבלה- ה-Cursor לא יצליח לבצע Update ..Where Current Of וניאלץ לבצע Update רגיל בדיוק כמו ב-While וכך הביצועים של שתי האפשרויות יתדרדרו פלאים באופן דומה.

לסיכום: אין בדרך כלל הבדלים משמעותיים בין Cursor ו-While- בעיקר כשיש Primary Key, אבל כדי להיות בטוחים עדיף להשתמש ב-Cursor.

בצד זה- אופציית השימוש בשירשור ערכים בעייתית כשמדובר באלפי שורות ומעלה.

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

כתיבת תגובה

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