Cursors are the Devil

22/04/2013

צח פניגשטיין

clip_image002

יועץ SQL בכיר, בעל 10 שנות ניסיון בתחום התוכנה, ר"צ DBA בפרוייקט ממשלתי מטעם נאיה טכנולוגיות


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

למה אני מספר לכם על זה?
כי בכל פעם שאני שוקל לפתור בעיה בעזרת Cursor, קופצת לי מאמא בראש, עם הקופיץ ביד,
ושואגת "!!Cursors are the Devil"

אתם חושבים שאני מגזים?

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

בדקתי estimated execution plan וגיליתי זוועה:

clip_image002[4]

(הצגת estimated execution plan היא הדרך הפשוטה להציג תוכנית ביצוע של מה שמתרחש בתוך פונקציות. הדרך הפחות פשוטה היא לפתוח פרופיילר, ולבחור את האירוע
showplan XML Statistics Profile תחת Performance. כדאי להרחיב את בחירת האירועים גם ל- Stored Procedures>SP:StmtCompleted. במידה ואתם משתמשים בפרופיילר, חשוב מאוד להוסיף פילטר, בכדי לא לפגוע בביצועי השרת.)

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

clip_image003[4]: מוצאת את המפתחות לסט הנתונים שעליו יופעל ה-cursor. שימו לב שבמקרה זה המפתחות נשמרים בתוך tempdb (מצ"ב פירוט לגבי האובייקט בריבוע 1):

clip_image004[4] 

הפונקציה המדוברת מפעילה cursor עם הגדרות דיפולטיביות. בזמן היצירה של ה-cursor ניתן להגדיר מאפיינים שונים שלו. מאפיינים אלו משפיעים על אופן הפעולה של ה-cursor, ועל האופן שבו ה-cursor מתנהל מול tempdb. ניתן לקרוא על ההגדרות השונות של מאפייני ה-cursor
כאן:
http://msdn.microsoft.com/en-us/library/ms180169.aspx

clip_image005[4] : משתמשת במפתחות שנשמרו בשלב הקודם, בכדי לשלוף את הנתונים הנדרשים בכל אינטראציה של ה-cursor.

במקרה שלנו, שלב ה-fetch מבצע scan על טבלה של 2.5 מליון רשומות, בכל אינטראציה של
ה-
cursor (ריבוע 2).

כלומר: ביקשנו לשרשר את מספרן של 3 חשבונית? סרקנו 7.5 מליון שורות.

הקוד התמים למראה נראה כך:

DECLARE @AllINVOICEs VarChar(1000)

DECLARE @InvoiceNumber varchar(50)

DECLARE @InvoiceType varchar(2)

 

SET @AllINVOICEs = "

 

DECLARE    INVOICEs CURSOR FOR

     SELECT  InvoiceNumber,InvoiceType

     FROM    Tbl_015_Invoice

     WHERE Tbl_015_Invoice.OperationalFileNumber = @FileNum

     ORDER BY InvoiceDate

 

OPEN INVOICEs

 

FETCH NEXT FROM INVOICEs

INTO @InvoiceNumber,@InvoiceType

 

WHILE @@FETCH_STATUS = 0

BEGIN

 

     IF @AllINVOICEs = "

       SET @AllINVOICEs = @AllINVOICEs + @InvoiceType + ':' + @InvoiceNumber

     ELSE

       SET @AllINVOICEs = @AllINVOICEs + ',' + @InvoiceType + ':' + @InvoiceNumber

   

   FETCH NEXT FROM INVOICEs

   INTO @InvoiceNumber,@InvoiceType

END

 

CLOSE INVOICEs

DEALLOCATE INVOICEs

 

בנקודה זו אני רוצה לפתוח סוגריים גדולים.
חדי העין מבינכם שמו לב שתוכנית הביצוע המוצגת כאן לא הגיונית.
מאחר ובמשפט האיתחול של ה-
cursor לא הוגדר טיפוס, ה-cursor יהיה מטיפוס ברירת המחדל, כלומר keyset cursor. כפי שראינו, cursor מסוג זה שומר את המפתחות של הטבלה עליה הוא אמור לרוץ ב-tempdb (בטבלה בשם CWT_PrimaryKey). בכול אינטראציה, ה-cursor מבצע JOIN מול טבלת המקור, על-פי המפתחות של הטבלה, בכדי לשלוף עמודות נדרשות שאינן חלק מהמפתח.

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

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

אם באגים מוזרים באופטימיזציה של cursorים לא משכנעים אתכם לנטוש אותם – אני לא יודע מה כן ישכנע אתכם…
סגור סוגריים
.

יש דרכים רבות לבצע שרשור של מידע ממספר שורות למחרוזות אחת. שימוש ב-cursor היא הדרך הנפוצה ביותר – והבעייתית ביותר.

אחת הדרכים החביבות עלי לשרשור מחרוזות היא ניצול לרעה של for xml path.
כאשר אנו מוסיפים
for xml  לשאילתה, הכוונה היא שאנו מבקשים לקבל את סט הנתונים שלנו בפורמט XML. אבל XML הוא למעשה מחרוזת, ואם נשחק עם פלט ה-XML, נוכל להגיע למצב שבו נקבל את הנתונים שלנו כמחרוזת משורשרת.

create table #colors (color varchar(20))

 

insert #colors

values ('red'),('green'),('blue')

 

select color

from #colors

הפלט די ברור:

clip_image006[4]

ננסה שאילתה בסיסית עם for xml

select color

from #colors

for xml path('line')

נקבל את הפלט הבא:

<line>

  <color>red</color>

</line>

<line>

  <color>green</color>

</line>

<line>

  <color>blue</color>

</line>

המאפיין line מגיע מתוך ההגדרה ('for xml path('line, בעוד המאפיין color מגיע משם העמודה ששלפנו.

את line קל מאוד להעלים:

select color

from #colors

for xml path(")

נקבל:

<color>red</color>

<color>green</color>

<color>blue</color>

 

את המאפיין color קשה יותר להעלים. אם ננסה לשנות את השם של העמודה למחרוזת ריקה:

select color as "

from #colors

for xml path(")

נקבל שגיאה:

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

 

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

נהרוג שתי ציפורים במכה:

select ',' + color

from #colors

for xml path(")


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

,red,green,blue

נשאר רק לחתוך את הפסיק המיותר בתחילת המחרוזת:

select SUBSTRING(

     (

     select ',' + color

     from #colors

     for xml path(")

     )

     ,2,1000)

והגענו לארץ המובטחת: red,green,blue

במקרה המדובר נחליף את קוד הפונקציה:

SELECT     SUBSTRING(

           (

                SELECT  ',' + CONVERT(VARCHAR(20),InvoiceType) + ':' + CONVERT(VARCHAR(20),InvoiceNumber)

                FROM    Tbl_015_Invoice

                WHERE Tbl_015_Invoice.OperationalFileNumber = @FileNum

                ORDER BY InvoiceDate

                FOR XML PATH(")

           ), 2,1001)

ונקבל תוכנית קטנה ואלגנטית, שמבצעת 15 reads במקום 180k:

clip_image008[4]

נפגש בפרק הבא של מכסחי השדים… (;

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

כתיבת תגובה

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

2 תגובות

  1. עדי13/05/2013 ב 9:23

    בעבר הייתי מאד נגד Cursors, והמשפט cursor זה דבר רע, היה נאמר באופן קבוע בכל פעם, שנתקלתי באחד כזה. מאז קצת שיניתי את דעתי. cursor הוא לא דבר רע, הוא פשוט לא מובן להרבה אנשים:-). אני מסכים שב99 אחוז מהמקרים, שבהם אני רואה cursor, יש דרך טובה יותר לטפל בנתונים, אבל בגלל האחוז הקטן של המקרים, שבהם צריך/עדיף להשתמש בcursor, אני יותר נוטה להאשים את מי שכותב את הקוד מאשר את הcursor. לדעתי באחוז מאד קטן של המקרים, יש באמת ייתרון של שימוש בcursor. לפעמים זה יכול להיות מצב, שבו באמת לא ניתן או מאד קשה למצוא פתרון אחר. לפעמים זה יכול להיות מקרה, שתחזוקת הקוד החלופי תהיה מאד קשה. לפעמים יכולה להיות גם סיבה אחרת (מהירות פיתוח ללא פגיעה בביצועים וכד') דוגמא לשימוש נכון בcursor – סקריפט שמבצע בהתחלה בדיקה על הפרגמנטציה של כל הטבלאות, ומכניס לטבלה את הטבלאות והאינדקסים , שדורשות טיפול. בשלב השני, באמצעות cursor עוברים כל אינדקס ומבצעים את הפעולה הנדרשת (rebuild or reorganize).

    הגב
  2. צח14/05/2013 ב 3:55

    היי עדי,

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

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

    TSQL נועדה בכדי לבצע מניפולציות על סטים של נתונים. cursor עובד בלולה. הרבה מאוד פעמים (לא תמיד!) לטחון גוש גדול של נתונים במכה אחת, יהיה הרבה יותר יעיל מעיבוד של גושים קטנים בלולאה.

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

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

    אבל, מבחינתי, בכל מה שלא קשור לאדמיניסטרציה, cursor הוא מוצא אחרון.

    הגב