הטיפים שלי לשיפור ביצועי שאילתות

הנושא של שיפור ביצועי שאילתות הואנ רחב מאוד, כמו שקובע חוק מרפי - "מה שיכול להשתבש - ישתבש", ותמיד יש מה לשפר כי תמיד משהו איכשהו לא יעבוד כפי שרצינו.

הסיבה העיקרית לכך לטעמי היא טבעה של שפת SQL: זו שפה דקלרטיבית, בה הפקודות מציינות מה אני רוצה, ולא איך לבצע את זה (כמקובל בשפות פרוצדורליות כדוגמת #C וג'אווה); מה שהופך את השפה למאוד ידידותית מצד אחד, אבל גם בעייתית מצד זה שהמערכת אולי תבצע את מה שביקשנו - אך לא בהכרח בדרך הנכונה, מסיבות שונות. אם כך מספר טיפים ממני, אבל כל מי שבתחום יוכל כמובן להציע טיפים אחרים לא פחות טובים!

אינדקסים

רוב השאילתות הן ברורת מאליהן, והביצועים שלהן תלויים בדבר אחד - באינדקסים. למשל:

Select Name,
        Address
From   Employees
Where  ID=1234;

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

הכל תלוי כאן באינדקס: אינדקס על עמודה ID שבתנאי, ורצוי שתכלול גם את שתי העמודות שב-Select.

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

לא לכל שאילתה יוצרים אינדקס, ויש לשקול היטב שכר אינדקס כנגד הפסדו; ובנוסף - לא פעם לא ברור מה האינדקס האופטימלי וזה כרוך בניסוי וטעייה.

שיפור השאילתות

נושא זה רלוונטי לשאילתות מורכבות ופינות בעייתיות, לא לשאילתות ברורות מאליהן כמו הנ"ל.

גם כאן - דוגמאות יש בשפע, ואסתפק באחת שנתקלתי בה לא מעט במקום עבודתי, והכוונה לשימוש בפונקציות התאריך DatePart, DateAdd, DateDiff; למשל:

Select  *
From    MyTable
Where   DatePart(Year,DateColumn)=2023;

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

Select  *
From    MyTable
Where   DateColumn>='20230101' And DateColumn<'20240101';

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

עדכון סטטיסטיקות

לכאורה משהו שרץ באמצע הלילה וקשור רק בעקיפין למה שאנחנו מריצים עכשיו, אך ללא סטטיסטיקות מעודכנות - המערכת עלולה לבחור ב-Execution Plans לא מתאימים ולפגוע בביצועים. למשל: אם אנחנו מבצעים Join בין שתי טבלאות, יש מספר דרכים בהן המערכת יכולה להתאים את השורות בטבלה הזו לשורות בטבלה השנייה; וכדי לבחור בפתרון המתאים יש צורך בסטטיסטיקות מעודכנות.

שורה תחתונה להקפיד ולהריץ עדכון סטטיסטיקות (Update Statistics) ואם אפשר פעם ביום, או ליתר דיוק בלילה: כשכולם ישנים שהשרת יעבוד בשבילנו..

פירוק שאילתות

לעתים שאילתות חייבות לרוץ כיחידה אחת, גם כשמדובר בשאילתה מורכבת; אבל לפעמים ניתן לפרקה לשלבים בפרוצדורה (למשל).

מתי נרצה לפרק? דוגמה:

Select  a.Col1,
        b.Col2
From    Tbl1 a
Inner Join MyLnkdSrvr.MyDB.dbo.Tbl2 b
        On a.Col1=b.Col1
Where   b.Col2>=101;       

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

Select  Col1,
        Col2
Into    #Tbl2        
From    MyLnkdSrvr.MyDB.dbo.Tbl2
Where   Col2>=101;

Create Clustered Index Idx_#Tbl2
On #Tbl2(Col1);

Select  a.Col1,
        b.Col2
From    Tbl1 a
Inner Join #Tbl2 b
        On a.Col1=b.Col1
Where   b.Col2>=101; 

תכנון נכון של בסיס הנתונים

אחרון אחרון חביב בפוסט, אבל ראשון בסדר הפעולות.

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

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

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

כפי שאפשר להבין, סוף מעשה במחשבה תחילה: תכנון נכון של הדטבייס מראש, תכליתו שליפה יעילה של המידע בדיעבד.