Sql Query Optimizer เพื่อเพื่มความเร็วในการสืบค้นข้อมูล

เกี่ยวกับ ปัญหาการใช้งาน การติดตั้ง ฐานข้อมูล MySql Oracle MSSQL ect...
การเขียน คำสั่ง SQL เพื่อดึกข้อมูล บอร์ดนี้ควรระบุโครงสร้างตารางของท่านในคำถามด้วยนะ

Moderator: mindphp

User avatar
ecitepage.com
PHP Sr. Member
PHP Sr. Member
Posts: 52
Joined: 09/10/2010 2:54 pm
Contact:

Sql Query Optimizer เพื่อเพื่มความเร็วในการสืบค้นข้อมูล

Post by ecitepage.com »

Sql Query Optimizer เพื่อเพื่มความเร็วในการสืบค้นข้อมูล
บอกไว้ก่อนว่า ไม่ได้เขียนเองนะ ได้มาจาก 1.บางข้อได้ยินคนเค้าคุยกัน เถียงกันเวลาทำงาน 2. ไปเอามาจากเว็บไซต์หนึ่ง ซึ่งจำ url ไม่ได้
ปล. ใครว่างช่วยทดลองกันหน่อย ว่าที่เค้าพูดกันมันจริงมั๊ย แต่เราขอ confirm ข้อ 1,2,8 ว่าใช้ได้จริง

1 ใช้ in / union แทน or ใน where cause

เช่น where custacct = ‘1’ or custacct = ‘2’ ให้เปลี่ยนเป็น where custacct in (‘1’,’2’)
หมาย เหตุ : การใช้ in โปรแกรมจะทราบว่า argument ตัวที่จะ compare คืออะไร (ในกรณี or อาจมี argument ได้หลาย argument เช่น custacct = ‘1’ or acount = ‘01-20001-0’) ทำให้ Optimizer สามารถทำงานได้อย่างมีประสิทธิภาพ

2 หลีกเลี่ยงการให้ 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 หลีกเลี่ยงการใช้ Match , like ใน where cause

หมายเหตุ : ถ้าใช้ Match / Like จะทำให้ SQL ใช้ Sequential Scan (ไม่สามารถใช้ Index Scan ได้)

4 select field เฉพาะที่ใช้งานกรณีที่ใช้ข้อมูลไม่กี่ field (ลดการใช้ select * นั่นเอง) ใช้ select * กรณีที่ Table นั้นมี filed มากกว่า 30 field

หมายเหตุ : จะพบว่า performance Drop ได้ชัดเจน (เมื่อจำนวนฟิลด์เพิ่มขึ้น) ในการเรียกผ่าน Delphi เช่น หน้าจอ Customer Information

5 หลีกเลี่ยงการใช้ Sub - Query เช่น select sharecode from tst
where shareid in (selest shareid from tmst where xchgmkt = ‘1’)
หมายเหตุ : มีอีกทฤษฏีบอกว่า ถ้า table ใน sub - query มีจำนวน row ไม่มาก Performance จะไม่ต่างกัน

6 ใช้ temptable เมื่อต้องอ่านข้อมูลซ้ำๆ จาก Table ควรสร้าง Index และเลือกเฉพาะข้อมูลที่ใช้มาสร้างเท่านั้น

หมาย เหตุ : เพราะจะเสียเวลาตอนอ่าน Temp Table (เพื่อให้มีข้อมูลจำนวนน้อย) และสร้าง Index ตามต้องการครั้งแรกครั้งเดียว (ใน Table จริงอาจไม่มี Index ตาม where clause ที่ใช้ก็ได้)

7 หลีกเลี่ยงการใช้ operation / function call ใน where cause หรือ loop

ไม่ควรใช้ where cause ดังต่อไปนี้
where account[1,1] = 1’
where duedate = bankdate(?,3)
where unit*price*.50 > ?
หมายเหตุ : เพราะการที่จะทราบว่า record ใด satisfy เงื่อนไขนี้หรือไม่ จะต้อง call function ทำการคำนวณทุกครั้ง

8 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 พบ

9 ใน 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 เร็วขึ้นมาก


10 สร้าง 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’ จะทำให้เหลือข้อมูลถึง ฝ)

11 หลีกเลี่ยง prepare นอกจากมีการเรียกใช้ select ดังกล่าวใน loop หรือมีเงื่อนไขแบบ dynamic

หมาย เหตุ : การ prepare เป็นการสร้าง cursor สำหรับ select statement ซึ่งต้องใช้เวลาในการสร้าง cursor ถ้าไม่ได้ใช้ cursor นั้นบ่อยๆ (มากกว่า 2-3 ครั้งขึ้นไป) จะทำให้ overhead ในการ prepare มากกว่าการ select โดยตรง

12 หลีกเลี่ยงการใช้ count(*) ในการเช็คว่ามีข้อมูลหรือไม่ ให้ใช้ fetch ฟิลด์ใดฟิลด์หนึ่งแทน (โดยไม่ต้องมี where cause) และเช็คว่ามีข้อมูลหรือไม่จาก sqlca.sqlcode

หมายเหตุ : สำหรับ select count(*) SQL จะต้องนับจำนวนทุก record ใน Table ซึ่งเสียเวลา ส่วน การ fetch คือหา record พบรายการใดรายการหนึ่งก็หยุดการทำงานแล้ว

13 ใช้ standard SQL (Sub Routine ที่ตัดปะไว้ในโปรแกรม) แทน library เช่นการหาชื่อภาษาไทย/อังกฤษของ Report

ถ้าใน Report มีการใช้ข้อมูลจาก table TCT (ไฟล์ข้อมูลลูกค้า) อยู่แล้ว
หมาย เหตุ : ใน Library จะทำการ select ใหม่ทุกครั้ง ; แต่ Standard SQL ที่เขียนเป็น sub routine ในโปรแกรมจะมีการเลือกลง Temp Table ให้มีเฉพาะลูกค้าที่ต้องการ และมีการสร้าง index ที่สอดคล้องกับโปรแกรมมากกว่า Library ที่เป็น standard สำหรับทุก report

14 เลือก table ที่ถูกต้อง

หมาย เหตุ : บาง Table อาจมีข้อมูลที่ไม่ครบถ้วนตามที่เราต้องการ ทำให้ต้อง union กับ Table อื่นหลาย Table เพื่อให้ได้ข้อมูลที่ต้องการ ; ถ้าเราทราบว่าข้อมูลที่เราต้องการอยู่ที่ Table ใดบ้าง จะช่วยให้ต้องทำการ join ลดลง และใช้ index ที่ถูกต้อง
ที่มา: http://board.cpenu.com
ใครลองแล้วได้ผลข้อไหน ช่วย confirm หน่อยนะ :-D :-D
ประกาศซื้อขาย การดูแล บ้านคอนโด
http://www.ecitepage.com
  • Similar Topics
    Replies
    Views
    Last post

Return to “SQL - Database”

Who is online

Users browsing this forum: No registered users and 4 guests