none
אלטרנטיבות ל-Cursor RRS feed

  • שאלה

  • שלום,

    היה לנו תהליך חיצוני ב-PERL שביצע העברת נתונים בין SQL Instance עם ביצוע של מניפולציה של מידע על הרשומות שהועברו.

    החלטנו להעביר את התהליך לתוך SQL Server, אז תירגמתי את הסקריפט לפרוצדורה של TSQL.

    אנחנו עובדים עם SQL Server 2008.

     

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

    המימוש יחסית פשוט:

    DECLARE @chunkSize int 
    DECLARE @b int 
    DECLARE @c int 
    DECLARE @a int 
    
    DECLARE db_cursor CURSOR FOR 
    SELECT top (@chunkSize) a,b,c
    FROM ***** 
    WHERE *******
    
    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @a,@b,@c  
    
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        exec [linkedServer].[dbname]..[procedurename] @a,@b,@c  <br/>
        FETCH NEXT FROM db_cursor INTO @name  
    END  
    
    CLOSE db_cursor  
    DEALLOCATE db_cursor
    
    הבעיה היא שזה מאוד איטי.
    אם אני לוקחת chunk של כ-2000 רשומות התהליך לוקח כ-2.5 דקות, וזה הרבה יותר איטי מאשר אותו תהליך של PEARL.
    ברור לי שזה גם תלוי בפרוצדורה עצמה שמבצעת מניפולציה של נתונים, ועליה לא הרחבתי כאן את ההסבר.
    אני פשוט מניחה שחלק עיקרי מהאיטיות נובע מה-cursor.

    קראתי שעדיף לממש את זה עם טבלאות זמניות. אנסה זאת מחר.
    האם יש עוד דרכים מעניינות שיכולות להאיץ את העניינים ?

    תודה מראש לעונים
    רוני.

    יום רביעי 09 פברואר 2011 08:35

תשובות

  • הי,

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

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

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

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

    יום טוב,

      


    אסף שלם
    יום רביעי 09 פברואר 2011 10:47
  • דרך טובה לעקוף שימוש בסמן ולהגיע למשאבים טובים יותר לפעמים (לפעמים בהבדל גדול) זה שימוש בטבלת מספרים. טבלה שמכילה עמודה אחת של מספרים רצים...

    חפש בגוגל טבלת מספרים (באנגלית + כמובן SQL) ותראה כמה תוצאות

     

    * תוספת:

    דרך אגב בדרך כלל פיתוח חיצוני ובעיקר פיתוח קוד שאפשר לקמפל יביא אותך לתוצאות הרבה יותר טובות (בפיתוח טוב) אם אפשר להכניס את כל הנתונים לזכרון. לכן גם עבודה עם CLR תחת מסד הנתונים יכולים להוביל לשיפור מאוד ניכר במשאבים (למרות שאין קימפול מלא אלא רק קימפול לשפת ביניים). עבודה עם CLR לפעולות עיבוד מאוד מומלצת. להוצאה/עדכון/הוספה של רשומות ... מאוד לא מומלץ

    יום רביעי 09 פברואר 2011 13:16
    מנחה דיון
  • אפשר אולי להחליף את הקריאות המרובות לפרוצדורה עבור כל רשומה ב-CURSOR בקריאה אחת לפרוצדורה עם נתוני הקוד ששלחת (במקום ה-CURSOR), ובפרוצדורה לבצע את הפעולות השונות עבור כל השורות הנדרשות (אלו שנבחרות ב-SELECT של ה-CURSOR) ביחד.

    או בניסוח אחר - את הפילטור - SELECT top (@chunkSize) a,b,c FROM ***** WHERE תכניס לתוך הפעולות בפרוצדורה procedurename, לדוגמא אם יש שם פעולת UPDATE אז תבצע משהו כזה: update <TABLE> SET  **** FROM ***** INNER JOIN ***** ON ***** WHERE....

    מקווה שהצלחתי להסביר טוב מה התכוונתי..... :-)

    itaigitt, http://copypastenet.blogspot.com
    • הוצע כתשובה על-ידי EitanBlumin יום רביעי 23 פברואר 2011 07:57
    • סומן כתשובה על-ידי Guy GlantserMVP, Moderator יום חמישי 21 אוגוסט 2014 06:01
    יום רביעי 09 פברואר 2011 15:16
  • הי לכולם,

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

    במידה והפרוצדורה עובדת כראוי הייתי בודק את ה- linked server ,היינו מפעיל 2000 קריאות ,ב- loop ולא ב- cursor ,לפרוצדורה שלא עושה כלום.

    ורק אז ואם עדין אם עדין הזמן לא מספק הייתי מחליף את ה- cursor  בלולאה.

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

    לדעתי הבעיה ב- Linked server גדולה יותר מה- cursor. 

    בהצלחה 


    אסף שלם
    יום רביעי 09 פברואר 2011 15:46
  • היי.

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

    כמו כן - האם ההכוונה בשאלה דוקא ל-CURSOR אומרת שזהו מקור הבעיה בוודאות?

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


    itaigitt, http://copypastenet.blogspot.com
    יום רביעי 09 פברואר 2011 16:04
  • תודה לכל העונים.

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

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

    http://sqlpractices.wordpress.com/2008/01/11/how-to-perform-sql-server-row-by-row-operations-without-cursors

     

    אעדכן בהמשך

    תודה

    רוני.

     

    יום חמישי 10 פברואר 2011 08:29

כל התגובות

  • הי,

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

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

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

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

    יום טוב,

      


    אסף שלם
    יום רביעי 09 פברואר 2011 10:47
  • דרך טובה לעקוף שימוש בסמן ולהגיע למשאבים טובים יותר לפעמים (לפעמים בהבדל גדול) זה שימוש בטבלת מספרים. טבלה שמכילה עמודה אחת של מספרים רצים...

    חפש בגוגל טבלת מספרים (באנגלית + כמובן SQL) ותראה כמה תוצאות

     

    * תוספת:

    דרך אגב בדרך כלל פיתוח חיצוני ובעיקר פיתוח קוד שאפשר לקמפל יביא אותך לתוצאות הרבה יותר טובות (בפיתוח טוב) אם אפשר להכניס את כל הנתונים לזכרון. לכן גם עבודה עם CLR תחת מסד הנתונים יכולים להוביל לשיפור מאוד ניכר במשאבים (למרות שאין קימפול מלא אלא רק קימפול לשפת ביניים). עבודה עם CLR לפעולות עיבוד מאוד מומלצת. להוצאה/עדכון/הוספה של רשומות ... מאוד לא מומלץ

    יום רביעי 09 פברואר 2011 13:16
    מנחה דיון
  • אפשר אולי להחליף את הקריאות המרובות לפרוצדורה עבור כל רשומה ב-CURSOR בקריאה אחת לפרוצדורה עם נתוני הקוד ששלחת (במקום ה-CURSOR), ובפרוצדורה לבצע את הפעולות השונות עבור כל השורות הנדרשות (אלו שנבחרות ב-SELECT של ה-CURSOR) ביחד.

    או בניסוח אחר - את הפילטור - SELECT top (@chunkSize) a,b,c FROM ***** WHERE תכניס לתוך הפעולות בפרוצדורה procedurename, לדוגמא אם יש שם פעולת UPDATE אז תבצע משהו כזה: update <TABLE> SET  **** FROM ***** INNER JOIN ***** ON ***** WHERE....

    מקווה שהצלחתי להסביר טוב מה התכוונתי..... :-)

    itaigitt, http://copypastenet.blogspot.com
    • הוצע כתשובה על-ידי EitanBlumin יום רביעי 23 פברואר 2011 07:57
    • סומן כתשובה על-ידי Guy GlantserMVP, Moderator יום חמישי 21 אוגוסט 2014 06:01
    יום רביעי 09 פברואר 2011 15:16
  • הי לכולם,

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

    במידה והפרוצדורה עובדת כראוי הייתי בודק את ה- linked server ,היינו מפעיל 2000 קריאות ,ב- loop ולא ב- cursor ,לפרוצדורה שלא עושה כלום.

    ורק אז ואם עדין אם עדין הזמן לא מספק הייתי מחליף את ה- cursor  בלולאה.

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

    לדעתי הבעיה ב- Linked server גדולה יותר מה- cursor. 

    בהצלחה 


    אסף שלם
    יום רביעי 09 פברואר 2011 15:46
  • היי.

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

    כמו כן - האם ההכוונה בשאלה דוקא ל-CURSOR אומרת שזהו מקור הבעיה בוודאות?

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


    itaigitt, http://copypastenet.blogspot.com
    יום רביעי 09 פברואר 2011 16:04
  • תודה לכל העונים.

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

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

    http://sqlpractices.wordpress.com/2008/01/11/how-to-perform-sql-server-row-by-row-operations-without-cursors

     

    אעדכן בהמשך

    תודה

    רוני.

     

    יום חמישי 10 פברואר 2011 08:29
  •  

    היי,

    אשמח אם תוכל/י לעדכן אותנו בסטטוס השאלה שלך.

    במידה וקיבלת תשובה מתאימה לשאלתך, יש לסמן את התשובה המתאימה ע"י לחיצה על "סמן כתשובה" ליד סימון ה V הירוק

    אם לא קיבלת תשובה, מומלץ לספק פרטים נוספים אודות הבעיה, פרטי לוג, צילומי מסך וכו'

    על מנת להעלות תמונה לפורום ניתן להעזר במדריך להעלאת תמונה


    אם תגובתי פתרה את בעייתך - לחץ/י, על "סמן כתשובה" ליד סימן ה V הירוק.


    מיקרוסופט מציעה שירות זה ללא תשלום, למטרת סיוע למשתמשים והעשרת הידע הקשור בטכנולוגיות ובמוצרים של Microsoft. תוכן זה מתפרסם כפי שהוא והוא אינו מעיד על כל אחריות מצד מיקרוסופט.
    יום שלישי 22 פברואר 2011 07:52
  • מה הסיכוי שאת עדיין כאן Roni Vered ?

    אם הנושא אינו רלווטי או קיבלת תשובה אז אנא סיגרי את השרשור על ידי סימון התשובות שקיבלת (על הדרך אפשר גם הלצביע להודעות מועילות)


    [Personal Site] [Blog] [Facebook]signature

    שבת 09 אוגוסט 2014 14:38
    מנחה דיון