מה הבעייה עם nvarchar(max) או varchar(max)

כולנו (או לפחות, כל מי שיצר טבלה ב- DB) מכיר את הרגע שבו הוא מגיע לקבוע data type של עמודה טקסטואלית, בוחר varchar/nvarchar וצריך לקבוע את הגודל המקסימלי. האם לשים 10? 20? 100? הרי אף אחד לא יודע מה יילד יום. וכשלא יודעים מה יקרה – יש כאלה שפשוט בוחרים ללכת על המקסימום. וככה נולדות להן הרבה עמודות nvarchar(max) / varchar(max), כי הרי למה להגביל היום ולחטוף את השגיאה של string or binary data would be truncated מתישהו בעתיד?
דרך נוספת שבה עמודות כאלה נולדות – היא משימוש ב- Entity Framework Code First ושאר ה-ORM-ים. אם מגדירים string property במודל, ומייצרים ממנו באמצעות EF Code First טבלה, אז Entity Framework שואף לשמר את ההתנהגות של System.String, שאינו מוגבל באורכו, ולכן מגדיר את העמודה בתור nvarchar(max) (ניתן כמובן להשתמש ב- StringLengthAttribute כדי להגדיר אורך שונה, אבל זאת פעולה נוספת, שהרבה מפתחים לא עושים).
לכאורה, באמת לא ברור מה המשמעות של ההגבלה. הרי כשאני כותב קוד ב- C#, אף אחד לא מבקש ממני להגיד מה גודל ה-string המקסימלי שאני מחזיק בזמן ההצהרה. למה שאני אדרש לכזה דבר בהגדרת הטבלה? ואם אני כבר נדרש, מה רע בלשים את ה- max?
אז ללבחור max יש כמה חסרונות, והמטרה של הפוסט הזה היא להתמקד בעיקריים שבהם.

אתם מקשים על MSSQL לייצר Execution Plan טוב

שאילתות ב- SQL מגדירות איך אנחנו רוצים שהתוצאה תיראה, ולא כיצד צריך לבצע את הפעולות כדי להגיע לתוצאה. מי שמחליט בסופו של דבר מבין שלל הדרכים להריץ את השליפה, באיזה דרך לבחור, הוא המנוע DB עצמו.
כדי להגיע להחלטה הזאת, הוא נעזר בשלל כלים – אחד הבסיסיים בהם הוא הסכימה של הטבלה, והמידע שקיים כחלק מהסכימה. למשל, אם מוגדרת על עמודה מסויימת unique constraint, זה מידע ש- SQL Server יכול להשתמש בו כדי לייצר execution plan. מידע נוסף שהוא משתמש בו, הוא הערכה של גודל הנתונים שיהיו מעורבים בביצוע. יש הבדל בין הדרכים האפקטיביות לבצע, למשל, JOIN של טבלה קטנה עם טבלה קטנה, טבלה קטנה עם גדולה או שתי טבלאות גדולות.
כדי להעריך את כמות הנתונים, בנוסף לסטטיסטיקות שונות, ה- query optimizer (הרכיב שבוחר את ה- execution plan) מסתמך גם על הגדרת הטבלה עצמה, כדי להעריך מה גודל שורה שמוחזר מכל אופרטור ב- execution plan. במקרה של ערכים שהגודל שלהם הוא קבוע (כמו int, bigint, nchar וכו’) – הוא יודע להגיד את הגודל המדוייק. במקרה של עמודות עם אורך משתנה, הוא נדרש לנחש, כאשר הבסיס לניחוש הוא הגודל המקסימלי המוצהר של העמודה.

לשם הדוגמא, לקחתי את טבלת Posts של StackOverflow, עם הנתונים מ- 2016 בלבד, (ניתן להוריד מכאן)  – והשוויתי בין שתי סכימות של הטבלה: בראשונה העמודה Title מוגדרת בתור nvarchar(250) ובשנייה בתור nvarchar(max).

הרצתי שתי שליפות:

SET STATISTICS IO ON

SET STATISTICS TIME ON

 

SELECT COUNT(DISTINCT Title)

FROM PostsMax a

 

GO

 

SELECT COUNT(DISTINCT Title)

FROM Posts a

 

השליפה הראשונה לקחה קצת יותר מ-29 שניות (90 שניות של CPU Time, ה-execution plan כלל parallelism), בעוד שהשליפה השנייה לקחה כ-5 שניות (18 שניות של CPU Time). למה הפער הכ”כ מהותי הזה?  כי ה- execution plan היה שונה. הסיבה לשוני היא  ה- estimation לגבי גודל השורה. הפער אפילו ברור יותר כאשר עושים SELECT פשוט מהטבלאות:

12

כלומר – אם אתם מגדירים סתם עמודות nvarchar(max) ו- varchar(max), או עמודות עם גדלים שמנותקים מהגודל האמיתי של הנתונים, אתם מקשרים על ה- query optimizer להביא execution plan טוב כי אתם מכריחים אותו להסתמך על נתונים שגויים. אם יודעים מה הגודל הצפוי – רצוי פשוט להגדיר אותו.

Compression

בפוסט הקודם נגעתי קצת ב- compression מזווית של דחיסת נתונים גדולים. בפרט, ציינתי ששימוש ב- row compression או page compression לא עובד באותה הצורה עבור עמודות שמוגדרות max (למשל, עמודות nvarchar(max) לא עוברות unicode compression). הנ”ל יכול לגרום לכם לאכסן סתם יותר מידע בדיסק, ולבזבז סתם יותר זיכרון ב- buffer pool – ולחינם (אם הגודל של המידע לא באמת מצדיק את זה).

והערה כללית לגבי שמירת ערכים גדולים ב- DB

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

  • הן נוטות יותר לפרגמנטציה (למשל בגללעדכונים שבהם משתנה גודל הטקסט, או בגלל ששורות חדשות ממוקמות ב- page נפרד כדי שיהיה מקום לכל השורה בתוך ה- page)
  • ה- data access pattern בשורות כאלה הוא פחות יעיל – כאשר הגודל של השורה עובר את ה- 8KB (הגודל של page בודד), MSSQL יפצל את השורה, ויעביר חלק מהמידע ל- overflow pages, כאשר בשורה עצמה יישמר pointer ל- overflow page. הקפיצות הללו יוצרות IO pattern לא יעיל, שרצוי להימנע ממנו
  • full table scans יהיו יותר איטיים – בגלל שהטבלה יותר גדולה, שליפות שדורשות full table scan (למשל, לצרכי reporting, או שליפות ad-hoc), יצטרכו לעבור על יותר data, גם אם ה- data המעניין הוא יחסית קטן. כמובן שיש לזה פתרונות שונים (בין אם לפצל את העמודות הגדולות לטבלאות נפרדות, אינדקסים על הערכים של העמודות הקטנות יותר, שימוש ב- columnstore indexes ועוד)
  • הערכים הגדולים, שנטענים לזיכרון ל- buffer pool, מגיעים על חשבון data שאולי חשוב לנו יותר שיישמר ב- cache, וגורמים לזה שמידע יפונה יותר מהר לדיסק – ולכן יידרש יותר IO.

במקרה כזה, כדאי לשקול פתרונות אחרים – שמירה של המידע בדיסק עם הפנייה מה- DB, שימוש ב- FILESTREAM tables, שמירה של המידע דחוס ב- DB ועוד.

השאר תגובה