ปัญหาเรื่อง SQL Sub Query นี่เป็นปัญหาใหญ่จริงๆครับ

[magicstudent]

ปัญหาเรื่อง SQL Sub Query กับข้อมูลจำนวนมหาศาล

จากงานที่ได้รับมา ต้องทำการ Query ข้อมูลพนักงาน ประมาณ 6000 คน ที่เก็บแบบประเมินผล
คนละ 20-30 หัวข้อ งานที่ได้เหมือนๆจะดูง่าย ไม่ยากเท่าไร กับข้อมูล ประมาณ 180000 แถว
แต่ชีวิต มันไม่ง่ายอย่างนั้น เพราะลูกค้า เอากระจายออกเป็น 10 กอง จากนั้นจึงส่งข้อมูลกลับมายังเรา
เพื่อให้ทำการประมวลผลข้อมูล ข้อมูลที่ได้รับ ไม่สามารถเอามารวมกันทันที เนื่องจากมีเงื่อนใขที่เปลี่ยนไป
จากเดิม แต่ผลลัพธ์ที่ลูกค้าต้องการคือ ให้รวมกับมาเป็นข้อมูล 1 ชุดเหมือนเดิม และต้องแยกเอาคนที่
มีปัญหาออกมาจากคนที่ไม่มีปัญหาได้ด้วย ผมทำใน access นะครับ ต้องขอบอกก่อนว่า ที่ต้องใช้
access เพราะว่าตัว report ที่ใช้งานถูกออกแบบไว้เสร็จแล้วใน access นะครับ ทำให้หลายๆท่าน
ที่หวังดีกับผมจะแนะนำมาว่า ทำไม ไม่ใช้ sql server ,mysql หรือแม้แต่ oracle ล่ะครับ คำตอบ
มันง่ายๆครับ คนที่จะจ่ายตังค์ให้ผม เขาใช้ access อยู่ และผมก็ประเมิณดูแล้วข้อมูล ขนาดนี้ access
เอาไหว แน่ๆ แต่ที่มันมีปัญหา มันเป็นปัญหาเรื่อง เทคนิค ของการเขียน sql อย่างไรให้เกิดประสิทธิภาพสูงที่สุด
เพราะปัญหาเดียวกันนี้ ผมเอาไปค้นหาดูพวกฝรั่งตาน้ำข้าวที่ใช้ทั้ง sql server oracle หรือตัวอื่นๆใช่ว่า
จะไม่มีปัญหานะครับ แค่ปัญหาข้อนี้ ที่สั่งให้ access ทำงาน คือค้นหารายชื่อพนักงาน จากฐานข้อมูลของ
พนักงานจำนวน 6000 คน เลือกตัดเอาเฉพาะคนที่มีปัญหาออกมา โดยคนที่มีปัญหา ก็คือคนที่เป็น Sub set ของ
ของกลุ่มคนที่เสร็จแล้วนั่นเอง ซึ่งต้องไปค้นหาเอาจาก อีก table ปัญหาเกิดจากตรงนี้แหละที่ต้องการค้นหาจาก
ข้อมูล table อื่น แล้วข้อมูลมันไม่คืนกลับมาเป็น 1 ตัว แต่มันจะคืนกลับมาเป็นกลุ่ม (set of Values)
ลองดู SQL ด้านล่างนี้

SELECT T1.empID FROM temployee AS T1
WHERE T1.empID NOT IN (SELECT DISTINCT T2.empID FROM tDetail AS T2)

ผมไม่ได้เขียนผิดนะครับ ที่จริงทำงานได้ แต่จะเกิดปัญหาอยู่ 2 ข้อ
1.ช้ามากๆ.....................มากๆๆๆๆๆๆๆๆ
2.memory out ไปเลย ถ้าใครมีน้อยๆ

ปัญหาที่เกิดขึ้นก็คือ ข้อมูลที่อยู่ใน Sub query ที่จะคืนกลับมาประมาณ 4000-5000 คน
และข้อมูลที่จะเอามาเทียบกับประมาณ 6000 คนใน table แรก ถ้าคิดกันเบาะๆ ก็ประมาณ 30 ล้านรอบนั่นเอง
อย่าคิดว่า accces  จะทำไม่ได้นะครับ ทำได้ในเวลาที่ผมยังจิบกาแฟยังไม่หมดถ้วยเลยด้วยซ้ำไป
แต่ ทำได้แบบมีปัญหานะครับ ปัญหาก็คือ ทำแล้วแต่ยังไม่รอให้ผลเสร็จก่อน รีบเอามาแสดงผลก่อน
ประมาณ 1 page ซึ่งคุณจะไม่สามารถเอาผลไปใช้ได้ เพราะจะยังไม่สามารถสรุปได้ว่ามีกี่คนกันแน่
ต้องรอต่อไป จนคุณอาจกินกาแฟท้องแตกตาย หรือเจ็บใจจนตายที่เสียเวลารอมัน เพราะสุดท้าย
มันก็จะ hang ไปเลย เครื่องผม core 2 (new) 2.4  RAM 4 gb ยังเดี้ยง หลังจากนั้นผม ก็เสียเวลา
ค้นหาวิธีแก้ใข ไม่ว่าจะจาก microsoft เอง  หรือ google 3 วันไปแล้ว ก็ยังหาวิธีแก้ที่เหมาะๆ และตอบโจทย์จริง
มิได้ จน เมื่อคืนนี้เองจึงเกิดดวงตาแห่งธรรมขึ้นมาว่า คำสอนของพระพุทธองค์ ท่านตรัสไว้ "ตนแล เป็นที่พึ่ง แห่งตน"
จึงมาคิดใหม่ทำใหม่ แบบละเอียดขึ้น ว่าเรายังมองข้ามด้านใดไปบ้าง และมีแนวทางอื่นๆ ที่สามารถ ประยุกตร์
เพิ่มเข้ามากับแนวทางเดิมที่มีปัญหา ได้บ้างมั้ย จนพบว่า ถ้าเราจะกระจายงานออกเป็น 2 ส่วน คือตรงข้อมูลในส่วน
sub query เราก็ไปทำแยกให้เรียบร้อยก่อน ซึ่งผมได้แยกออกเป็น table ใหม่ขึ้นมาเลย ชื่อ tempFromDetail
จากนั้นจึงมาลอง เขียน sql ใหม่เป็นด้านล่างนี้

SELECT T1.empID FROM temployee AS T1
WHERE T1.empID NOT IN (SELECT  T2.empID FROM tempFromDetail AS T2)

โดยตัด DISTINCT ออกเนื่องจาก table tempFromDetail มี filed เดียว และไม่มีข้อมูลซ้ำกันเลย
มีฝรั่งคนนึงมันแนะนำไว้ว่าคำสั่งนี้ อาจทำให้ช้าได้ ซึ่งมันก็ขัดแย้งกับฝรั่งคนอื่นๆอยู่เหมือนกันนะ แต่ผมแก้ปัญหา
แบบต่อยอดไปเลยโดยแทนที่จะไม่ใช้ คำสั่งนี้ เราไปสร้าง table ที่มันไม่มีแถวที่ต้องซ้ำกันเลยน่าจะดีกว่า
ผลปรากฎว่า สามารภ run ผ่านได้เพียงแค่ผม กำลังจะเริ่มจิบกาแฟ ยังไม่หายร้อนเลยด้วยซ้ำไป ก็เอามาบอกเล่า
สู่กันฟัง เผื่อเป็นประโยชน์ต่อท่านอื่นๆ ความรู้ยิ่งแจกจ่ายก็ยิ่งมีความสุขต่อคนให้นะครับ ท่านพุทธองค์สอนไว้
ให้อะไร ก็ไม่มีความสุขเท่าให้ความรู้กับเขา ก็ช่วยๆ shareๆ กันบ้างนะครับ ส่วนใครจะ copy ไปเผยแพร่ก็ยินดี แต่
ช่วยให้ เครดิตกับเว็ปนี้บ้าง จะขอบคุณมากๆเลย

musicman

[sompohj]

  เท่าที่จับประเด็นดูคือ

  แยก (SELECT DISTINCT T2.empID FROM tDetail AS T2)


  ให้ออกมาเป็น table ตัวนึงใช่ไหมครับ

  ถ้าใช่ คือเอา logical table  (SELECT DISTINCT T2.empID FROM tDetail AS T2)

  มาเป็น physical table tempFromDetail



  แสดงว่าต้อง collect ข้อมูล มา insert หรือ update     ตาราง   tempFromDetail


  ใช่ไหมครัีบ


  บางทีเกร็ดเล็กน้อยก็เป็นประโยชน์มากนะครับ


  ขอบคุณที่ื share ครับ


  ขอเล่าเรื่องบางอย่างหน่อย พอดีไปดูโปรแกรม ที่เขาซื้อกัน 1.2 ล้านบาท ใช้ oracle ทำงาน

  ปรากฏว่าโปรแกรมนี้ เขาทำ transaction  ไม่ได้

  ผมเลยออกอาการแหยๆ ว่า ใช้ฐานข้อมูลซะหรูเชียว แต่เอาไว้เก็บ

  ข้อมูลใน table เฉยๆ   55555


  เอาไว้มีเรื่องบางอย่างจะ share มั่ง แต่ผมกลัวว่าหลายท่านในที่ีนี้จะรู้มากกว่าผมแล้วดี๊

[magicstudent]

ครับ บางทีการแก้ปัญหาโดยการพิจารณาปัญหาใหญ่แล้ว
ค่อยๆย่อยลงจนแยกต่ออีกไม่ได้ จากนั้นแก้ปัญหาย่อยๆก่อน
ซึ่งจะง่ายกว่า แล้วจึงค่อยๆประกอบมันขึ้นมา เพื่อแก้ปัญหาที่ใหญ่ๆอีกที
จะช่วยให้สามารถแก้ปัญหาใหญ่ๆได้ดีขึ้น แต่ยังงัยการแลกเปลี่ยนความรู้และ
ต่อยอดไปจากของเดิมจะช่วยลดเวลาลงอย่างมากๆ ผมถึงถามหลายๆ
ท่านที่เข้ามาถามปัญาว่า ได้ค้นจาก google แล้วหรือยัง เพราะว่าตั้งแต่
มี google และ open source มานี่ผมว่าชีวิตผมง่ายขึ้นกว่าเมื่อก่อน
เยอะนะ(ต้องขอบคุณ) 

วิเคราะห์<-กระบวนการ->สังเคราะห์

[rstx_snap]

น่าจะใช้คำสั่งนี้นะครับ ถึงจะเร็ว    NOT IN ไม่น่าจะ Optimize

SELECT T1.empID
FROM temployee AS T1
WHERE  not exists (SELECT * FROM tDetail AS T2 where t1.empID=T2.empID)

[magicstudent]

น่าจะใช้คำสั่งนี้นะครับ ถึงจะเร็ว    NOT IN ไม่น่าจะ Optimize

SELECT T1.empID
FROM temployee AS T1
WHERE  not exists (SELECT * FROM tDetail AS T2 where t1.empID=T2.empID)

ขอบคุณนะครับ ที่มาช่วยแจม พอดีตอนนั้นคำสั่ง NOT IN ยังพอยอมรับ performance ได้
เพราะเป็นการเรียกใช้เพียงครั้งเดียวหลังจากนั้นก็ไม่ถูกเรียกใช้อีกผมก็เลยไม่ได้มองหาคำสั่ง
อื่นๆมาเพื่อ optimize code แต่ก็ขอบคุณที่แนะนำครับ ตั้งแต่มี case นี้แล้วก่อนจะ release
งานออกไป(ถึงจะเป็นแค่เบต้าก็ตาม)ให้ลูกค้าผมต้องนั่งเขียน case ก่อนเสมอ (ให้ความสำคัญมากๆ)
ดังนั้นตอนนี้ ปัญหานี้จะเกิดขึ้นน้อยลงแล้วล่ะครับ ที่จริงการเขียนคำสั่งไม่ว่าจะเป็นการ coding
หรือ sql ฝรั่งนี้เขาจะทำตาม standard เข้มงวดเลย ต้องไปฟังคุณโอฬารแกเล่าเพราะแกเจอมาเยอะครับ
เมื่อก่อนไม่เคยสนใจตอนนี้มองเห็นประโยชน์เมื่อเจอปัญหาบ่อยๆหนักๆและซ้ำซากนี่ล่ะครับ 
นี่ก็ว่าจะหาเวลาไปเรียนกับแกเรื่อง scm(Agile) อยู่ว่างๆก็มาแนะนำกันอีกนะครับ ขอบคุณจริงๆ