Implicit Conversion, Indexes and the gap in between
SQL Server 2012 מכיל חידושים רבים. רבות נכתב על הפונקציות והיכולות החדשות שנוספו למוצר, אך תחום אחד בקושי הוזכר – שיפורים בהצגת תוכניות הביצוע. ל-Execution Plan נוספו אזהרות חדשות, והיום אני רוצה להתמקד באזהרה אחת : אזהרת המרה לא-מפורשת(implicit conversion warnings).
נניח שאנו ניצבים מול המצב הבא:
טבלה א' הינה טבלת לקוחות וטבלה ב' הינה טבלת מכירות. הקישור הטבעי שרובנו נעשה הינו יחיד לרבים, שאומר לקוח אחד מסוגל לבצע הרבה קניות. אך לעיתים, מסיבות שונות ומשונות, מסד הנתונים הוקם כאשר השדה המקשר בטבלה ב' (שברוב המקרים אמור לשמש כמפתח זר) יהיה מטיפוס שונה משדה המפתח בטבלה א. בדוגמא המצורפת השדה המקשר בין טבלת מכירות לטבלת הלקוחות הוא מזהה הלקוח. אך, המפתח הראשי בטבלת לקוחות הינו מטיפוס מספר שלם, בעוד מזהה הלקוח נשמר בטבלת המכירות בעמודה מטיפוס מחרוזת.
נבצע שאילתא פשוטה אשר מקשרת בין שתי הטבלאות. אנו נרצה לראות את כל ההזמנות של לקוח 16240 – במידה וקיימות לו הזמנות (בכל הבדיקות נבצע את אותה השאילתא):
SELECT C.FirstName, C.LastName, S.SalesID, S.ProductID, S.Quantity
FROM [dbo].[Sales] S
INNER JOIN [dbo].[Customers] C ON S.CustomerIDString = C.CustomerID
WHERE C.CustomerID = 16240
נבחן את תוכנית הביצוע של השאילתא:
(38907 row(s) affected)
Table 'Sales'. Scan count 3, logical reads 48460
Table 'Customers'. Scan count 0, logical reads 2
SQL Server Execution Times:
CPU time = 1638 ms, elapsed time = 1891 ms.
ניתן לראות שתוכנית הביצוע לשאילתא בזבזנית:
שימוש בפרלליזם.
סריקת כל טבלת ההזמנות – Scan על ה- Clustered Index.
עלות של 41.40 (נתון זה ייחסי, ובהמשך נבחן את שיפור הביצועים)
בנוסף, ניתן להבחין בסימן קריאה בצמוד לצלמית הSelect. כאשר אנו מרחיבים את האינפורמציה, ניתן לראות שאנו מקבלים אזהרה:
“Type conversion in expression ColumnExpression may affect "CardinalityEstimate" in query plan choice”
מטרת השאילתא הינה חיפוש הזמנות של לקוח ספציפי, כך שביצוע פעולת Scan על כל טבלת ההזמנות אינה דבר רצוי. כדי לשפר את השאילתא ננסה ליצור אינדקס מכסה על שדה לקוח בטבלת הזמנות, בכדי שנוכל להתמקד בכלל ההזמנות שבוצעו ע"י לקוח מסויים.
CREATE NONCLUSTERED INDEX [IX_CustomerIDString] ON [dbo].[Sales]([CustomerIDString] ASC) INCLUDE (SalesID, ProductID, Quantity)
(38907 row(s) affected)
Table 'Sales'. Scan count 3, logical reads 22212
Table 'Customers'. Scan count 0, logical reads 2
SQL Server Execution Times:
CPU time = 1451 ms, elapsed time = 1506 ms.
נבחן את התוצאות: ניתן לראות שיש שיפור מבחינת עלות השאילתא: 22.02 אל מול 40.39. אך למרות שהקמנו אינדקס ספציפי לטובת החיפוש, עדיין אנו מקבלים פעולת Scan (שימו לב, אין מדובר כאן על נושא ה- Tipping Point מאחר והקמנו אינדקס מכסה). בואו נמשיך לבדוק את הממצאים. אם נביט על מספר הקריאות מטבלת המכירות, ניתן לראות שכמות הקריאות מהדיסק הצטמצמה משמעותית. למה בכל זאת קיים שיפור בשאילתא, אבל עדיין אנו מבצעים פעולת Scan? אם נתבונן מקרוב על הצלמית של ה-Scan נוכל לראות שהיא מבצעת סריקה כמו בתוכנית הקודמת. אבל הסריקה מתבצעת על האינדקס החדש שיצרנו. אותו אינדקס, IX_CustomerIDString קטן משמעותית מהClustered Index (שזה בעצם כל הטבלה). האינדקס החדש ממוקד למטרת השאילתא, לכן המנוע העדיף לסרוק אותו ולא את כל הטבלה. לפיכך, הסיבה היחידה לשיפור היא סריקה של אינדקס קטן יותר. ובכן, מדוע לאחר שיצרנו אינדקס שתואם למטרת השאילתא, אנו עדיין מקבלים סריקה של כל האינדקס?
נחזור לאזהרה שקיבלנו. למעשה, האזהרה מציינת שבעקבות ביצוע המרה במהלך השאילתא, מנוע ה-SQL אינו יכול להגיע להערכה מדוייקת לגבי מספר השורות שיהיו מעורבות בפעולה. לפיכך, עלולה להתקבל תוכנית ביצוע שאינה אופטימאלית.
"הפרד ומשול": נפרק את חלקי השאילתא. אנו מעוניינים בפרטי עסקאות של לקוח 16240. בכדי לקבל את התוצאה הרצויה אנו נדרשים לקשר בין שתי טבלאות: טבלת לקוחות אשר מכילה את פרטי הלקוח המבוקש, וטבלת מכירות שמכילה פרטי מכירה (מספר פריט וכמות). קשר בין 2 טבלאות מבוצע בעזרת השוואה (=,<,> וכו') בין שדות מקשרים, בפעולת JOIN. על- מנת להשוות, טיפוסי השדות חייבים להיות זהים – או ניתנים להמרה לטיפוס זהה. כאשר טיפוס שני שדות הקישור שונה, נדרשת פעולת המרה – מפורשת או לא-מפורשת (המרה לא-מפורשת מבוצעת ע"פ ראות עניו של השרת –http://msdn.microsoft.com/en-us/library/ms191517(v=sql.105).aspx ).
במקרה שלנו, קישורנו בין שתי הטבלאות בעזרת שני שדות מטיפוסים שונים (INT,VARCHAR).
מנוע ה-SQL נקט במקומנו בפעולת המרה וביצע המרה לא-מפורשת. טיפוס VARCHAR הומר לטיפוסINT(פונקציית CONVERT).
להלן ההמרה הלא-מפורשת -
ההבדלביןטיפוסעמודתCustomerIDStringלטיפוסעמודתCustomerID יוצרבעיה. מצד אחד מנוע ה-SQL חייב להמיר את אחת העמודות כך שאפשר יהיה להשוות ביניהן לצורך ביצוע ה- Join. המרה זאת מתבצעת באמצעות פונקציית המרה. מצד שני, השימוש בפונקציית ההמרה (ולמעשה כל שימוש בפונקציה על שדה מאונדקס) מונע מ- SQLלהשתמש באינדקס בצורה יעילה ולבצע seek.
מדוע פונקציה מונעת שימוש באינדקס בצורה ממוקדת (Seek)?
נחשוב על הדוגמה הבאה: אנו נגשים אל ספר טלפונים, ורוצים למצוא את הטלפון של פלוני אלמוני.
ספר טלפונים ממויין על-פי שמות משפחה:
נעבור על הספר. נמצא באיזה עמוד מתחילה האות א'. נדפדף אל העמוד ובגישה ממוקדת (Seek) נוכל למצוא את האדון.
אך אם נרצה למצוא את מספרי הטלפונים של כל האנשים, שבאות השנייה של שם משפחתם קיימת האות- ל', נמצא את עצמנו בבעיה. בכדי למצוא את האות השנייה, נאלץ להשתמש בפונקציה. מאחר שאין לנו שום ידע – אינדקס, על האות השנייה בשמות המשפחה, נאלץ לעבור (לסרוק) את כל ספר הטלפונים בכדי למצוא את כל האנשים המתאימים לתנאי החיפוש.
הדרך הישירה והמתבקשת לפתרון בעיית ההמרה הלא-מפורשת היא לשנות את טיפוס הנתונים של עמודת הלקוח בטבלת המכירות. הדבר ימנע את הצורך בהמרה, ונקבל תוכנית ביצוע אלגנטית ופשוטה.
(38907 row(s) affected)
Table 'Sales'. Scan count 1, logical reads 109
Table 'Customers'. Scan count 0, logical reads 2
אך, כאשר אנו מטפלים במקרה נקודתי, צריך תמיד לזכור באיזה סביבה אנו עובדים.
ברוב המקרים כאשר נגיע לייעץ לארגון, מערכות המידע נמצאות בשלב הייצור וקשה מאוד לבצע שינויים מהותיים במבנה מסד הנתונים או בקוד האפליקציה.
שינוי טיפוס נתונים הוא שינוי עמוק, שידרוש שינוי קוד של האפליקציה ו/או קוד TSQL.
לכאורה, השינוי מהותי וחשוב, אבל צריך לזכור שהשינוי לא מתמצה בהרצת פקודת ALTER TABLE,אלא בשעות עבודה יקרות של מפתחים, DBA ובודקים – וכמו כל דבר עסקי בחיים, הכל עניין של עלות מול תועלת. ברוב המקרים, לא תפקידנו להחליט על דרך הפעולה, אלא רק להצביע על האלטרנטיבות. שינויים בסביבה חיה ונושמת הם שינוים נדרשים וחלק מחיי היום – יום, אך תמיד צריך לחשוב על כל היבטי השינוי. במיוחד על צמצום גודל השינוי במקרה של נזקים משניים (השפעה על קוד האפלקציה, שרתי דוחות וכו').
עם מגבלות אלו אנו חייבים לחיות יד ביד, בשיתוף מלא עם יתר הצוותים. אך, למרות המגבלות הללו ניתן ורצוי לשפר את המצב הנתון.
נחזור לדוגמא שלנו – ספר טלפונים.
אם נייצר עמודה שמכילה את האות השנייה של כל שם משפחה, ונאנדקס אותה, נוכל לפתור את הבעיה בצורה יעילה. כמובן שלפתרון מסוג זה יש עלות: כתיבה נוספת לעוד עמודה, וקוד שיטפל בכל עדכון נתונים והכנסה של רשומות חדשות.
למזלנו, SQL מכיל אובייקט שנקרא "עמודה מחושבת" (Computed Column), המקל עלינו ליישם פתרונות מסוג זה. עמודה מחושבת היא עמודה בטבלה השומרת ביטוי. הביטוי יכול להשתמש בעמודה או מספר עמודות בטבלה,בפונקציות, קבועים או כל שילוב של אלה. הביטוי מחושב מחדש בכל פעם שניגשים לטבלה.
ניתן להגדיר עמודה מחושבת כעמודה קבועה (PERSISTED): עמודה מחושבת קבועה מכילה את תוצאת הביטוי, בצורה פיזית על הדיסק, בדיוק כמו שאר העמודות בטבלה.
בכל שינוי בשדות שמהם מחושבת העמודה, העמודה תחושב ותישמר מחדש. כאשר אנו שולפים נתונים מהטבלה אנו חוסכים בזמן עיבוד, מכוון שהנתונים כבר חושבו ואוכסנו פיזית.
עמודה מחושבת קבועה יעילה יותר במקרים בהם אנו נוטים להשתמש בטבלה לקריאה. מאחר ואין צורך לחשב מחדש את ערך העמודה בכל שליפה. כאשר אנו נוטים להשתמש בטבלה לכתיבה, נעדיף שהעמודה לא תהיה קבועה.
כיצד עמודה מחושבת מתחברת לפתרון הבעיה?
על עמודות מחושבות ניתן להקים אינדקסים. שימו לב שאם הגדרתם עמודה מחושבת שאינה קבועה, ואתם רוצים להקים עליה אינדקס – עליה לעמוד בכל הדרישות של עמודה מחושבת קבועה. הדבר נובע מכך שאינדקס הוא מבנה פיזי שנכתב לדיסק, כך שהקמת האינדקס על העמודה הופכת אותה למעשה לקבועה.
יש מספר דרישות להקמת אינדקס על עמודה מחושבת. זה לא המקום להרחיב בנושא. ניתן לקרוא על דרישות אלו בקישור הבא: http://msdn.microsoft.com/en-us/library/ms189292.aspx
נקים עמודה מחושבת:
ALTER TABLE [dbo].[Sales] ADD [CustomerIDComputed] AS (CONVERT([int],[CustomerIDString],0)) PERSISTED
נקים אינדקס על העמודה שיצרנו:
CREATE NONCLUSTERED INDEX [IX_CustomerIDComputed] ON [dbo].[Sales]([CustomerIDComputed] ASC) INCLUDE (SalesID, ProductID, Quantity)
השאילתא שלנו:
SELECT C.FirstName, C.LastName, S.SalesID, S.ProductID, S.Quantity
FROM [dbo].[Sales] S
INNER JOIN [dbo].[Customers] C ON S.CustomerIDString = C.CustomerID
WHERE C.CustomerID = 16240
(38907 row(s) affected)
Table 'Sales'. Scan count 1, logical reads 109
Table 'Customers'. Scan count 0, logical reads 2
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 631 ms.
שימו לב לאינדקס שבו המנוע בחר להשתמש. לכאורה אין קשר בינו ובין השאילתא, מאחר ולא שינינו את השאילתא, ולא התייחסנו בשום צורה לעמודה המחושבת שייצרנו.
לא רמזנו בשום צורה למנוע הSQL- להשתמש באינדקס החדש, והעמודה המקשרת בJOIN נשארה אותה עמודה – CustomerIDString.בכל זאת, מנוע הSQL-בחר להשתמש באינדקס החדש, כלומר פעולת הJOIN בוצעה מול העמודה המחושבת שהגדרנו.
קישורנו בין שתי הטבלאות בעזרת שני שדות מטיפוסים שונים (INT,VARCHAR).
מנוע ה-SQL יודע שבשביל לקשר בין הטבלאות נדרשת המרה סמויה.
פונקציית ההמרה(CONVERT) קיימת בעמודה המחושבת (CONVERT([int],[CustomerIDString],0). המנוע ידע לגשת לעמודה המחושבת, המכילה את הביטוי הנדרש לביצוע הJOIN, במקום לחשב את הערך בזמן הריצה.
מאחר והמנוע עבר להשתמש בעמודה המחושבת, הוא מסוגל להשתמש גם באינדקס שיצרנו.
איזה חיסכון. לא בוצע שום שינוי אפליקטיבי, כל ההפניות בפרוצדורות ובאפליקציה נשארות כשהיו. לא עברנו על שום קוד שקשור לטבלה, מכוון שהשימוש באינדקס החדש עובד כאשר קוראים לעמודה המקורית – שזה כשלעצמו חיסכון אדיר בזמן עבודה.
נבחן את ביצועי השאילתא :
העלות הצטמצמה ל- 0.29.
אין פרלליזם.
צמצום פעולת הסריקה על האינדקס בעזרת SEEK.
והכי חשוב, לא שינינו שום קוד TSQL.
כאשר ניסינו לשנות את טיפוס העמודה לINT, כמות הקריאות היתה 109. בשאילתא הנוכחית לאחר הקמת העמודה המחושבת והאינדקס הגענו לאותה כמות קריאות. מבחינת העלות הכוללת של תוכנית הביצוע התוצאה כמעט זהה – פשוט מדהים.
שימו לב שהאזהרה עדיין נשארת בעינה, למרות שהיא טופלה כהלכה. אך האזהרה שרתה את מטרתה בכך שהפנתה את תשומת ליבנו לבעיה.
אינדקס על עמודה מחושבת יכול לשפר ביצועים במקרים רבים, מעבר לבעיות הנובעות מהמרה:
שימוש בתת מחרוזת משדה כקישור לטבלה אחרת.
שרשור של מספר שדות כקישור לטבלה אחרת.
חיפוש על-פי חלק מתאריך (לדוגמא: שנה או חודש ספציפי).
למעשה כל שימוש בפונקציות, המונע ממנו שימוש יעיל באינדקסים.
לסיכום:
חשוב להכיר את האזהרות החדשות של SQL Server 2012, מאחר והן יכולות לעזור לנו להבין את תוכנית הביצוע, ואם ניתן לשפר אותה בהתאם למוצע. כשניגשים לתוכנית ביצוע חשוב להכיר את הסימנים\צלמיות של תוכנית הביצוע. כל סימן שאיננו מכירים צריך להתייחס אליו כאל סימן חשוד שעלול לגרום לביצועים גרועים עד שהוכח אחרת.
ביצענו פתרון זול, מאחר וההשבתה מינימאלית וההשלכות על הקוד מצומצמות. אך, יחד עם זאת מאוד יעיל.
עצם המחשבה שניתן להוסיף עמודה מחושבת ועליה אינדקס אינה חדשה – במיוחד לא בגרסה 2012. אך, גישה לאינדקס החדש בשילוב העמודה המקורית, מקנה לנו יכולת להישאר עם הקוד המקורי וחוסכת לנו הרבה עבודה.
באופן עקרוני הוספת עמודה מחושבת לטבלה אמורה להיות שקופה עבור אפליקציה שעובדת מול הטבלה. אך, במקרים מסויימים האפליקציה לא תדע להתמודד עם שינוי מסוג זה. במידה והחלטתם להוסיף עמודה מחושבת למערכת חיה, יש לבצע את המהלך בתיאום עם צוותי הפיתוח, ולוודא שמבוצע מהלך QA מעמיק לשינוי בסביבת הבדיקות.
