บทความแรกของปี 2016 เลย (จริงๆ Plan ว่าจะเขียนนานแล้ว แต่ติดงานประจำที่ Change บ่อยมากๆ) วันนี้เลยไปเจอ Shell Script ของ AIX ตั้งนึง ที่ทำหน้าดึงข้อมูลมาใส่ใน Temp Table และส่งออกเป็น Text File ไประบบอื่นผ่าน FTP แต่ปัญหา คือ ว่ามันช้ามากกกกกกกกกกกกก รันเกือบ 20 นาทียังไม่ออกเลย
SELECT DISTINCT I1.PID,
I1.VALUEDATE,
I1.SECURITY_ID,
I1.SECURITY_CODE,
I2.EFFECTIVE_STARTDATE,
I3.EFFECTIVE_ENDDATE,
I1.REFERENCE_RATE_CURVE,
I1.TENOR,
I1.REFERENCE_RATE,
I1.SPREAD,
I1.FIXED_RATE,
I1.TRANS_FLAG,
I1.LEG_FLAG,
I2.CASHFLOWID AS MINCASHFLOWID
FROM INVEST.I_INV_SCHEDULE AS I1
INNER JOIN INVEST.I_INV_SCHEDULE AS I2
ON I2.VALUEDATE = I1.VALUEDATE
AND I2.SECURITY_ID = I1.SECURITY_ID
AND I2.SECURITY_CODE = I1.SECURITY_CODE
AND I2.CASHFLOWID = (SELECT MIN(I.CASHFLOWID)
FROM INVEST.I_INV_SCHEDULE AS I
WHERE I.VALUEDATE = I1.VALUEDATE
AND I.SECURITY_ID = I1.SECURITY_ID
AND I.SECURITY_CODE =
I1.SECURITY_CODE
AND (
( I.TRANS_FLAG = 'FIXED'
AND I.FIXED_RATE =
I1.FIXED_RATE
AND I.LEG_FLAG IS NULL )
OR ( I.TRANS_FLAG = 'FIXED'
AND I.FIXED_RATE =
I1.FIXED_RATE
AND I.LEG_FLAG =
I1.LEG_FLAG )
OR ( I.TRANS_FLAG = 'FLOAT'
AND I.SPREAD =
I1.SPREAD
AND I.REFERENCE_RATE =
I1.REFERENCE_RATE
AND I.REFERENCE_RATE_CURVE IS
NULL
AND I.TENOR IS NULL
AND I.LEG_FLAG IS NULL )
OR ( I.TRANS_FLAG = 'FLOAT'
AND I.SPREAD = I1.SPREAD
AND I.REFERENCE_RATE =
I1.REFERENCE_RATE
AND I.REFERENCE_RATE_CURVE IS
NULL
AND I.TENOR IS NULL
AND I.LEG_FLAG =
I1.LEG_FLAG )
OR ( I.TRANS_FLAG = 'FLOAT'
AND I.SPREAD = I1.SPREAD
AND I.REFERENCE_RATE_CURVE =
I1.REFERENCE_RATE_CURVE
AND I.TENOR = I1.TENOR
AND I.REFERENCE_RATE IS NULL
AND I.LEG_FLAG IS NULL )
OR ( I.TRANS_FLAG = 'FLOAT'
AND I.SPREAD = I1.SPREAD
AND I.REFERENCE_RATE_CURVE =
I1.REFERENCE_RATE_CURVE
AND I.TENOR IS NULL
AND I.REFERENCE_RATE IS NULL
AND I.LEG_FLAG =
I1.LEG_FLAG ) ))
INNER JOIN INVEST.I_INV_SCHEDULE AS I3
ON I3.VALUEDATE = I1.VALUEDATE
AND I3.SECURITY_ID = I1.SECURITY_ID
AND I3.SECURITY_CODE = I1.SECURITY_CODE
AND I3.CASHFLOWID = (SELECT MAX(I.CASHFLOWID)
FROM INVEST.I_INV_SCHEDULE AS I
WHERE I.VALUEDATE = I1.VALUEDATE
AND I.SECURITY_ID = I1.SECURITY_ID
AND I.SECURITY_CODE =
I1.SECURITY_CODE
AND (
( I.TRANS_FLAG = 'FIXED'
AND I.FIXED_RATE =
I1.FIXED_RATE
AND I.LEG_FLAG IS NULL )
OR ( I.TRANS_FLAG = 'FIXED'
AND I.FIXED_RATE =
I1.FIXED_RATE
AND I.LEG_FLAG =
I1.LEG_FLAG )
OR ( I.TRANS_FLAG = 'FLOAT'
AND I.SPREAD =
I1.SPREAD
AND I.REFERENCE_RATE =
I1.REFERENCE_RATE
AND I.REFERENCE_RATE_CURVE IS
NULL
AND I.TENOR IS NULL
AND I.LEG_FLAG IS NULL )
OR ( I.TRANS_FLAG = 'FLOAT'
AND I.SPREAD = I1.SPREAD
AND I.REFERENCE_RATE =
I1.REFERENCE_RATE
AND I.REFERENCE_RATE_CURVE IS
NULL
AND I.TENOR IS NULL
AND I.LEG_FLAG =
I1.LEG_FLAG )
OR ( I.TRANS_FLAG = 'FLOAT'
AND I.SPREAD = I1.SPREAD
AND I.REFERENCE_RATE_CURVE =
I1.REFERENCE_RATE_CURVE
AND I.TENOR = I1.TENOR
AND I.REFERENCE_RATE IS NULL
AND I.LEG_FLAG IS NULL )
OR ( I.TRANS_FLAG = 'FLOAT'
AND I.SPREAD = I1.SPREAD
AND I.REFERENCE_RATE_CURVE =
I1.REFERENCE_RATE_CURVE
AND I.TENOR IS NULL
AND I.REFERENCE_RATE IS NULL
AND I.LEG_FLAG =
I1.LEG_FLAG ) ))
WHERE I1.VALUEDATE = '2015-11-29'
AND ( ( I2.TRANS_FLAG = 'FIXED'
AND I2.FIXED_RATE = I1.FIXED_RATE
AND I2.LEG_FLAG IS NULL )
OR ( I2.TRANS_FLAG = 'FIXED'
AND I2.FIXED_RATE = I1.FIXED_RATE
AND I2.LEG_FLAG = I1.LEG_FLAG )
OR ( I2.TRANS_FLAG = 'FLOAT'
AND I2.SPREAD = I1.SPREAD
AND I2.REFERENCE_RATE = I1.REFERENCE_RATE
AND I2.REFERENCE_RATE_CURVE IS NULL
AND I2.TENOR IS NULL
AND I2.LEG_FLAG IS NULL )
OR ( I2.TRANS_FLAG = 'FLOAT'
AND I2.SPREAD = I1.SPREAD
AND I2.REFERENCE_RATE = I1.REFERENCE_RATE
AND I2.REFERENCE_RATE_CURVE IS NULL
AND I2.TENOR IS NULL
AND I2.LEG_FLAG = I1.LEG_FLAG )
OR ( I2.TRANS_FLAG = 'FLOAT'
AND I2.SPREAD = I1.SPREAD
AND I2.REFERENCE_RATE_CURVE = I1.REFERENCE_RATE_CURVE
AND I2.TENOR = I1.TENOR
AND I2.REFERENCE_RATE IS NULL
AND I2.LEG_FLAG IS NULL )
OR ( I2.TRANS_FLAG = 'FLOAT'
AND I2.SPREAD = I1.SPREAD
AND I2.REFERENCE_RATE_CURVE = I1.REFERENCE_RATE_CURVE
AND I2.TENOR IS NULL
AND I2.REFERENCE_RATE IS NULL
AND I2.LEG_FLAG = I1.LEG_FLAG ) )
AND ( ( I3.TRANS_FLAG = 'FIXED'
AND I3.FIXED_RATE = I1.FIXED_RATE
AND I3.LEG_FLAG IS NULL )
OR ( I3.TRANS_FLAG = 'FIXED'
AND I3.FIXED_RATE = I1.FIXED_RATE
AND I3.LEG_FLAG = I1.LEG_FLAG )
OR ( I3.TRANS_FLAG = 'FLOAT'
AND I3.SPREAD = I1.SPREAD
AND I3.REFERENCE_RATE = I1.REFERENCE_RATE
AND I3.REFERENCE_RATE_CURVE IS NULL
AND I3.TENOR IS NULL
AND I3.LEG_FLAG IS NULL )
OR ( I3.TRANS_FLAG = 'FLOAT'
AND I3.SPREAD = I1.SPREAD
AND I3.REFERENCE_RATE = I1.REFERENCE_RATE
AND I3.REFERENCE_RATE_CURVE IS NULL
AND I3.TENOR IS NULL
AND I3.LEG_FLAG = I1.LEG_FLAG )
OR ( I3.TRANS_FLAG = 'FLOAT'
AND I3.SPREAD = I1.SPREAD
AND I3.REFERENCE_RATE_CURVE = I1.REFERENCE_RATE_CURVE
AND I3.TENOR = I1.TENOR
AND I3.REFERENCE_RATE IS NULL
AND I3.LEG_FLAG IS NULL )
OR ( I3.TRANS_FLAG = 'FLOAT'
AND I3.SPREAD = I1.SPREAD
AND I3.REFERENCE_RATE_CURVE = I1.REFERENCE_RATE_CURVE
AND I3.TENOR IS NULL
AND I3.REFERENCE_RATE IS NULL
AND I3.LEG_FLAG = I1.LEG_FLAG ) )
ORDER BY I1.SECURITY_CODE
, MINCASHFLOWID จาก Query ตัวอย่าง เราจะพบว่า มัน Self Join เยอะมากก ซึ่งใน DBMS ถ้าตัวเจ๋งๆ มันจะแลาดพอที่จะแปลง Query ให้ดีขึ้น หรืออย่างน้อยมี Execution Plan ช่วยตรวจสอบครับ (ราคา License ของ DB แต่ละเจ้าที่มันแพงๆ ก็เพราะ Feature เหล่านี้แหละ)
งานที่ทำอยู่ DB2 เลยลองใช้ Execution Plan บน Toad และกัน ตัว IBM Data Studio มันโคตรกินแรม 5555
Execution Plan ที่ได้ครับ พบว่ามันมีส่วนของ Table Scan เยอะมากกกกครับ จำเป็นที่จะต้องลง Table Scan ให้น้อยที่สุด โดยการเพิ่ม Index ครับ การเพิ่ม Index ที่ดี เราต้องมาดู Query ที่เราใช้กันบ่อยๆก่อนนะครับ เพราะ ถ้าเพิ่ม Index ไม่เหมาะสมมันส่งผลกับการ Insert/Update นะ และตอนนี้จากการ Scan Query คร่าวๆ ดูแล้วพวกว่ามีการ Self join เยอะครับ โดยดูจาก Field "SECURITY_ID" และ "VALUEDATE" ครับ
CREATE INDEX INVEST.IDX_I_INV_SCHEDULE1
ON INVEST.I_INV_SCHEDULE
( SECURITY_ID ASC, VALUEDATE ASC )
DISALLOW REVERSE SCANS
COMPRESS NO
INCLUDE NULL KEYS;
COMMIT;Execution Plan หลังจากเพิ่ม Index เร็วขึ้นเหลือ 30 วินาทีครับ และมีการดึงจาก Index แทนการใช้ Table Scan ครับ
เปรียบเทียบ Cost ก่อน และหลัง เพิ่ม Index ครับ จากของเดิมที่เป็น TBSCAN (Table Scan ที่ใช้ Cost เยอะมาก) ไปใช้ IXSCAN (Index Scan) แทนครับ
เรื่องนี้เป็น Step ต้นๆ ในการปรับแต่ง Query ให้เร็วขึ้นครับ หากนึกอะไรออก ผมจะทยอยมาเขียนบทความเพิ่มนะครับ ^___^
Discover more from naiwaen@DebuggingSoft
Subscribe to get the latest posts sent to your email.