הבלוג של גרי רשף

28/08/2011

מיחזור חישובים

Filed under: Uncategorized — גרי רשף @ 18:56

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

Use tempdb;
Go

If Object_Id('Try','U') Is Not Null Drop Table Try;
Go

Create Table Try(I Float);
Go

Insert
Into   Try
Select 1.00 Union All
Select 1.01 Union All
Select 1.02 Union All
Select 1.03 Union All
Select 1.04 Union All
Select 1.05 Union All
Select 1.06 Union All
Select 1.07 Union All
Select 1.08 Union All
Select 1.09;
Go

Select  *
From    Try;
Go

clip_image002

ונבצע שליפה עם כמה פעולות שחוזרות על עצמן תוך בדיקת ה-Execution Plan:

Select  *,
        I+1 I1
From    Try
Where   I+1>0
Order By I+1;

clip_image004

ה אנחנו רואים:

1. Table Scan הכולל פילטור ובמהלכו חושב מן הסתם הערך I+1.

2. Sort- התבצע מיון שכולל כנראה חישוב מחדש של I+1.

3. Compute Scalar- חושב שוב הערך I+1 להצגה בפלט.

האמנם? אולי המערכת מספיק חכמה כדי לדעת ש- I+1 הוא חישוב סרק במקרה זה?

נבצע את הכל מחדש עם טבלה בעלת Primary Key (כלומר- אינדקס על העמודה I):

If Object_Id('Try1','U') Is Not Null Drop Table Try1;
Go

Create Table Try1(I Float Primary Key);
Go

Insert
Into   Try1
Select 1.00 Union All
Select 1.01 Union All
Select 1.02 Union All
Select 1.03 Union All
Select 1.04 Union All
Select 1.05 Union All
Select 1.06 Union All
Select 1.07 Union All
Select 1.08 Union All
Select 1.09;
Go

Select  *,
        I+1 I1
From    Try1
Where   I+1>0
Order By I+1;
Go

clip_image006

אותי התוצאה מפתיעה: המערכת לא ביצעה כלל מיון, ומכאן שהיא "ידעה" שהמיון הקיים באינדקס לפי I זהה למיון לפי I+1.

מה יקרה אם במקום חישוב טריוויאלי כמו I+1 נשתמש בחישוב קצת יותר מורכב כמו חישוב שורש?

Select *,
       Sqrt(I) I1
From   Try1
Where  Sqrt(I)>0
Order By Sqrt(I);
Go

clip_image008

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

כיצד נדע עם Compute Scalar הוא חישוב בודד או שלושה חישובים שונים? נשווה בין שני Execution Plans של שליפות מהטבלה, כשהראשונה כוללת חישוב פשוט, והשניה מאה (100) חישובים מורכבים:

Select *,
       I+1
From   Try;

Select  *,
        Power(I,1),Power(I,2),Power(I,3),Power(I,4),Power(I,5),Power(I,6),Power(I,7),Power(I,8),Power(I,9),Power(I,10),
        Power(I,11),Power(I,12),Power(I,13),Power(I,14),Power(I,15),Power(I,16),Power(I,17),Power(I,18),Power(I,19),Power(I,20),
        Power(I,21),Power(I,22),Power(I,23),Power(I,24),Power(I,25),Power(I,26),Power(I,27),Power(I,28),Power(I,29),Power(I,30),
        Power(I,31),Power(I,32),Power(I,33),Power(I,34),Power(I,35),Power(I,36),Power(I,37),Power(I,38),Power(I,39),Power(I,40),
        Power(I,41),Power(I,42),Power(I,43),Power(I,44),Power(I,45),Power(I,46),Power(I,47),Power(I,48),Power(I,49),Power(I,50),
        Power(I,51),Power(I,52),Power(I,53),Power(I,54),Power(I,55),Power(I,56),Power(I,57),Power(I,58),Power(I,59),Power(I,60),
        Power(I,61),Power(I,62),Power(I,63),Power(I,64),Power(I,65),Power(I,66),Power(I,67),Power(I,68),Power(I,69),Power(I,70),
        Power(I,71),Power(I,72),Power(I,73),Power(I,74),Power(I,75),Power(I,76),Power(I,77),Power(I,78),Power(I,79),Power(I,80),
        Power(I,81),Power(I,82),Power(I,83),Power(I,84),Power(I,85),Power(I,86),Power(I,87),Power(I,88),Power(I,89),Power(I,90),
        Power(I,91),Power(I,92),Power(I,93),Power(I,94),Power(I,95),Power(I,96),Power(I,97),Power(I,98),Power(I,99),Power(I,000)
From    Try;

clip_image010

בשני המקרים ה-Estimated CPU cost הוא 0.000001, ואני מתרשם שזה "מחיר קבוע" שאינו תלוי בסיבוכיות החישוב ובכמות החישובים, ולכן לא ניתן ללמוד מזה דבר.

אם כך נסתכל על ה- Statistics Timeונשווה בין חישוב בודד של החזקה לחישוב מרובה אך זהה שלה:

Set Statistics Time On

Select  *,
        Power(I,10)
From    Try;

Select  *,
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
       Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),
        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10)
From Try;

 

Set Statistics Time Off

 

clip_image012

בשליפה הראשונה חישבנו פעם אחת את החזקה העשירית ובשניה – חמש מאות פעם.

חישוב חזקה זו פעולת CPU, וניתן לראות שבשניה- עלות ה-CPU גבוהה יותר, ומכאן- המערכת חישבה שוב ושוב את החזקה.

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

ביצעתי בדיקה נוספת מכיוון אחר: במקום חישוב של Scalar נבצע חישוב בעזרת שאילתת משנה:

Select  *,
        (Select Max(I) From Try T2 Where T2.I<=T1.I) I2
From    Try T1
Where   (Select Max(I) From Try T2 Where T2.I<=T1.I)>=0
Order By (Select Max(I) From Try T2 Where T2.I<=T1.I);

clip_image014

ניתן לראות שחישוב תת השאילתה מתבצע שלוש פעמים, למרות שמדובר באותה תת שאילתה (הקפדתי להעתיק אותה על ידי Ctrl C + Ctrl V כדי לוודא שאין הבדלים בטקסט), ובנוסף- בדיקה בעזרת Statistics IO אישרה את ממצאי ה-Execution Plan.

מה יקרה אם נמקם את ה-Select ללא הפילטור והמיון בתוך CTE ונפנה לתוצאה של החישוב?

With T As
(Select *,
        (Select Max(I) From Try T2 Where T2.I<=T1.I) I2
From    Try T1)
Select  *
From    T
Where   I2>=0
Order By I2;

clip_image016

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

סיכום: כאשר שליפה כוללת ביטוי או חישוב החוזר על עצמו מספר פעמים – היא מחשבת אותו מחדש.

כדי למנוע זאת ניתן להפוך את השליפה עם החישוב לשאילתת משנה או ל-CTE, ואז לפנות לתוצאה שלו (של החישוב) מספר פעמים כנדרש.

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

%d בלוגרים אהבו את זה: