ไม่สร้าง Index File ใหม่ได้มั๊ย

ใครที่ต้องทำหน้าที่ DB-Admin บน DB2/400 ที่เขียนด้วย RPG (โดยไม่ใช้ SQL)
คงเคยมึนกับปัญหานี้
    Developer ต้องการสร้าง Index ใหม่เพื่อความรวดเร็วในการสร้าง report ที่มีการเรียงลำดับ
มีคนแซวว่า  แทบจะสร้าง 1 report ต่อ 1 index File กันเลย

Q:บริษัทคุณมีรายงาน (สร้างด้วย RPG ที่ไม่ใช้ SQL) กี่ตัว ?
ถ้าวันนี้มีถึง 1,000 report คุณน่าจะมี Index File มากเกิน 100 ตัวแล้ว

แล้วจะ "จัดการ" Index อย่างไรดี ? 
- เปลี่ยนการเขียน Report ไปติดต่อด้วย SQL (ด้วย Web, RPG)
- RPG ใช้ Work File แทนการสร้าง Index File ตัวใหม่ 
- RPG ใช้ Array แทนการสร้าง Index File ตัวใหม่ 
- ...

วันนี้เราจะพูดหัวข้อนี้ครับ  ใช้ Work File 
เหมาะกับคนที่ยังไม่พร้อมจะเรียนรู้ SQL หรือ การแก้ไขโปรแกรมที่มีอยู่ในลักษณะแก้ไขเล็กน้อย
(ไม่รู้น้อยจริงหรือเปล่า)

หลายคนคงแย้ง ว่า การจัดการ Index มีหลายเรื่องต้องคำนึง ... ผู้เขียนไม่เถียงครับ
(แต่เรื่องมันจะยาว และอ่านยากกว่านี้)

(ทบทวน) Index File 

การสร้าง Index เป็นการให้  เครื่องฯ (DB Engine) ทำการเรียงลำดับข้อมูล "ทันที" 
และจัดเก็บไว้ (รอการเรียกใช้)  

ข้อดี   การ SQL Data ปริมาณมาก ที่ีมีการ Filter (Where) จะทำงานเร็วขึ้น

ข้อเสีย
...จำบทการเรียงลำดับข้อมูล ตอนเรียนได้มั๊ยครับ (มันเข้าใจยาก พอสมควร)
   มันจะทำงานทุกครั้งที่ Field ที่เป็น Index (เพิ่ม/แก้ไข/ลบ) = ทำงานช้า
...รอการเรียกใช้ = ถ้าไม่ใช้งานมัน  ก็ทำงานแบบหนักฟรีไป

ลองนึกภาพตามนี้  

งานบันทึกที่มี 5 Users ป้อนงานกัน ต่อเนื่องทั้งวัน
(Call Center, งานบันทึกผลการผลิต, การส่งออก, ...)
- การป้อนแต่ละ 1 ครั้งที่มีผลต่อ  5 Table  ที่แต่ละตัวมี Index 10 ตัว  
... แสดงว่า แต่ละCPU ต้องทำ Index 250 ครั้ง

การสร้างรายงานด้วย RPG ใน 20 ปีที่ผ่านมา
- โปรแกรมเมอร์ ต้องไปค้นหาว่า เคยมีคนสร้าง Index File ที่ตรงกับรายงานที่เราจะสร้างด้วย RPG หรือไม่ ?
- ถ้ามี  ก็เรียกใช้
- ถ้าไม่มี  ก็สร้าง index file "ใหม่"

การทำงานมันยุ่งยากจน  บางคนได้สร้าง Utility เอาไว้ค้นหา Index File กันเลยน๊ะครับ
(สร้างได้ไม่ยาก)

ข้อสังเกต

- Index File ที่สร้างในช่วงแรก ค่อนข้างจะเป็น Index File ที่มีการใช้งานบ่อย เช่น เรียงตาม Year,Month  เป็นต้น

- Index File ที่สร้างในช่วงหลัง  มักจะเป็นงาน "เฉพาะ" ตามไอเดียของผู้ใช้  น๊ะเวลานั้นๆ
(ยุคนี้ เรียกว่าการทำ Adhoc Query) 



ตย.เช่น  เรียงตาม  อายุงาน(พนักงาน),  จำนวนคงเหลือ, คำอธิบายสินค้า, ...
(เมื่อสังเกตดู) จะเห็นว่า  เป็น Index ที่มีความถี่ของการใช้งานน้อยมาก
(อาจจะใช้งาน แค่ตอนสร้าง เท่านั้น)


ที่บริษัทฯผมทำงาน  แต่ละระบบงาน จะสร้าง Index กับ Table หลักไว้เฉลี่ย 10 ตัว ต่อ 1 Table 

(มากที่สูงคือ 63 ตัว  ... อ๊าก!)

ถ้าบริษัทฯ ใดเริ่มเปลี่ยนการสร้างรายงาน มาใช้ SQL (RPG กับ SQL, Web กับ SQL)

ก็แทบจะ "ไม่จำเป็นต้องสร้าง Index File ใหม่" (การเรียงลำดับ เกิดขึ้นใน SQL:Order By)
...บริษัทใด ยังไม่มีแผนฯ ควรเริ่มได้แล้วครับ


Tip การจะเลือกยกเลิก Index ใน MS SQL Server จะมี Tool:Query Optimize เป็น Log 
     ที่ตรวจว่ามีใครใช้ Index อะไรในช่วงเวลานั้น  เมื่อเทียบกับ Index ที่คุณมีทั้งหมด
     ก็พอจะรู้ได้ว่า ตัวไหนใช้น้อย/ไม่ใช้งานเลย 

เมื่อรายงานเริ่ม "ช้า" 

โดยไม่มีเหตุผลที่เหมาะสม (ไม่น่าจะช้าที่ Spec เครื่อง, ไม่ใช่โปรแกรมทำงานผิดปรกติ)
ถ้ามีงบฯ มักจะ Upgrade เครื่องใหม่ (คิดง่ายดี)
ถ้าไม่มีงบฯ - ทีม Architecture มักจะมาตรวจปัญหาพบ  กับงานกลุ่ม Index เป็นกลุ่มแรกๆ ที่ทำให้เครื่องช้า
เป็นงานตรวจที่ใช้เวลานาน และตรวจเสร็จแล้วใช้เวลาแก้ไขนาน (จนไม่มีใครอยากจะแก้ไข)


Tip ใน DB2/400 จะรู้ได้อย่างไรว่า SQL ทำงานช้าที่จุดไหน (กรณี Join หลาย File) ต้องใช้
     iSeries Navigator : Visual Explain ดูผลการทำงานใน SQL ของเรา
     ใน Visual Explain จะบอกให้รู้ว่า DB2 ผ่านขั้นตอนอย่างไร และ เสียเวลาทำงานในแต่ละจุด
     นานแค่ไหน เมื่อรู้ว่าจุดไหน"ช้า" ก็แก้ปัญหาซะ เปลี่ยน SQL, สร้าง Index ใหม่ในจุดนั้น

ตย. การเพิ่มความเร็วของเครื่อง (อาจจะต้องทำหลายเรื่องพร้อมกัน)
- "ยกเลิก" Index บางตัว  แล้วปรับ RPG มาใช้  แนวทางการทำงานของ SQL 
Select *  from File1 where Y/M = 2013/05 Order by อายุงาน
- "สร้าง"  Index ที่จำเป็นจริงๆ  บางตัว "ต้อง" มี  แต่มันไม่มี  เช่น  รายงาน 50% ต้องเรียง/เลือกโดย Field:Customer  แต่ Index ที่สร้างไว้ "ไม่มี" Field นี้


(ทบทวน) การทำงานของ SQL 
Select *  from File1 where Y/M = 2013/05 Order by อายุงาน

#1 ถ้าข้อมูลมี 1,000,000 rows  Filter จะเลือกข้อมูล โดยใช้ Index (ที่ใช้บ่อย) ตย.นี้คือ Y/M จะได้ ข้อมูลที่สนใจ  มาเพียง  1,000 rows
#2 จากนั้น จึงเรียงลำดับข้อมูลดังกล่าว    
จากข้อมูล 1,000,000 rows เรียงข้อมูลจริงเพียง 1,000 rows เท่านั้น

การใช้ Work File

ตย. สร้าง Index File เช่น WRK010p

Field-Key [100]
Field-Data [200]
กำหนดให้เรียงลำดับ ตาม Field-Key
(ตย. นี้ใช้ Physical File ที่มี Key)

#1: อ่านข้อมูล + Filter โดยใช้ Index -> จัดเก็บ Data ไว้
Read File1
Loop 
Age = อายุงาน
        UsId = รหัสพนักงาน
กรณีที่ Data มีปริมาณน้อย  จัดเก็บ Data ลงอ Field-Data
WrData = UserName
กรณีที่ Data มีปริมาณมาก  จัดเก็บ Primary Key ไว้ (เรียกใช้ภายหลัง)
                UsId = รหัสพนักงาน
Write  WRK010p
Read File1
End Loop

อาจจะใช้ Data Structure ช่วยจัดการ Field

#2: อ่านจาก Data ที่เลือกไว้ (เรียง) มาแสดงผล
Read WRK010p
Loop
กรณีที่ Data มีปริมาณน้อย - นำ Field-Data มาแสดงผลเลย
กรณีที่ Data มีปริมาณมาก - ใน Primary Key ไปค้นหาข้อมูลหลัก
Field-Data (Primary Key) -> File1  (อ้างอิง Index)
Print  
Read WRK010p
End Loop

#0: เพิ่มเติม
กรณีที่ ต้องเรียกใช้หลายเครื่องฯ พร้อมกัน  เราจะใช้เทคนิค สร้าง Object ไว้ใน Lib: QTEMP
ดังนี้

    Call CL-100  
                 CRTDUPOBJ OBJ(WRK010)  FROMLIB(QGPL)                                   OBJTYPE(*FILE)  TOLIB(QTEMP)  
                 Call RPG-111

สรุป

เราไม่ต้องสร้าง Index File ใหม่  ก็ได้  ด้วยความเข้าใจและเลือกใช้วิธีที่เหมาะสมถ้าใครยังคงเขียน RPG (แบบไม่ใช้ SQL) ก็คงต้องใช้เทคนิคนี้ วิธีที่อธิบายถือว่าเป็นทางเลือก  

สำหัรบแต่คนที่กำลัง กำหนดแนวทาง ผมเสนอให้ทำความเข้าใจและเลือกในทางที่เหมาะสม
ส่วนใครที่ใช้ RPG ด้วย SQL หรือ Web (ด้วย SQL) การสร้าง Index บางกรณีจะช่วยให้ทำเร็ว
รวมทั้ง  ทำให้คุณเลือก "สร้าง Index" ที่เหมาะสมน๊ะครับ