none
בעיית ביצועים עקב IF EXISTS בטריגר RRS feed

  • שאלה

  • היי.

    משהו מוזר. יש לי משפט UPDATE על טבלה, משהו בסגנון של:

    update MyTable set SomeIntColumn = 1 where AnotherIntColumn = 11

    במקרה שלי זה רץ על כ-12,500 שורות בטבלה (מתוך 18200 שורות סה"כ) וזה לוקח 2 וחצי דקות בערך (= הרבה זמן...).

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

    	IF EXISTS	(	select 1
    					from Inserted i
    					join deleted d on d.id=i.id and (i.MoreIntColumn<>d.MoreIntColumn)
    				)

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

    אבל, אם אני מוריד את הקטע הזה מהטריגר, הפעולה לוקחת 0 זמן....

    אגב, דימיתי את ה-SELECT שבתוך התנאי שבטריגר וזו לא פעולה שלוקחת הרבה זמן.

    אז מה לעזאזל קורה פה? למה הדבר הזה מעכב כ"כ?


    itaigitt, http://copypastenet.blogspot.com

    יום רביעי 05 ספטמבר 2012 15:03

תשובות

  • בוקר טוב ותודה לכולם.

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

    איבן - inserted היא טבלה שמכילה את כל הרשומות שעודכנו במשפט ה-UPDATE, כלומר 12500 רשומות במקרה הזה. לכן גם הדימוי שלי היה בסדר.

    את הפתרון מצאתי בעזרתו של שי אנגלברג, שעובד לשם שמים ולא לשם נקודות :-), וזה הפתרון:

    הבעיה הייתה אכן ב-SELECT שבבדיקה.

    ההבדל בין ה-SELECT הזה על Inserted ו-deleted לבין הדמיה שלו על הטבלה המקורית הוא שהטבלה המקורית מאונדקסת לעומת Inserted ו-deleted שאינם מאונדקסים. Inserted ו-deleted הן לא ממש טבלאות והאופטימייזר לא תמיד יעשה עליהן עבודה טובה כמו על טבלאות. למעשה הוא מבצע סריקה על כל deleted לכל שורה ב-inserted. מספיק רע, לא?

    פתרונות:

    שימוש בפונקציה update() על השדה מוריד את זמן הריצה לאפס!

    למי שלא מכיר - http://msdn.microsoft.com/en-us/library/ms187326.aspx

    במקרה שלי זה מצוין.

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

    לדוגמא:

    update MyTable set SomeIntColumn = 1 where AnotherIntColumn = 11
    IF UPDATE(SomeIntColumn) --> = TRUE

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

    האמת שאני הולך לצאת ממש צדיק, ולבדוק באמצעות update, ואם הבדיקה תהי חיובית (לא במקרה שציינתי פה) - אני אשפוך את ה-DATA מ-Inserted ו-deleted לתוך טבלאות זמניות מאונדקסות ועליהן אני אעבוד.

    זה העניין. תודה לכולם.


    itaigitt, http://copypastenet.blogspot.com

    • סומן כתשובה על-ידי itaigitt יום חמישי 06 ספטמבר 2012 08:45
    יום חמישי 06 ספטמבר 2012 08:43

כל התגובות

  • הבהרה:

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

    כלומר - רק בדיקת התנאי אשמה באיטיות.....


    itaigitt, http://copypastenet.blogspot.com

    יום רביעי 05 ספטמבר 2012 15:09
  • כשאתה מריץ את השליפה שבתוך הסוגררים של התנאי מחוץ לטריגר- מה קורה? כמה זמן זה נמשך?
    האם ID מוגדר כ-Primary Key & Clustered Index?

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


    El castellano no es mi lengua materna. Discúlpenme por los errores gramaticales, y, si pueden, corríjanme en los comentarios, o por correo electrónico. ¡Muchas gracias! Blog: http://about.me/GeriReshef

    יום חמישי 06 ספטמבר 2012 02:47
  • היי!

    לפי מה שהבנתי, כאשר אתה מעדכן את ה-12500 שורות שלך, הבדיקה IF EXISTS מתבצעת עבור כל שורה וזה מה שפוגע בביצועים. לעומת זאת, בטבלת inserted  אמורה להיות רק רשומה אחת כל פעם, לכן כל בדיקה כזאת אמורה לקחת זמן קצר מאוד. נסה אולי להוסיף פקודה לטריגר, שתאכסן את התוכן של inserted לטבלת history. אולי התשובה היא פשוטה: 12500 בדיקות כאלה לוקחות הרבה זמן. איך דימית את הבדיקת הזאת מחוץ לטריגר בדיוק? אולי במצב הזה הבדיקה בוצעה רק פעם אחת עבור 12500 שורות...

    יום חמישי 06 ספטמבר 2012 05:28
  • בוקר טוב ותודה לכולם.

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

    איבן - inserted היא טבלה שמכילה את כל הרשומות שעודכנו במשפט ה-UPDATE, כלומר 12500 רשומות במקרה הזה. לכן גם הדימוי שלי היה בסדר.

    את הפתרון מצאתי בעזרתו של שי אנגלברג, שעובד לשם שמים ולא לשם נקודות :-), וזה הפתרון:

    הבעיה הייתה אכן ב-SELECT שבבדיקה.

    ההבדל בין ה-SELECT הזה על Inserted ו-deleted לבין הדמיה שלו על הטבלה המקורית הוא שהטבלה המקורית מאונדקסת לעומת Inserted ו-deleted שאינם מאונדקסים. Inserted ו-deleted הן לא ממש טבלאות והאופטימייזר לא תמיד יעשה עליהן עבודה טובה כמו על טבלאות. למעשה הוא מבצע סריקה על כל deleted לכל שורה ב-inserted. מספיק רע, לא?

    פתרונות:

    שימוש בפונקציה update() על השדה מוריד את זמן הריצה לאפס!

    למי שלא מכיר - http://msdn.microsoft.com/en-us/library/ms187326.aspx

    במקרה שלי זה מצוין.

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

    לדוגמא:

    update MyTable set SomeIntColumn = 1 where AnotherIntColumn = 11
    IF UPDATE(SomeIntColumn) --> = TRUE

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

    האמת שאני הולך לצאת ממש צדיק, ולבדוק באמצעות update, ואם הבדיקה תהי חיובית (לא במקרה שציינתי פה) - אני אשפוך את ה-DATA מ-Inserted ו-deleted לתוך טבלאות זמניות מאונדקסות ועליהן אני אעבוד.

    זה העניין. תודה לכולם.


    itaigitt, http://copypastenet.blogspot.com

    • סומן כתשובה על-ידי itaigitt יום חמישי 06 ספטמבר 2012 08:45
    יום חמישי 06 ספטמבר 2012 08:43
  • שי אנגלברג הוא המלך ! :)


    חיים פישנר.

    יום שלישי 11 ספטמבר 2012 19:14