sqltip

เหตุผลที่ การ run query ครั้งแรก อาจจะช้า แต่ครั้งที่ 2 เป็นต้นไป จะเร็วขึ้น เพราะอะไร? เพราะ sql server มีการจำรูปแบบ query นี้เก็บไว้ ทำให้การ query ครั้งที่ 2 ทำงานได้เร็วขึ้น หากเรา modify query แล้วจะทดสอบ performance ของ query จึงควรที่จะ clear สมองของ sql server ก่อน ด้วยการ run command DBCC FREEPROCCACHE to clear Execution plan DBCC DROPCLEANBUFFERS to clear SQL statement cache

การใช้ WHERE หรือ HAVING ควรกำหนดเงื่อนไขที่สามารถกรองข้อมูลได้มากที่สุดไว้ก่อนเสมอ

การใช้ OR ควรกำหนดเงื่อนไขที่ให้คำตอบเป็นจริงมากที่สุดไว้ในส่วนแรก เพราะเมื่อเจอ คำตอบที่เป็นจริง ก็จะไม่ตรวจสอบเงื่อนไขถัดไปแล้ว

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

ซ้ำกันมากๆ UNION กันไปก่อน เพื่อเพิ่มความรวดเร็วในการประมวลผล

สร้าง Index (ใน database) ที่มี date เป็นส่วนประกอบให้ใช้ date นำหน้า เช่น reftype, refdate, refno ควรเปลี่ยนเป็น refdate,reftype,refno เนื่องจาก refdate มีการกระจายของข้อมูลมากกว่า reftypeหมายเหตุ : เมื่อ SQL พบ where refdate = ? จะทำให้กรองข้อมูลให้เหลือน้อยกว่า (เช่น ถ้ามี 50 วัน ก็จะทำให้เหลือข้อมูล เพียง 1/50 ซึ่งเมื่อมี cause ที่สองจำนวน record ที่ต้องผ่านจะลดลงมาก) ใน ขณะที่ where reftype = ‘BU’ (ถ้า reftype = ‘BU’,’SE’ จะทำให้เหลือข้อมูลถึง ฝ)

ใน where cause ให้ check เงื่อนไขของแต่ละ Table ก่อน แล้วจึงเช็คเงื่อนไขในการ join กัน เช่น where a.*** = ? and  a.yyy = ? and  b.zzz = ? and  a.aaa = b.aaa  ดีกว่า where a.aaa = b.aaa and  a.*** = ? and b.zzz = ? and a.yyy = ?  หมาย เหตุ : ตามทฤษฏีของ Database ลำดับของ where cause ไม่ควรมีความสำคัญต่อ Performance แต่ในความเป็นจริง การ เรียงลำดับให้ เงื่อนไขในการหา อยู่เรียงตามลำดับของ Table ของเงื่อนไขนั้นๆ และมาก่อนเงื่อนไขในการ join ระหว่าง table ทำให้ performance เร็วขึ้นมาก

join table ให้ table ขนาดเล็ก (ที่ผ่าน where cause แล้ว) ขึ้นก่อน เช่น ต้องการ join JTR กับ MCSD ให้ใช้ from jtr a,mcsd b (JTR ที่ผ่าน where cause แล้วมีขนาดเล็กกว่า MCSD ที่ผ่าน where cause) หมายเหตุ : ตามทฤษฏี Database ควรจะทราบว่า หา Table ใดก่อน จะ Optimize ที่สุด แต่ในความเป็นจริง Informix จะใช้ Table แรกเป็น major table ในการ scan ผ่าน Table ที่สอง ผลก็คือ การที่ JTR มีจำนวน row น้อยกว่า mcsd ทำให้ใช้เวลาน้อยกว่าในการหา condition ใน where cause พบ 

 หลีกเลี่ยงการให้ not in ใน where cause ถ้าสามารถระบุได้ว่าข้อมูลที่ต้องการคืออะไร เช่น custtype มี 1,2,3,4,5 ถ้า Report นี้ไม่ต้องการพิมพ์ข้อมูลของลูกค้า Foreign และ Port ให้ใช้ where custtype in (‘1’,’3’,’4’) ไม่ควรใช้ where custtype not in (3,5) หมาย เหตุ : not in ทำให้ optimizer ไม่สามารถทำงานได้ (ใช้ index ไม่ได้) ถ้าทราบว่า custtype มีค่าเป็นอะไรได้บ้าง การใช้ in จะดีกว่ามาก

3. ใช้ EXISTS เร็วกว่า IN แน่นอน      ยกตัวอย่างอันนี้ผมใช้ใน Oracle นะครับ     select f1,f2,f3 from my_table where     exists (select 1 from dual where f1=1 or f1=2 or f1=3)      เร็วกว่า     select f1,f2,f3 from my_table where     f1 in (1,2,3)     ฟันธง

  select f1,f2,f3  from my_table where           (f1,f2) = (select max(f1),max(f2) from my_table)        เร็วกว่า      select f1,f2,f3  from my_table where           f1 = (select max(f1) from my_table)           and f2 = (select max(f2) from my_table)      ฟันธง 

1. Query ด้วยการระบุ field ให้ชัดเจนเร็วกว่าแน่นอน      แม้จะไม่ค่อยเห็นผลใน 1 query     แต่คิดดูถ้าคุณ query ซัก สิบล้านรอบมันจะมีผลแค่ใหน หุหุ      select  f1,f2,f3  from my_table      เร็วกว่า      select * from my_table     ฟันธง

where substring(firstname,1,1) = 'm' เราควรที่จะเปลี่ยนเป็น where firstname like 'm%' เพราะว่า Substring เป็น Function ทำให้รันได้ช้ากว่า อาจจะช่วยได้อย่างละนิด อย่างละหน่อยนะครับ

Truncate จะมีการลบข้อมูลที่เร็วกว่า Delete  ในกรณีที่ ต้องการลบข้อมูลทั้งหมด ใน Table

วันนี้เรามาดูว่า UNION กับ UNION ALL ต่างกันอย่างไร แล้วแบบไหนเร็วกว่ากัน UNION นั้น เป็นการ Join กันระหว่าง 2 Table ที่มี Columns เหมือนกันทั้งหมด และจะมีการหาข้อมูลซ้ำ เอามาโชว์แค่ตัวเดียว  UNION ALL นั้น ก็เป็นการ Join เหมือนกับ UNION แต่จะไม่มีการหาข้อมูลซ้ำ ซึ่งก็จะทำให้การ Join กันเร็วขึ้น ในกรณีที่เราไม่สนใจว่าข้อมูลเราจะซ้ำหรือป่าว ผลลัพท์ที่ออกมาก็จะออกมาทั้งหมดจิง ๆ ลองเอาไปประยุกต์ใช้ดูได้นะครับบบบบบ

ในกรณีที่เรารู้ข้อมูลแน่นอน และมีการเรียงของข้อมูล เช่น  SELECT CUSTOMER_NUMBER,CUSTOMER_NAME FROM CUSTOMER WHERE CUSTOMER_NUMBER IN (1000,1001,1002) กับ  SELECT CUSTOMER_NUMBER,CUSTOMER_NAME FROM CUSTOMER WHERE CUSTOMER_NUMBER BETWEEN 1000 AND 1002 แบบใช้ BETWEEN จะมีการ Query ได้เร็วกว่า

วันนี้เรามาดูเทคนิคเพิ่มเติมระหว่าง OR กับ UNION ALL กัน ในกรณีที่เรา ใช้ OR ในส่วนของเงื่อนไข เยอะ ๆ จะทำให้ Query ช้าลงมาก ๆ เช่น SELECT EMPLOYEEID,FIRSTNAME, LASTNAME FROM EMPLOYEE WHER DEPT = 'IT' OR CITY = 'UDONTHANI' OR DIVISION = 'SUPPORT' เราสามารถใช้การ UNION ALL เพื่อเพิ่มความเร็วในการ Query ได้ SELECT EMPLOYEEID,FIRSTNAME, LASTNAME FROM EMPLOYEE WHER DEPT = 'IT' UNION ALL SELECT EMPLOYEEID,FIRSTNAME, LASTNAME FROM EMPLOYEE WHER CITY = 'UDONTHANI' UNION ALL SELECT EMPLOYEEID,FIRSTNAME, LASTNAME FROM EMPLOYEE WHER DIVISION = 'SUPPORT' อย่างนี้ จะเพิ่มประสิทธิภาพในการ Query ได้นะครับ หวังว่าคงช่วยได้ไม่มากก็น้อยนะครับ