code:
/****************************************** DDL+DML */
Declare @Emp TABLE (
deptId INT ,
empName VARCHAR(30),
empAge INT );
insert into @Emp values
(1,'moshe',30),
(1,'john',28),
(1,'mark',45),
(3,'mahmood',35),
(3,'tonny',50),
(5,'jonny',48),
(5,'migel',31),
(5,'katan',41)
/****************************************** query 1: using sub quereis */
;--sub quereis
with CTE as (select distinct deptId from @Emp)
select Depts.deptId
,(select top 1 empName from @Emp t where t.deptId = Depts.deptId order by t.empAge) youngest
,(select top 1 empName from @Emp t where t.deptId = Depts.deptId order by t.empAge desc) oldest
from cte Depts
/****************************************** query 2: using RANK */
;--RANK
with CTE as (select deptId
,empName
,ROW_NUMBER() over (PARTITION by deptId order by empage) yuoungRank
,ROW_NUMBER() over (PARTITION by deptId order by empage desc) oldRank
from @Emp)
select ranks1.deptId
,ranks1.empName youngest
,(select empName from CTE ranks2 where ranks2.deptId=ranks1.deptId and ranks2.oldRank=1) oldest
from cte ranks1
where ranks1.yuoungRank =1
/****************************************** query 3: using MIN/MAX */
--MIN/MAX
select * from
(
select
deptId,empName
,[MinMax] = case
when MYMin = empAge then 'min'
when MyMax = empAge then 'max'
else null
end
from
(
select
*
, MIN(empAge) over (PARTITION BY deptId) MYMin
, MAX(empAge) over (PARTITION BY deptId) MyMax
from @Emp
) Tbl
where MYMin = empAge or MyMax = empAge
) Tbl
pivot (min(empName) for [MinMax] in ([min],[max])) as p
אז מה יש לנו כאן:
בשלב ראשון אנו מריצים את כל השאילות ביחד כדי שנוכל לראות בגדול את אחוז המשאבים שמושקע בכל שאילתה. מכיוון שאנחנו מריצים 3 שאילתות ביחד (למעשה 4 כולל השאילתה של ה DML) ההבדלים לא מוצגים במדוייק. על פני השטח נראה שיש "רק" פי 3 הבדל בין השאילתה הראשנה לאחרונה. אבל כדי לקבל רזולוציה טובה יותר אני אריץ בשלב השני השוואה רק בין השאילתה הראשונה לאחרונה. כך נוכל לראות נקודתית במספר פעולות שנראות בתוכנית ההרצה שנבלעו בהרצה הראשונה בשל כך שהן זניחות ביחס לעולות המרכזיות שיוצרות את ההבדל, הרי הן פעולות ה SORT. ובשלב השלישי נריץ רק את השאילתה השלישית על מנת לבדוק רזולוציה מקסימלית של מה קורה בתוכה.
בהרצה ראשונה אנו מקבלים יחסי אחוזי השקעה כך: 45% (שאילתה ראשונה), 30% (שנייה), 10% (שאילתה שלישית)
ניתן לראות שהפעולה הכבדה ביותר בכל תוכניות ההרצה היא פעולות ה SORT. ההבדל הגדול בין השאילות נובע ישירות ממספר הפעמים שאנו מבצעים SORT. קיבלנו למעשה בדיוק את היחס של מספר הפעמים שמבצוע SORT. למעשה נבלעו לנו פעולות נוספות שנותנות ייתרון לשאילתה האחרונה כפי שנראה. אלו הן פעולות פחות כבדות ולכן כדי להציג אותן בבירור נעבור לשאילתה שנייה
הפעולה הנוספת הבאה בתור מבחינת משקל שיש לנו היא פעולת סריקה הטבלה. ניתן לראות שיחסית ל SORT היא נבלעת בחישוב האחוזים הכללי בהרצה הראשונה אבל בהרצה השנייה היינו מצפים שנקבל יחס של 1-3 ז"א 21-63, בין שאילתה ראשונה לאחרונה אלא שאנו מקבלים 21-64 ז"א יש לנו אחוז הבדל נוסף :-) מהיכן מגיע אחוז זה?
אם נשים לב בשאילתות הראשונות מבוצע סריקה של הבטלה כמספר של פעולות ה SORT. ז"א בשאילתה ראשונה מבוצע 3 פעמים סריקה של כל הטבלה (כמובן שדבר זה ישתנה במערכת חיה בה נעבוד עם אינדקסים למשל... כרגע אנחנו לא דנים במיטוב השאילתות אלא רק בבסיס של כללי אצבע). עם סה בשאילתה האחרונה מבוצע פעם אחת בלבד סריקה של הטבלה ובפעמיים הנוספות נעשה שימוש בטבלה זמנית שהשרת בונה בזמן ההרצה table spool. על מנת להדגיש אף יותר את ההבדל נריץ פעם נוספת רק את השאילתה האחרונה ונבדוק מה ההבדל מבחינת משאבים בין סריקה של הטבלה לבין השימוש ב table spool
בהרצה השלישית כשאנו מריצים את השאילתה השלישית בלבד אנו רואים שפעולת ה SORT לוקחת כאמור הכי הרבה משאבים 77% ופעולת סריקת הטבלה לוקחת 22% אבל נראה שפעולות ה Table Spool פשוט זניחות ולוקחות 0% משאבים.
זהו ההבדל הנוסף של האחוז שקיבלנו בהרצה השנייה
תמונות תוכניות הרצה
הערה: בתמונות הבאות חתכתי את תמונת תוכניות הרצה והשארתי רק את האחוזים של משאבי השימוש בכל חלק של השאילתה. על מנת לראות את תוכנית ההרצה המלאה יש ללחוץ על התמונה (התמונה היא קישור לתמונה המלאה)
הרצה ראשונה: כל השאילתות ביחד
הרצה שנייה: שאילתות 1+3
הרצה שלישית של השאילתה האחרונה בלבד (אין משמעות להראות רק את האחוזים כמובן כי זה ייתן פשוט אחוז ביחס לשאילתת ה DML של הכנסת הנתונים ולכן נציג את התוכנית בתמונה מוקטנת)
.
.