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

11/09/2010

Cursor ללא Cursor

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

השימוש ב-Cursor הוא דרך המלך למשימות שלא ניתן לבצע בכלים הסטנדרטיים של SQL, למשל – כפי שיודגם להלן – גיבוי כל הדטבייסים בשרת שאינם System Databases.
הפתרון הסטנדרטי בעזרת Cursor הוא כזה:

Declare @S Varchar(Max),

        @SQL Varchar(Max);

Declare Cr Cursor For Select name From sys.databases Where name Not In ('master','tempdb','model','msdb');

Open Cr;

Fetch Cr Into @S;

While @@Fetch_Status = 0

    Begin

    Set    @SQL='Backup Database '+@S+' To Disk = ''C:\Tmp\'+@S+'.bak'';';

    Print @SQL;

    Exec(@SQL);

    Fetch Cr Into @S;

    End

Close Cr;

Deallocate Cr;

Go

ה-Cursor עובר על כל הדטבייסים הרלוונטיים בעזרת טבלת המערכת sys.databases,

יוצר באופן דינמי פקודת SQL לגיבוי,

ומבצע אותה.

מה קורה כשרוצים להימנע משימוש ב-Cursor?

טוב, מדוע בכלל נרצה להימנע משימוש ב-Cursor? אני לא הולך לספר סיפור על מישהו שנקלע לאי בודד במסגרת אחת מתוכניות הריאליטי וכעת עליו לבצע גיבוי לשרת ששרד עימו ללא ה-cursor שטבע במצולות(..), והסיבה שאני עושה זאת מפעם לפעם היא שאינני זוכר בעל פה את הסינטקס ולכן משתמש בדרך אחרת מחמת עצלנותי, ובין היתר- חוסך כמה שורות קוד, ואולי אף משפר קלות את הביצועים – כפי שנראה בהמשך.

אגב- אם הטיעון הוא שקראנו באיזה מקום שהשימוש ב-Cursor אינו יעיל – הפתרונות החלופיים שאציג אינם החלופה המבוקשת..

פתרון אפשרי יכול להיות בדרך של שירשור ערכים מטבלה לשם יצירת פקודות SQL דינמיות:

Declare    @SQL Varchar(Max);

Set        @SQL='';

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

From sys.databases Where name Not In ('master','tempdb','model','msdb');

Print    @SQL;

Exec(@SQL);

Go

התוצאה אותה תוצאה, וגם רווח צנוע- הקוד יותר קצר..

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

Exec master..sp_MSForEachDB 'If ''?'' Not In (''master'',''tempdb'',''model'',''msdb'') Backup Database ? To Disk = ''C:\Tmp\?.bak'';'

Go

יש להודות: יותר קצר מזה אי אפשר..

ולבסוף- לולאה כמו ב-Cursor:

Declare    @S Varchar(Max),

        @SQL Varchar(Max);

Select    Top 1 @S=name From sys.databases Where name Not In ('master','tempdb','model','msdb') Order By name;

While    @@RowCount>0

    Begin

    Set    @SQL='Backup Database '+@S+' To Disk = ''C:\Tmp\'+@S+'.bak'';';

    Print @SQL;

    Exec(@SQL);

    Select    Top 1 @S=name From sys.databases Where name Not In ('master','tempdb','model','msdb') And name>@S Order By name;

End;

Go

בכל ארבע הדוגמאות הנ"ל – התוצאה המתקבלת זהה.

כדי לא לפוצץ את הדיסק עם גיבויים מיותרים- נמחק אותם כך (או בכל דרך אחרת):

Exec xp_cmdshell 'Del C:\Tmp\*.bak';

Go

מה קורה מבחינת הביצועים? איזה פתרון יותר יעיל?

השאלה עצמה ברוב המקרים מיותרת- עיקר העבודה היא הגיבויים עצמם – בדוגמה זו – ורווח של אלפית שניה בקריאת רשימת הדטבייסים הוא זניח. בדרך כלל לא נשתמש ב-Cursor – כך אני מקווה – כדי לעבור על טבלאות בנות רבבות שורות ויותר; אבל בכל זאת – נבדוק!

ניצור טבלת מספרים בת 100,000 שורות ונעבור עליה בשלוש מהדרכים הנ"ל (ms_ForEachDB אינו רלוונטי כאן):

If Object_Id('T_Misparim') Is Not Null Drop Table T_Misparim;

Go


Declare    @N Int=100000;

With T AS

(Select    1 Mispar

Union All

Select    Mispar+1

From    T

Where    Mispar<@N)

Select    *

Into    T_Misparim

From    T

Option (MaxRecursion 0);

Go

ושלושת הפתרונות להדפסת תוכן הטבלה:

--Cursor-שימוש ב

Declare @I Int;

Declare Cr Cursor For Select * From T_Misparim;

Open Cr;

Fetch Cr Into @I;

While @@Fetch_Status = 0

    Begin

    Print @I;

    Fetch Cr Into @I;

    End

Close Cr;

Deallocate Cr;

Go


--שימוש בלולאה

Declare @I Int;

Select    Top 1 @I=Mispar From T_Misparim Order By Mispar;

While    @@RowCount<>0

    Begin

    Print    @I;

    Select    Top 1 @I=Mispar From T_Misparim Where Mispar>@I Order By Mispar;

    End

Go


--שירשור ערכים מטבלה

Declare    @S Varchar(Max);

Set        @S='';

Select    @S=@S+Char(13)+Cast(Mispar As Varchar) From T_Misparim;

Print    @S;

Go

הבהרה: ברור ש-Select פשוט טוב ויעיל ונוח מכל ההתחכמויות הנ"ל, וזה נועד רק לצורכי השוואה.

פתרון בעזרת Cursor נמשך אצלי כ-4 שניות,

פתרון בדרך של לולאה – עצרתי לאחר כשלוש דקות לאחר שהושלמו כ-4% מהמשימה (היה נמשך קרוב לשעה וחצי),

פתרון בדרך של שירשור ערכים מטבלה 1:50 דקות (ובשל הגדרות ומגבלות של ה-SSMS לא באמת מציג את כל המספרים).

במקרה זה ה-Cursor לוקח בהליכה עם שתי הרגליים קשורות מאחורי הגב..

מה קורה עם מוסיפים אינדקס לטבלה?

Create Unique Clustered Index Idx_T_Misparim On T_Misparim(Mispar);

Go

כעת הפתרון בעזרת Cursor לוקח כ-5 שניות (כנראה לא חל שום שינוי בביצועים ובכל מקרה צריך לסרוק את כל הטבלה),

פתרון בעזרת לולאה נמשך 2 שניות בלבד (ביצוע Index Seek לערך הבא יעיל מאוד),

ושירשור ערכים מטבלה – כמו קודם: 1:47 דקות.

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

במדידה חוזרת התקבלו תוצאות מעט שונות- השימוש ב-Cursor לקח 7 שניות עם או בלי אינדקס,

והשימוש בלולאה לקח 4 שניות עם אינדקס.

כששיניתי את סוג ה-Cursor ל-Fast_Forward כמומלץ (Declare Cr Cursor Fast_Forward For Select * From T_Misparim) הזמן התקצר ל-4 שניות..

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

מודעות פרסומת

תגובה אחת »

  1. ,תודה עזרת לי מאוד

    תגובה של asaf — 06/10/2011 @ 16:03


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

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

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