[DB2] แค่เปลี่ยน Query ชีวิตก็ดีขึ้นแล้ว JOIN vs Sub Query

วันนี้พอดีมาประจำการที่ Site และได้ดูเคสบ่นๆจาก User ว่าทำไมหน้า Exchange Rate มันช้าจังเลย เปิดทีรอ 2-3 นาทีได้ ตอนแรกเราก็เฉยๆอยู่ เพราะงานหลักของเรา คือ BOT DMS (DataSet กับ DataFile 2 พี่น้อง) แต่พอสักสายๆ เข้า BA บอกว่าเปิดไม่ได้เลย เค้าลอง Interface feed ข้อมูล ย้อนหลัง 10 ปี เข้าไป น่าจะประมาน 100,000 Record ได้

หลังจากผมเข้าไปดูที่เครื่อง BA สิ่งที่ผมเข้าใจได้ คือ Query มัน Timeout ครับ รีบไปเปิด Code ทันทีเลยครับ มาดู Query กัน

SELECT A.PAIRID
     , A.CURRENCYPAIRCODE 
     , UI1.USERNAME AS CREATEBY
     , ME.CREATETIME 
     , UI2.USERNAME AS PROVEBY 
     , ME.PROVETIME 
FROM CURRENCYPAIR A 
  LEFT OUTER JOIN (      
              SELECT MEX.CREATEBY
                   , MEX.CREATETIME
                   , MEX.PROVEBY
                   , MEX.COMMODITYID
                   , MEX.QUOTEDID
                   , MEX.PROVETIME
              FROM MARKETEXCHANGE MEX 
              WHERE MEX.ACTIVEFLAG = 'A'              
                AND MEX.CREATETIME = (SELECT MAX(ME2.CREATETIME) AS CREATETIME                   
                                      FROM MARKETEXCHANGE ME2                   
                                      WHERE ME2.ACTIVEFLAG = 'A' 
                                        AND MEX.COMMODITYID = ME2.COMMODITYID                   
                                        AND MEX.QUOTEDID = ME2.QUOTEDID              
                                      )      
                  ) ME 
             ON A.COMMODITYID = ME.COMMODITYID        
                AND A.QUOTEDID = ME.QUOTEDID 
       LEFT OUTER JOIN USERINVEST UI1 
        ON ME.CREATEBY = UI1.USERID 
          AND UI1.ACTIVEFLAG = 'A' 
       LEFT OUTER JOIN USERINVEST UI2 
        ON ME.PROVEBY = UI2.USERID 
          AND UI2.ACTIVEFLAG = 'A' 
       LEFT OUTER JOIN CURRENCY B
        ON A.COMMODITYID = B.CURRENCYID
          AND B.ACTIVEFLAG = 'A' 
       LEFT OUTER JOIN CURRENCY C
        ON A.QUOTEDID = C.CURRENCYID 
          AND C.ACTIVEFLAG = 'A' 
WHERE A.ACTIVEFLAG = 'A' 
ORDER BY A.CURRENCYPAIRCODE

จาก Query ข้างต้นนะครับ เท่าที่ดูมาใช้ LEFT JOIN ปกตินะ แต่มีอยู่ก้อนนึง ที่มีความพิเศษเป็นการ Join ที่เกิดจากการ SELECT และก้อนที่มีการ SELECT มา JOIN เนี่ย มีใช้ Sub Query ต่ออีก โดยผมขอตัดมาให้ดูนะครับ

SELECT MEX.CREATEBY
     , MEX.CREATETIME
     , MEX.PROVEBY
     , MEX.COMMODITYID
     , MEX.QUOTEDID
     , MEX.PROVETIME
 FROM MARKETEXCHANGE MEX 
 WHERE MEX.ACTIVEFLAG = 'A' 
   AND MEX.CREATETIME = (
                         SELECT MAX(ME2.CREATETIME) AS CREATETIME      
                         FROM MARKETEXCHANGE ME2      
                         WHERE ME2.ACTIVEFLAG = 'A' 
                           AND MEX.COMMODITYID = ME2.COMMODITYID      
                           AND MEX.QUOTEDID = ME2.QUOTEDID 
                        )      

ถ้ามองที่ Query แล้ว โอ้วมีการใช้ Sub Query ที่มีการ Join กับ Query หลักด้วย (AND MEX.COMMODITYID = ME2.COMMODITYID  AND MEX.QUOTEDID = ME2.QUOTEDID) คิดดูเล่นๆ ข้อมูล 100,000 Record มาก็เอามา Cross กับอีก 100,000 Record โอ้วมหาศาลมากครับ เพื่อที่จะหาข้อมูลที่ตรงเงื่อนไข ลองมาส่องดูจาก Execution Plan ดูครับ

คราวนี้ผมลองมาปรับ Query ครับ จากเดิมที่ Cross กับ Sub Query ตรงๆ ผมเปลี่ยนให้มีจัดกลุ่ม (Group ฺฺฺBy) ข้อมูลก่อนครับ แล้วค่อยหา Max จากข้อมูลเต็ม 100,000 Record เนี่ย มันเหลือน้อยลงครับ(น่าจะเหลือตาม Currency Pair ที่มีในระบบประมาณ 27 อันครับ) แล้วมา Join กับ Query หลักเพื่อให้ได้ผลลัพธ์ออกมา

SELECT MEX.CREATEBY
     , MEX.CREATETIME
     , MEX.PROVEBY
     , MEX.COMMODITYID
     , MEX.QUOTEDID
     , MEX.PROVETIME
FROM MARKETEXCHANGE MEX 
       INNER JOIN (
                   SELECT MAX(MEX.CREATETIME) AS CREATETIME
                        , MAX(MEX.PROVETIME)  AS PROVETIME
                        , MEX.COMMODITYID
                        , MEX.QUOTEDID             
                   FROM MARKETEXCHANGE MEX 
                   WHERE MEX.ACTIVEFLAG = 'A'   
                   GROUP BY MEX.COMMODITYID
                          , MEX.QUOTEDID
                  ) MAXEX
              ON MAXEX.COMMODITYID = MEX.COMMODITYID
                     AND MAXEX.QUOTEDID = MEX.QUOTEDID
                     AND MAXEX.CREATETIME = MEX.CREATETIME
                     AND MAXEX.PROVETIME = MEX.PROVETIME
WHERE MEX.ACTIVEFLAG = 'A' 

ลองมาดู Execution Plan ของ Query ที่มีการปรับปรุงดูครับ Step น้อยลงกว่าเดิมเยอะครับ

ลองเอา Execution Plan ของทั้ง 2 แบบเทียบกันดูครับ พบว่า Step น้อยลงกว่าเดิมครับ

• ของเก่าช่วง Sub Query ในกรอบสีแดง มี Step เยอะมาก และใช้ Index หลายจุดครับ ตรงนี้ใช้ Cost น้อยครับ แต่ตรง NLJOIN นี่ใช้ Cost เยอะมากกกกกกกครับ 10,246.17
• ของใหม่ช่วง JOIN ในกรอบสีน้ำเงิน มี Step น้อย ใช้ Cost เยอะกว่าอันแรก แต่ตรง HSJOIN ใช้ Cost น้อยครับ เพียง 619.37 น้อยกว่าแบบเดิมมากกว่า 10 เท่าเลย
• สำหรับตัว NLJOIN กับ HSJOIN ผมขอแตกไปเขียน Blog แยกนะครับ เขียนต่อคงยาวเกิ้น 5555

และสุดท้าย Query ที่ทำการปรับปรุงแล้ว จากเดิมใช้เวลาประมาน 2-3 นาที เหลือประมาณ < 1 วินาทีเท่านั้นครับ

SELECT A.PAIRID
     , A.CURRENCYPAIRCODE 
     , UI1.USERNAME AS CREATEBY
     , ME.CREATETIME 
     , UI2.USERNAME AS PROVEBY 
     , ME.PROVETIME 
FROM CURRENCYPAIR A 
  LEFT OUTER JOIN (      
              SELECT MEX.CREATEBY
                   , MEX.CREATETIME
                   , MEX.PROVEBY
                   , MEX.COMMODITYID
                   , MEX.QUOTEDID
                   , MEX.PROVETIME
              FROM MARKETEXCHANGE MEX 
                INNER JOIN (SELECT MAX(MEX.CREATETIME) AS CREATETIME
                                 , MAX(MEX.PROVETIME) AS PROVETIME
                                 , MEX.COMMODITYID
                                 , MEX.QUOTEDID             
                            FROM MARKETEXCHANGE MEX 
                            WHERE MEX.ACTIVEFLAG = 'A'   
                            GROUP BY MEX.COMMODITYID
                                   , MEX.QUOTEDID) MAXEX
                  ON MAXEX.COMMODITYID = MEX.COMMODITYID
                    AND MAXEX.QUOTEDID = MEX.QUOTEDID
                    AND MAXEX.CREATETIME = MEX.CREATETIME
                    AND MAXEX.PROVETIME = MEX.PROVETIME
              WHERE MEX.ACTIVEFLAG = 'A'         
                  ) ME 
             ON A.COMMODITYID = ME.COMMODITYID        
                AND A.QUOTEDID = ME.QUOTEDID 
       LEFT OUTER JOIN USERINVEST UI1 
        ON ME.CREATEBY = UI1.USERID 
          AND UI1.ACTIVEFLAG = 'A' 
       LEFT OUTER JOIN USERINVEST UI2 
        ON ME.PROVEBY = UI2.USERID 
          AND UI2.ACTIVEFLAG = 'A' 
       LEFT OUTER JOIN CURRENCY B
        ON A.COMMODITYID = B.CURRENCYID
          AND B.ACTIVEFLAG = 'A' 
       LEFT OUTER JOIN CURRENCY C
        ON A.QUOTEDID = C.CURRENCYID 
          AND C.ACTIVEFLAG = 'A' 
WHERE A.ACTIVEFLAG = 'A' 
ORDER BY A.CURRENCYPAIRCODE