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

12/08/2010

מחיקת שורות ישנות מטבלאות גדולות

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

ניסינו בהתחלה בתמימותנו – לבצע 'Delete From MyTbl Where Taarih<='20091231 כלומר- למחוק את כל מה שמשנת 2009 ודרומה, אבל זה הסתיים בתקיעת השרת למשך שעות ארוכות וניפוח הלוג למימדים מפלצתיים; ולבסוף עצרנו את המחיקה.

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

Create Procedure MyProc As

Declare    @D DateTime,

        @RC Integer;


Select    @D=(Select MIN(Taarih) From MyTbl),

        @RC=50000;


Set RowCount 50000;

While @D<='20091231'

    Begin

    While @RC=50000

        Begin


        Delete

        From    MyTbl

        Where    Taarih=@D;


        Set        @RC=@@RowCount;


        End

    Select    @D=(Select MIN(Taarih) From MyTbl),

            @RC=50000;

    End;

Set RowCount 0;

Go

כדאי לשים לב לפקודה Set RowCount=50000 שמגבילה את מספר השורות הנמחקות בפקודת ה-Delete ל-50000 (ולביטולה בסוף),

ולמשתנה המערכת RowCount@@ שמציין כמה שורות טופלו בפקודה הקודמת לו.

בנוסף- הלולאה החיצונית היא על התאריכים, והפנימית – על 50000 שורות בכל פעם.

זה כבר עבד, אבל לאט מאוד: כ-45 דקות לכל יום, כשיש כמה מאות ימים למחוק..

בדקנו את ה-Execution Plan והתברר שהמערכת מבצעת בכל פעם Clustred Index Scan: לטבלה מוגדר Clustered Index, אולם עמודת התאריך היא רק השניה בסדר ולא הראשונה, ולכן אין ברירה אלא לעבור על כל הטבלה כדי למצוא את השורות של תאריך מסויים.. יקר ומיותר!

מי שיסתכל על הכותרת יראה שמופיעה שם המלצה להוספת אינדקס על שדה התאריך וכך נהגנו, אך בשינוי קטן: ההמלצה הייתה על Covered Index, כלומר- כזה הכולל (Include) את כל שאר העמודות. ההמלצה הזו יכולה להועיל בשליפות בהן יש להציג עמודות אחרות, אך נראתה מיותרת לפעולת Delete ולכן התעלמנן מה-Include ויצרנו אינדקס פשוט על עמודת התאריך.

כעת ה-Execution Plan נראה פשוט וזריז – Index Seek, כלומר- מציאת השורות המתאימות בעזרת האינדקס, ופניה אליהן למחיקה בטבלה עצמה (ה-Clustered Index);

ומחיקת כל יום נמשכה 4.5 דקות בלבד (כ-10% מהזמן המקורי).

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

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

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

בלוג בוורדפרס.קום.

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