en-UShe-IL
You are here:  Blog > new Forums FAQ > Forums FAQ 345
Register   |  Login
Minimize

שאלה: יעילות שאילתות.

נתונות הטבלאות הבאות:

table2:
date                 | country_code | b
------------------------------------
2011-07-01      | FR                  | 32
2011-07-01      | DE                  | 30
2011-07-01      | UK                  | 36
2011-07-01      | IL                    | 34
2011-07-02      | FR                  | 32
2011-07-02      | DE                  | 38
2011-07-02      | UK                  | 25
2011-07-02      | IL                    | 28
2011-07-03      | FR                  | 34
2011-07-03      | DE                  | 30
2011-07-03      | UK                  | 42
2011-07-03      | IL                    | 34
table1:
date                 | country_code | a
------------------------------------
2011-07-01      | FR                  | 10
2011-07-01      | DE                  | 12
2011-07-01      | UK                  | 15
2011-07-01      | IL                    | 8
2011-07-01      | TR                  | 10
2011-07-02      | FR                  | 20
2011-07-02      | DE                  | 18
2011-07-02      | UK                  | 22
2011-07-02      | IL                    | 35
2011-07-02      | TR                  | 12
 

ונתונות 2 השאילתות הבאות:

SELECT date, SUM(a) a, SUM(b) b
FROM (
            SELECT date, SUM(a) a, 0 b FROM table1
            GROUP BY date
            UNION ALL
            SELECT date, 0 a, SUM(b) b FROM table2
            GROUP BY date
) report
GROUP BY date
 
SELECT date, SUM(a) a, SUM(b) b
FROM (
            SELECT date, a, 0 b FROM table1
            UNION ALL
            SELECT date, 0 a, b FROM table2
) report
GROUP BY date

השאלה: איזה מהשאילתות יעילה יותר?

תגובה:

בוא ננסה להוביל אותך לפתרון לבד:

* הערה: כרגע לא נדבר על בנייה של שאילתה מיטבית יותר מאלו שאתה הגעת אליהן, אלא רק נחקור את 2 המקרים שאתה הצגת ונראה כיצד להשוות בינן

** הערה חשובה!

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

זו היא הערה לכל השואלים! בפורומים רבים בעולם (כמו בפורומים של התמיכה של SQL באנגלית) בלי לצרף נתון זה בכלל לא יתחילו לעזור לכם ויש לזה סיבה.

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

אז נתחיל ממה שהיית אמור לתת לנו וזה שאילתה של יצירת הטבלאות שלך ושאילתת הכנסת נתונים לדוגמה:

create table table1
             ([date] datetime,[country_code] varchar(3),[a] int)

create table table2
             ([date] datetime,[country_code] varchar(3),[b] int)

go

insert table1
SELECT '20110701','FR'  ,10 union
SELECT '20110701','DE'  ,12 union
SELECT '20110701','UK'  ,15 union
SELECT '20110701','IL'  ,8 union
SELECT '20110701','TR'  ,10 union
SELECT '20110702','FR'  ,20 union
SELECT '20110702','DE'  ,18 union
SELECT '20110702','UK'  ,22 union
SELECT '20110702','IL'  ,35 union
SELECT '20110702','TR'  ,12
GO

insert table2
SELECT '20110701','FR'  ,32 union
SELECT '20110701','DE'  ,30 union
SELECT '20110701','UK'  ,36 union
SELECT '20110701','IL'  ,34 union
SELECT '20110702','FR'  ,32 union
SELECT '20110702','DE'  ,38 union
SELECT '20110702','UK'  ,25 union
SELECT '20110702','IL'  ,28 union
SELECT '20110703','FR'  ,34 union
SELECT '20110703','DE'  ,30 union
SELECT '20110703','UK'  ,42 union
SELECT '20110703','IL'  ,34
GO

עכשיו רק אפשר להתחיל לדון בשאלה!

בוא בצע את הפעולות הבאות ונבדוק את יעילות השאילתות שלנו על פי ממצאי מנוע המיטוב של השרת:

1. נרשום את 2 השאילתות אחת מתחת לשנייה באותו קובץ SQL

SELECT date, SUM(a) a, SUM(b) b
FROM (
      SELECT date, SUM(a) a, 0 b FROM table1
      GROUP BY date
      UNION ALL
      SELECT date, 0 a, SUM(b) b FROM table2
      GROUP BY date
) report
GROUP BY date

SELECT date, SUM(a) a, SUM(b) b
FROM (
      SELECT date, a, 0 b FROM table1
      UNION ALL
      SELECT date, 0 a, b FROM table2
) report
GROUP BY date

2. נסמן את 2 השאילתות ביחד

3. נבחר ב "הצג תוכנית הרצה"

והתוצאה:

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

כדי להתרשם טוב יותר נסה את הפעולה תמיד שוב כשהפעם אתה הופך את סדר השאילתות. במקרה הנוכחית תראה שאין הבדל והשאילתה שמבצעת GROUP BY בתוך השאילתה הפנימית בסוגריים יוצאת כ 66% לעומת 34% לשאילתה בה מבוצע GROUP BY פעם אחת בלבד.

* אם רוצים לדייק יותר בבדיקת תוכנית הרצה ומיטוב שאילתות בודקים כל שאילתה בנפרד. יש להקפיד להריץ מספר פעמים לפני שמקבלים מסקנה ולא לקפוץ לתוצאת ההרצה הראשונה

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