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

26/12/2010

חסכו ואמצו: Sparse Columns

Filed under: Uncategorized — תגיות: , , — גרי רשף @ 18:55

Sparse Columns הוא בשורה שגרסת SQL 2008 הביאה עמה לפני כשנתיים: עמודות שרובן Nulls (כלומר- מאוכלסות בפועל במעט מאוד ערכים) יכולות להיות מוגדרות כ-Sparse Columns ואז הן תחסוכנה מקום שכן לא יוקצה שטח אחסון ל-Nulls אלא לערכים אמיתיים בלבד.

נבדוק במה דברים אמורים- ניצור שתי טבלאות עם עמודת Int – בראשונה נגדיר אותה כ-Sparse ובשניה לא, לשתיהן נוסיף 100,000 שורות, ואולי אף נגזים ולעמודת ה-Sparse נכניס Nulls ולעמודה שאינה Sparse נכניס מספרים, ונבדוק כמה זה עולה לנו:

Use tempdb;

Go


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

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

Go


Create Table T1(I Int Sparse Null);

Create Table T2(I Int Null);

Go


With T As

(Select 1 I

Union All

Select I+1

From T

Where I<100000)

Insert Into T1

Select Null

From T

Option (MaxRecursion 0);


With T As

(Select 1 I

Union All

Select I+1

From T

Where I<100000)

Insert Into T2

Select I

From T

Option (MaxRecursion 0);

Go


Exec sp_SpaceUsed T1;

Exec sp_SpaceUsed T2;

Go

clip_image002

שטח האחסון של טבלה T1 בה עמודת ה-Sparse הריקה קטן מזה של טבלה T2 בה העמודה מלאה בכ-17%.

זה הכל? כן- זה הכל, ומכאן המצב רק יחמיר מכיוון שאם נאכלס את עמודת ה-Sparse בערכים באופן חלקי, הפער הקטן מלכתחילה ילך ויצטמצם, ובסביבות 20% הן יתפסו שטח אחסון דומה (כלומר- אם בטבלה T1 יהיו 20% מהשורות מלאות ו-80% ריקות – שטח האחסון שלה יהיה כשל T2 100% מלאה).

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

נבצע בדיקה אחרת- הפעם שתי טבלאות עם עמודת Varchar(Max):

Use tempdb;

Go


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

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

Go


Create Table T1(S Varchar(Max) Sparse Null);

Create Table T2(S Varchar(Max) Null);

Go


With T As

(Select 1 I

Union All

Select I+1

From T

Where I<100000)

Insert Into T1

Select Null

From T

Option (MaxRecursion 0);


With T As

(Select 1 I

Union All

Select I+1

From T

Where I<100000)

Insert Into T2

Select Null

From T

Option (MaxRecursion 0);

Go


Exec sp_SpaceUsed T1;

Exec sp_SpaceUsed T2;

Go

clip_image004

לשתי הטבלאות אותו שטח אחסון!

זה באמת מוזר מכיוון שתא Varchar תופס שטח מינימלי כשהוא ריק, וציפיתי שבטבלת ה-Sparse השטח הזה יחסך, והנה לא; ומכאן החישובים הם לטובת טבלה T2 "הרגילה" אם נתחיל להזין לתוכן נתונים.

לא בדקתי מה קורה בעמודת Char מכיוון שזה לא לעניין: אם ידוע מלכתחילה שיהיו הרבה Nulls – אין טעם להשתמש בעמודת Char שמקצה את מלוא המקום לכל תא ללא קשר לניצול שלו.

בעמודות מסוג Datetime המצב דומה לזה של Int: המערכת מקצה מקום לכל תא גם אם הוא Null ולכן יש מקום לצפות שטבלת ה-Sparse תהיה קטנה משמעותית, אך בפועל ההפרש אינו גדול, וכשיש ערכים- הוא נעלם מהר מאוד ורווחנו יוצא בהפסדנו.

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

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

תגובה אחת »

  1. […] פוסט ביקורתי בנושא לפני מספר ימים, וקיבלתי תגובות מחכימות ממיכאל […]

    פינגבאק של חסכו ואמצו: Sparse Columns – מבט נוסף « הבלוג של גרי רשף — 08/01/2011 @ 05:56


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

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

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