Posted by: siamnobita | 06/14/2009

Tuning Function

ตามที่สัญญาไว้ในครั้งที่แล้ว ว่าจะยกตัวอย่างที่เคย tuning จริงมา post วันนี้ขอเริ่มด้วยกรณีการเรียกใช้ stored function ที่เขียนขึ้นเอง โดยไม่จำเป็น

ตัวอย่างนี้ เพิ่งได้มาสด ๆ ร้อน ๆ จากน้องในที่ทำงานเดียวกัน แต่อยู่คนละ project ซึ่ง project ของเขาเป็นโรงงานแห่งหนึ่ง ที่จริงเขาก็ยังไม่ได้ขอให้ tune query ให้ แค่ขอคำปรึกษาเรื่องการใช้ analytic function เท่านั้น แต่พอผมเห็น statement ของเขาแล้วมันอดไม่ได้ ก็เลยแนะนำเรื่องการ tune แถมให้ด้วยเลย

ฟังก์ชั่นของเขาจะรับค่าเวลาที่สินค้าถูกผลิตขึ้น และคำนวณว่าเวลานั้นตกอยู่ในช่วงใด ซึ่งแบ่งเป็นชั่วโมง ๆ หน้าตาเป็นแบบนี้ครับ

CREATE OR REPLACE FUNCTION GET_ATTIME1
( start_time IN NUMBER
) RETURN varchar2 IS
  rs_time VARCHAR2(6) ;
BEGIN
  if start_time = 80000 then
    rs_time:= '08:00';
  end if;
  if start_time >= 80001 and start_time <= 90000 then
    rs_time:= '09:00';
  end if;
  ...
  if (start_time >= 230001 and start_time <= 240000)
    or start_time = 0 then
    rs_time:= 't0:00';
  end if ;
  if start_time >= 1 and start_time <= 10000 then
    rs_time:= 't01:00';
  end if ;
  ...
  if start_time >= 70001 and start_time <  80000 then
    rs_time:= 't08:00';
  end if ;
  RETURN rs_time;
end ;
/

จากนั้นก็นำไปเรียกใช้ในลักษณะดังนี้

select get_attime1(starttime), count(*) cnt
from tt1
group by get_attime1(starttime) ;

จะเห็นว่าหนึ่งแถวใน tt1 ก็จะมีการเรียกใช้ function หนึ่งครั้ง ดู ๆ ไป ก็ไม่ได้ซับซ้อนอะไรนัก แต่จะมีผลต่อ performance หรือไม่ ขึ้นกับจำนวนแถวทั้งหมดใน tt1 ผมได้ลองทดสอบโดยสร้างข้อมูลขึ้นมาแบบสุ่ม จำนวน 500,000 แถว เพื่อจะทดลองผลของการ tuning (ดู code ทั้งหมด ได้ ที่นี่ ครับ )

ขั้นแรก พิจารณาในตัว code ที่น้องเขาเขียน จะเห็นว่า ใช้ if ย่อย ๆ หลาย if แทนที่จะใช้ elsif จุดนี้จะช่วยตัดทอนการทำงานที่ไม่จำเป็นออกได้ เนื่องจากถ้าใช้ if เราต้องทำการเปรียบเทียบทุกกรณีที่ระบุ แต่ถ้าเอา elsif เข้ามาช่วย เมื่อเจอกรณีที่ให้ค่าจริงปุ๊บ ก็หยุดปั๊บ ไม่ทำงานส่วนที่เหลือทันที (ดูที่ code get_attime2 ) ผลลัพธ์ที่ทดลองบนเครื่องของผม เวลาลดลงจาก 3.75 วินาที เหลือประมาณ 3.3 วินาที

อย่างไรก็ดี วิธีการที่ผมใช้ในการ tune จริง ๆ คือการเปลี่ยนมาใช้ buit-in function ล้วน ๆ ครับ จาก code ข้างบน ผมเขียนโดยใช้ sql function ได้ดังนี้

select (case
          when ( starttime < 80000 or starttime >= 230001)
            then 't'
          else null
        end)
       ||to_char(mod(ceil( starttime/10000 ),24),'fm09')||':00'
  , count(*) cnt
from tt1
group by (case
          when ( starttime < 80000 or starttime >= 230001)
            then 't'
          else null
        end)
       ||to_char(mod(ceil( starttime/10000 ),24),'fm09')||':00'  ;

ดูยุ่งยากกว่าเยอะนะครับ ใครเห็น code นี้คงมึนไปเหมือนกันว่าคนเขียนอยากทำอะไร แต่ผลลัพธ์ของ query นี้ได้เหมือนเดิมเป๊ะ ในเวลาเพียงประมาณ 0.75 วินาทีครับ

เวลาที่ลดลงไปเป็นผลมาจากการที่เราลดการ switching ระหว่าง sql engine กับ pl/sql engine ครับ ถ้าเทียบเวลาที่ใช้ในการคำนวณด้วยฟังก์ชั่นที่ผมเขียนกับการใช้ if แล้ว การคำนวณผมช้ากว่าด้วยซ้ำ ดูได้จาก function get_attime3 ซึ่งผมเขียนโดยเลียนแบบ built-in function ที่ผมใช้ ผลที่ได้ใช้เวลาถึง 5.5 วินาทีด้วยซ้ำ ดังนั้นหากฟังก์ชั่นนี้ถูกเรียกใช้ใน pl/sql เป็นหลักแทนที่จะใช้ใน sql statement การใช้ get_attime2 ก็จะให้ผลลัพธ์ที่ดีกว่า

หนังสือหลาย ๆ เล่มพูดถึงการ tuning function ด้วยการกำหนดให้ function นั้น เป็น deterministic function ลองดูว่าผลลัพธ์เป็นอย่างไร ผมใช้ code เดียวกับ get_attime2 มาสร้างเป็น deterministic function get_attime4 ผลลัพธ์ที่ได้ผมมองไม่เห็นความแตกต่างเลยครับ เรื่อง deterministic function นี้ผมว่าผมเอามาเขียนเป็นอีกหนึ่งหัวข้อได้เลย เพราะฉะนั้นอดใจรอหน่อยแล้วกันนะครับ

ถ้าคุณคิดว่า tune แค่นี้ก็โอเคแล้ว ยังครับ ยังมีอีกหนึ่ง trick เอาผลลัพธ์ของฟังก์ชั่นมาเก็บไว้ในตารางก่อน แล้ว join เอา เนื่องจาก input ของฟังก์ชั่นนี้มันมีจำนวนจำกัด แค่เวลาใน 24 ชั่วโมงเท่านั้น ตารางที่ใช้ในการเก็บผลลัพธ์ของเราก็ไม่ได้ใหญ่โตอะไร (ตาราง tt2) ผลลัพธ์จากการ join ที่หลายคนกลัวกันนักหนา เหลือเพียง 0.5 วินาทีครับ อย่างไรก็ดี trick นี้ เปลี่ยนจาก cpu load มาเป็น I/O load ดังนั้นก็อยู่ที่ server ของคุณว่า bottleneck อยู่ที่ใด ต้องทำการเปรียบเทียบเองครับ แต่ก็ถือเป็นอีกหนึ่งทางเลือกได้

น่าสนใจไหมครับ ถ้าเห็นว่าน่าสนใจ comment ติชมกันหน่อยนะครับ แล้วคราวหน้าผมจะนำตัวอย่างอื่น ๆ มาให้ดูอีกครับ

ขอบคุณครับ


Responses

  1. ถ้าต้องใช้งานคำสั่งนั้นบ่อยๆ
    จะเอามาอยู่ใน sql ก็ไม่เหมาะ เพราะ ถ้าแก้ไขก็ต้องแก้หลายจุด ต้อง copy code หลายจุด
    จะเอามาไว้ใน function ก็ช้า เพราะต้องทำงานไปที่ pl sql engine

    จะแก้ไขอย่างไรดีครับ

  2. ตอบคุณ meng ครับ

    ผมได้พูดถึงเรื่องนี้ใน Tuning Function Part II ไว้บ้างแล้วครับ โดยเสนอทางเลือกคือ view ซึ่งเท่ากับเป็นการซ่อนสูตรการคำนวณนั้นไว้ที่เดียว เช่นเดียวกับ stored function แต่ความยืดหยุ่นอาจจะน้อยกว่า เพราะผูกติดกับแต่ละ table ขณะที่ function นำไปใช้กับ table ไหนก็ได้

    แต่ในทางปฏิบัติแล้ว เราคงต้องหาจุดที่เหมาะสมที่สุดที่ตรงกับปัญหาของเราครับ กฏข้อแรกในการ tuning คือเราต้องมีเป้าหมายก่อนอื่นเลยครับ จริง ๆ แล้วจากตัวอย่างที่ผมแสดงมาในหัวข้อนี้ เห็นได้ชัดว่า built-in function เร็วกว่า user-defined function แต่หากดูเวลาที่ใช้จริง ๆ กับข้อมูลจำนวน 500,000 แถวมันต่างกันเพียง 2 วินาทีเท่านั้น แม้ว่าเทียบเป็นเปอร์เซ็นต์อาจจะมาก แต่เวลาที่ใช้จริง ๆ นั้น อาจไม่ใช่ปัญหาก็ได้ ขึ้นอยู่กับสถานการณ์ที่เราเผชิญอยู่ครับ ถ้าเรามีเป้าหมายว่า query นี้ต้องทำได้ภายใน 1 วินาที ทางเลือกของเราก็จะเป็นเป็นแบบหนึ่ง แต่ถ้าเป้าหมายเรายืดหยุ่นกว่านั้น ทางเลือกก็จะกลายเป็นอีกแบบหนึ่ง
    ในกรณีที่เราดูกันอยู่นี้ เมื่อใช้กับตารางหนึ่งข้อมูลไม่มาก function อาจเป็นตัวเลือกที่ดี ขณะที่อีกตารางควรใช้ view ส่วนอีกตารางไม่ได้ใช้บ่อย ก็เขียน sql ตรง ๆ

    สิ่งที่ดีที่สุด ไม่ได้เป็นสิ่งที่ดีที่สุดเสมอไปครับ


ใส่ความเห็น

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out / เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out / เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out / เปลี่ยนแปลง )

Connecting to %s

หมวดหมู่

%d bloggers like this: