none
משמעות שלילית של שדה NVARCHAR גדול משמעותית מהדרוש RRS feed

  • שאלה

  • כאשר מדובר משדות מסוג VARCHAR, ברור לי שבמידה ואגדיר שדה בגודל 100תווים אך אשמור בו תו אחד, עדיין יבובזבז מקום של 100תווים.
    כמו כן, ידוע לי שיש מגבלה של אורך שורה (סך כל העמודות) וככל שהיא גדולה יותר יש השפעה על הביצועים.

    האם הדבר זהה במקרה של (NVARCHAR - NVARCHAR(MAX ?

    תודה

    יום שלישי 01 ספטמבר 2015 07:57

תשובות

  • בזבוז מקום לא יהיה

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

    אם ידוע לך מקסימום אורך - תן אותו. עד 8000 תווים (4000 ב NVARCGHAR) זה ייכנס בדף אחד ולכן עדיף, מעבר לזה תשתמש ב MAX

    מקווה שהועלתי לך

    אם כן אתה מוזמן לסמן תשובתי ככזו

    אורי


    אורי כ.

    • סומן כתשובה על-ידי OMUser יום חמישי 03 ספטמבר 2015 19:35
    יום שלישי 01 ספטמבר 2015 08:45
  • מעט הבהרה על סוגים שונים והוספה לדברים היפים שכתב אורי +1

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

    http://ariely.info/Blog/tabid/83/EntryId/162/Examine-how-DateTime2-type-stored-in-the-data-file.aspx

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

    עתה נחזור לעניין שלנו בקצרה:

    טורים טקסטואליים שכוללים את הקידומת VAR הם טורים מסוד non-fixed-length שזה אומר שאורך הנתון במסד הנתונים אינו קבוע. זה נכון גם ל VARCHAR וגם ל NVARCHAR. האות N לפני ה VAR מסמנת שזה טור מסוג יוניקוד.

    כך למשל הטורים הבאים הם בעלי אורך קבוע fixed-length והם המקבילה לטורים מעל CHAR ו NCHAR.

    * אני מאוד מאוד מאוד ממליץ בברירת המחדל לעשות שימוש בתמיד בטורים של יוניקוד אלא אם אנחנו בטוחים שהתוכן לא יכיל שום תוו שאינו באנגלית/מספרים/תווי כמו -. יש לי הרצאה בנושא אלמנטים nondeterministic שנותנת יותר מידע על נקודות שונות. אני מאוד ממליץ לא לסמוך על ה CULTURE ולהיות גמישים לכל שינוי קונפיגורציה (לכן אני לא מקבל כשאומרים לי "אנחנו רושמים רק בעברית ולכן אסקי של עברית מתאים לנו"! עברית = יוניקוד מבחנתי).

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

    ** נקודה אחרונה: מי שלא שולט בנושא אז אני מאוד ממליץ לקרוא על מבנה הנתונים המסד הנתונים INTERNALS כמו מה זה עמודים במסד הנתונים וכיצד העמוד מסודר. במשפט אחר: כל הנתונים שלנו שמורים בתןך יחידות פיסיות של 8 קילובייט שנקראות עמוד. כל עמוד מכיל 96 בייט של כותרים. בסוף העמוד נשמר מערך הכולל מיפוי של הרשומות בעמוד (שימו לב שאני מדבר על רשומות בעמוד אבל שורות בטבלה... כל רשומה שומרת את הנתונים של שורה מסויימת). הרשומות עצמן בנויות במבנה מאוד קבוע ומוכר ועליו אני אדון כרגע. התמונה הבאה לקוחה מתוך המאמר שלי:

     

    נחזור לנושא שלנו (יש הרבה נקודות חשובות שכבר נכתבו ויש מעט נקדוות שהוזכרו לא מדוייקות):

    כיצד השרת יודע מה האורך של הנתון בקובץ? השרת שומר מאחורי הקלעים 2 בייטים מחוץ לתוכן של הטור עצמו שכולל את האורך (בתוך התוכן של הרשומה, יש רשימה של כל האורכים של הטורים בעלי אורך משתנה... 2 בייט עבור כל טור VAR).

    כמה תכונות חשובות להבין שהם הבסיס להחלטות:

    1. כפי שכתב אורי, טורים של יוניקוד צריכים כפול אורך עבור הדטא של כל תוו. הסיבה פשוטה: SQL עובד עם פורמט UTF-16 (ולא כמו שמקובל עם UTF-8 שהוא הרבה יותר טוב). בכל ההודעה הזו כשאני אגיד יוניקוד אני מתכוון ל UTF-16 לכן.

    2. נכון כמו שכתבת אורי (אני רק מחליף אתצ המילה "שורה" במילה ערך "VALUE"), ש"אין מגבלה על אורך הערך", אבל חשוב מאוד להבין שיש הבדל  ע נ ק   בין שמירה של נתון כחלק מהרשומה ב PAGE כפי שניראה בתמונה מעל, לבין מצב בו אנחנו עושים שימוש למשל בערך גדול מאוד MAX והתוכן אינו נשמר ביחד עם תוכן של הרשומה אלא בנפרד (ברשומה במקרה זה יהיה רק פוינטר). אורי כתב על כך ואין טעם להרחיב :-)

    3. לא מדוייק להגיד ש "תו אחד יתפוס BYTE אחד בלבד" עבור VARCHAR. החלק הראשון היה מדוייק: טור VAR שומר לפי האורך האמיתי. אבל אם שומרים X תווים במסד הנתונים בטור VAR באורך X+Y, אז נכון שלא ישמרו בפועל X+Y אבל גם לא X אלא X+2.

    גם אם שומרים תוו אחד בתוך NVARCHAR עדיין השרת שומר את 2 הבייטים עבור האורך, ולכן קיבלתם הכפלה של אורך המידע שלכם במסד הנתונים (4 תווים במקום 2), ואם זה היה טור מסוג VARCHAR אז קיבלת הכפלה פי 3 (בייט אחד עבור התוו + 2 בטים עבור האורך)!

    מסקנה: לכן נכון מאוד מי שאמר שיש פגיעה גדולה בביצועים במקרים מסויימים, ביחס לשימוש בטורים קבועים כמו CHAR או NCHAR!

    מסקנה: אין אף פעם הגיון לטור VAR שהוא קטן מ 3 למשל. עדיף בדרך כלל לעבוד עם CHAR של 3 על פני VARCHAR של 3, גם אם חלק מהמידע שלנו הוא רק תוו אחד ויש לנו רק חלק של 3 תווים.

    4. נשאלה השאלה האם שימוש בטור VAR באורך 300 במקום באורך 30 יביא לפגיעה בביצועים (שיניתי את השאלה מ 3 ל 30 מכיוון שכמו שכתבתי 3 אמורים לשמור בטור באורך קבוע בכל מקרה בדרך כלל).

    התשובה כמו הכל בשרתי SQL אינה חד משמעית כפי שנכתב מעל :-)
    לא רק שהוא לא תמיד יביא לפגיעה אלא לפעמים יביא לשיפור בביצועים!

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

    כאשר מנוע המיטוב של השרת בונה את תוכנית ההרצה עבור כל שאילתה, הוא מקציב מקום בזכרון עבור ההרצה. מה קורה אם השרת נתקל בטור VAR של אורך 300?!? השרת לא "רוצה" לבזבז מקום בזכרון עבור 300 תווים כאשר אולי בטבלה יש רק 3 תווים, אבל מצד שני הוא רוצה לשמור מספיק זכרון כדי למטב את השאילתה. אם לא יהיה מספיק זכרון שמור הביצועים של השאילתה עלולים להיפגע! מייקרוסופט החליטו לקבוע אלגורתמים פשוט בו השרת שומר עבור טור כזה את הממוצע (לפי חצי מאורך הטור). עתה אם כל הנתונים בטור יכללו אורך מלא, אז ייתכן שהשרת לא יבצע מיטוב נכון.

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

    אני מקווה שזה קצת מסדר עוד נקודות ומוסיף לדברים של אורי :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]





    • נערך על-ידי pituachMVP, Moderator יום שלישי 01 ספטמבר 2015 15:31
    • סומן כתשובה על-ידי OMUser יום חמישי 03 ספטמבר 2015 19:35
    יום שלישי 01 ספטמבר 2015 10:24
    מנחה דיון

כל התגובות

  • VARCHAR שומר רק לפי האורך האמיתי ולכן תו אחד יתפוס BYTE אחד בלבד, אולי התכוונת ל CHAR שם אכן נתפסים 100 תווים בדוגמא שלך

    הדבר נכון גם לגבי לגבי NVARCHAR אלא שכל תו תופס מקום של שני בתים במקום BYTE אחד

    אין מגבלה על אורך שורה אבל יש על אורך אינדקס וכמובן יש מגבלה על הביצועים



    אורי כ.

    יום שלישי 01 ספטמבר 2015 08:18
  • תודה על ההתיחסות לשאלתי.

    אני מבין מדבריך ששדה NVARCHAR(300) במקום NVARCHAR(3) יביא לפגיעה טאורטית בביצועים אך לא יביא לבזבוז מקום. 
    האם הבנתי נכון?

    בנוסף, האם ידוע לך האם יש Side Affects לשימוש ב NVARCHAR(MAX) במקום להגדיר את רוחב השדה?

    יום שלישי 01 ספטמבר 2015 08:26
  • בזבוז מקום לא יהיה

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

    אם ידוע לך מקסימום אורך - תן אותו. עד 8000 תווים (4000 ב NVARCGHAR) זה ייכנס בדף אחד ולכן עדיף, מעבר לזה תשתמש ב MAX

    מקווה שהועלתי לך

    אם כן אתה מוזמן לסמן תשובתי ככזו

    אורי


    אורי כ.

    • סומן כתשובה על-ידי OMUser יום חמישי 03 ספטמבר 2015 19:35
    יום שלישי 01 ספטמבר 2015 08:45
  • מעט הבהרה על סוגים שונים והוספה לדברים היפים שכתב אורי +1

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

    http://ariely.info/Blog/tabid/83/EntryId/162/Examine-how-DateTime2-type-stored-in-the-data-file.aspx

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

    עתה נחזור לעניין שלנו בקצרה:

    טורים טקסטואליים שכוללים את הקידומת VAR הם טורים מסוד non-fixed-length שזה אומר שאורך הנתון במסד הנתונים אינו קבוע. זה נכון גם ל VARCHAR וגם ל NVARCHAR. האות N לפני ה VAR מסמנת שזה טור מסוג יוניקוד.

    כך למשל הטורים הבאים הם בעלי אורך קבוע fixed-length והם המקבילה לטורים מעל CHAR ו NCHAR.

    * אני מאוד מאוד מאוד ממליץ בברירת המחדל לעשות שימוש בתמיד בטורים של יוניקוד אלא אם אנחנו בטוחים שהתוכן לא יכיל שום תוו שאינו באנגלית/מספרים/תווי כמו -. יש לי הרצאה בנושא אלמנטים nondeterministic שנותנת יותר מידע על נקודות שונות. אני מאוד ממליץ לא לסמוך על ה CULTURE ולהיות גמישים לכל שינוי קונפיגורציה (לכן אני לא מקבל כשאומרים לי "אנחנו רושמים רק בעברית ולכן אסקי של עברית מתאים לנו"! עברית = יוניקוד מבחנתי).

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

    ** נקודה אחרונה: מי שלא שולט בנושא אז אני מאוד ממליץ לקרוא על מבנה הנתונים המסד הנתונים INTERNALS כמו מה זה עמודים במסד הנתונים וכיצד העמוד מסודר. במשפט אחר: כל הנתונים שלנו שמורים בתןך יחידות פיסיות של 8 קילובייט שנקראות עמוד. כל עמוד מכיל 96 בייט של כותרים. בסוף העמוד נשמר מערך הכולל מיפוי של הרשומות בעמוד (שימו לב שאני מדבר על רשומות בעמוד אבל שורות בטבלה... כל רשומה שומרת את הנתונים של שורה מסויימת). הרשומות עצמן בנויות במבנה מאוד קבוע ומוכר ועליו אני אדון כרגע. התמונה הבאה לקוחה מתוך המאמר שלי:

     

    נחזור לנושא שלנו (יש הרבה נקודות חשובות שכבר נכתבו ויש מעט נקדוות שהוזכרו לא מדוייקות):

    כיצד השרת יודע מה האורך של הנתון בקובץ? השרת שומר מאחורי הקלעים 2 בייטים מחוץ לתוכן של הטור עצמו שכולל את האורך (בתוך התוכן של הרשומה, יש רשימה של כל האורכים של הטורים בעלי אורך משתנה... 2 בייט עבור כל טור VAR).

    כמה תכונות חשובות להבין שהם הבסיס להחלטות:

    1. כפי שכתב אורי, טורים של יוניקוד צריכים כפול אורך עבור הדטא של כל תוו. הסיבה פשוטה: SQL עובד עם פורמט UTF-16 (ולא כמו שמקובל עם UTF-8 שהוא הרבה יותר טוב). בכל ההודעה הזו כשאני אגיד יוניקוד אני מתכוון ל UTF-16 לכן.

    2. נכון כמו שכתבת אורי (אני רק מחליף אתצ המילה "שורה" במילה ערך "VALUE"), ש"אין מגבלה על אורך הערך", אבל חשוב מאוד להבין שיש הבדל  ע נ ק   בין שמירה של נתון כחלק מהרשומה ב PAGE כפי שניראה בתמונה מעל, לבין מצב בו אנחנו עושים שימוש למשל בערך גדול מאוד MAX והתוכן אינו נשמר ביחד עם תוכן של הרשומה אלא בנפרד (ברשומה במקרה זה יהיה רק פוינטר). אורי כתב על כך ואין טעם להרחיב :-)

    3. לא מדוייק להגיד ש "תו אחד יתפוס BYTE אחד בלבד" עבור VARCHAR. החלק הראשון היה מדוייק: טור VAR שומר לפי האורך האמיתי. אבל אם שומרים X תווים במסד הנתונים בטור VAR באורך X+Y, אז נכון שלא ישמרו בפועל X+Y אבל גם לא X אלא X+2.

    גם אם שומרים תוו אחד בתוך NVARCHAR עדיין השרת שומר את 2 הבייטים עבור האורך, ולכן קיבלתם הכפלה של אורך המידע שלכם במסד הנתונים (4 תווים במקום 2), ואם זה היה טור מסוג VARCHAR אז קיבלת הכפלה פי 3 (בייט אחד עבור התוו + 2 בטים עבור האורך)!

    מסקנה: לכן נכון מאוד מי שאמר שיש פגיעה גדולה בביצועים במקרים מסויימים, ביחס לשימוש בטורים קבועים כמו CHAR או NCHAR!

    מסקנה: אין אף פעם הגיון לטור VAR שהוא קטן מ 3 למשל. עדיף בדרך כלל לעבוד עם CHAR של 3 על פני VARCHAR של 3, גם אם חלק מהמידע שלנו הוא רק תוו אחד ויש לנו רק חלק של 3 תווים.

    4. נשאלה השאלה האם שימוש בטור VAR באורך 300 במקום באורך 30 יביא לפגיעה בביצועים (שיניתי את השאלה מ 3 ל 30 מכיוון שכמו שכתבתי 3 אמורים לשמור בטור באורך קבוע בכל מקרה בדרך כלל).

    התשובה כמו הכל בשרתי SQL אינה חד משמעית כפי שנכתב מעל :-)
    לא רק שהוא לא תמיד יביא לפגיעה אלא לפעמים יביא לשיפור בביצועים!

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

    כאשר מנוע המיטוב של השרת בונה את תוכנית ההרצה עבור כל שאילתה, הוא מקציב מקום בזכרון עבור ההרצה. מה קורה אם השרת נתקל בטור VAR של אורך 300?!? השרת לא "רוצה" לבזבז מקום בזכרון עבור 300 תווים כאשר אולי בטבלה יש רק 3 תווים, אבל מצד שני הוא רוצה לשמור מספיק זכרון כדי למטב את השאילתה. אם לא יהיה מספיק זכרון שמור הביצועים של השאילתה עלולים להיפגע! מייקרוסופט החליטו לקבוע אלגורתמים פשוט בו השרת שומר עבור טור כזה את הממוצע (לפי חצי מאורך הטור). עתה אם כל הנתונים בטור יכללו אורך מלא, אז ייתכן שהשרת לא יבצע מיטוב נכון.

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

    אני מקווה שזה קצת מסדר עוד נקודות ומוסיף לדברים של אורי :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]





    • נערך על-ידי pituachMVP, Moderator יום שלישי 01 ספטמבר 2015 15:31
    • סומן כתשובה על-ידי OMUser יום חמישי 03 ספטמבר 2015 19:35
    יום שלישי 01 ספטמבר 2015 10:24
    מנחה דיון