COOL IT IDEAS AND STUFF

See the original english version Click here!

 

ในองค์กรที่ทำงานโดยไม่มี Data Warehouse หรือ Analytical Database สำหรับ Report เป็นของตัวเอง แหล่งข้อมูลที่อัปเดตล่าสุดอาจอยู่ใน Live Production Database เมื่อทำการ Query ตัว Production Database สิ่งสำคัญก็คือการ Optimize ซึ่งการ Query ที่ไม่มีประสิทธิภาพอาจทำให้เกิดภาระกับ Resource ของ Production Database ได้และทำให้ Performance ช้าลง หรือสูญเสีย Service สำหรับ User รายอื่นหาก Query มีข้อผิดพลาด ดังนั้นมาดู 8 แนวทางการปรับแต่ง SQL Queries ใน Production Databases ของคุณ เพื่อให้ส่งผลกระทบต่อ Performance ของ Database ให้น้อยที่สุด

1. กำหนด Business Requirements ก่อนที่จะเริ่มต้น

การมี Business Requirements ที่ชัดเจนเป็นเรื่องสำคัญ หาก Business Requirements ไม่ชัดเจนหรือกำกวม อาจทำให้วิธีการและผลลัพธ์ที่ออกมาผิดพลาดได้ นอกจากนี้ยังมีประเด็นสำคัญ ๆ ที่ควรนำไปพิจารณาเมื่อมีการ Optimize SQL Queries ด้วยซึ่งประกอบด้วย:

• ระบุผู้ที่เกี่ยวข้องทั้งหมดให้ชัดเจนว่ามีใครบ้าง – ตรวจสอบให้แน่ชัดว่า ทุกฝ่ายที่เกี่ยวข้องได้อยู่ในการปรึกษาหารือร่วมกันในเรื่องการ Develop Query ของคุณ เมื่อจะทำการ Query Production Database ก็ควรให้ทีม DBA เข้าร่วมด้วย
• มุ่งเน้นไปที่ผลลัพธ์ทางธุรกิจ –  ต้องแน่ใจว่า Query นั้นมีวัตถุประสงค์ที่ชัดเจนและไม่ซ้ำซ้อนกัน การใช้ Production Database เพื่อการสืบค้นข้อมูลหรือดึง Report ที่ซ้ำซ้อนกัน อาจเป็นการสร้างความเสี่ยงโดยไม่จำเป็น
• กำหนดกรอบของการหารือร่วมกันเพื่อให้ได้ Requirement ที่ดี – กำหนดหน้าที่และขอบเขตของ Report โดยระบุกลุ่มเป้าหมายที่ต้องการ ซึ่งสิ่งนี้จะ Focus ที่การ Query บน Table ด้วยระดับความถูกต้องของรายละเอียดต่าง ๆ
• Develop Requirements ให้ดีขึ้นโดยการถามคำถามที่ดี – คำถามเหล่านั้นมักเป็นไปตามหลักของ 5W คือ Who? What? Where? When? Why?
• เขียน Requirements ให้เฉพาะเจาะจงที่สุดแล้วทำการยืนยันกับผู้ที่เกี่ยวข้องทั้งหมด – Performance ของ Production Database มีความสำคัญมาก จึงควรตรวจสอบเพื่อให้ Requirements มีความเฉพาะเจาะจงที่สุดเท่าที่จะเป็นไปได้ และยืนยัน Requirements เหล่านั้นกับผู้ที่เกี่ยวข้องทั้งหมดก่อนที่จะ Run Query

2. ระบุ Fields ที่จะทำการ SELECT แทนที่จะใช้ SELECT *

เมื่อ Run Queries เพื่อดูข้อมูล น่าจะมี Developer หลายคนที่ใช้ SELECT * (อ่านว่า “Select All”) เพื่อ Query ข้อมูลที่มีอยู่ทั้งหมดจาก Table อย่างไรก็ตามหาก Table มีหลาย Fields และหลาย Rows สิ่งนี้จะใช้ Resources ของ Database ไปเป็นจำนวนมากในการ Query ข้อมูลที่ไม่จำเป็น

การระบุ Fields ในคำสั่ง SELECT จะชี้ให้ Database ทำการ Query เฉพาะข้อมูลที่จำเป็นเพื่อให้ตรงกับ Business Requirements

เราลองมาดูตัวอย่างของ Business Requirements โดย หากเราต้องการที่อยู่ของลูกค้า 

แบบไม่มีประสิทธิภาพ:

Query นี้อาจดึงข้อมูลอื่น ๆ ที่เก็บไว้ใน Table “Customers” ออกมา เช่น Phone Numbers, Activity Dates และบันทึกจากฝ่าย Sales และฝ่าย Customer Service ออกมาด้วย 

แบบมีประสิทธิภาพ:

Query นี้จะดึงแค่ข้อมูลที่จำเป็น ซึ่งคือส่วนที่เป็น ที่อยู่ ออกมาเท่านั้น

หากต้องการเก็บ Index ของ Table และชื่อ Fields ทั้งหมด ก็ให้ Run Query จาก Table “System” เช่น INFORMATION_SCHEMA หรือ ALL_TAB_COLUMNS (สำหรับ MS SQL Server - สามารถดูข้อมูลเพิ่มเติมได้ที่นี่)

3. SELECT หลาย ๆ Fields เพื่อเลี่ยงการใช้ SELECT DISTINCT

SELECT DISTINCT เป็นวิธีที่สะดวกในการ Remove รายการที่ซ้ำกัน (Duplicates) ออกจาก Query โดย SELECT DISTINCT จะทำการจัดกลุ่ม (Grouping) Fields ทั้งหมดใน Query เพื่อสร้างผลลัพธ์ที่ไม่ซ้ำกัน อย่างไรก็ตามในการทำสิ่งนี้ จำเป็นต้องใช้การประมวลผลเป็นจำนวนมาก นอกจากนี้ข้อมูลอาจถูก Group จนไปถึงจุดที่ได้ข้อมูลที่ไม่ถูกต้อง ดังนั้นเพื่อหลีกเลี่ยงการใช้ SELECT DISTINCT ควรเลือกหลาย ๆ Fields เพื่อสร้างผลลัพธ์ที่ไม่ซ้ำกัน 

แบบไม่มีประสิทธิภาพและไม่ถูกต้อง: 

Query นี้ไม่ได้อธิบายอย่างชัดเจนเกี่ยวกับ คนที่อยู่ในรัฐ (State) เดียวกันโดยที่มีชื่อและนามสกุลเหมือนกัน ชื่อยอดนิยม อย่างเช่น David Smith หรือ Diane Johnson อาจจะถูก Group เข้าด้วยกัน ทำให้ได้จำนวน Records ที่ไม่ถูกต้อง สำหรับ Database ที่มีขนาดใหญ่ การที่มี David Smith และ Diane Johnson อยู่เป็นจำนวนมาก จะทำให้การ Query นี้ทำงานช้าลง 

แบบมีประสิทธิภาพและถูกต้อง:

จากการที่เพิ่ม Fields เข้าไป Records ที่ไม่ซ้ำกัน จะถูก Return โดยที่ไม่ต้องใช้ SELECT DISTINCT เลย นอกจาก Database จะไม่ต้องทำการ Group Fields ใด ๆ แล้ว จำนวนของ Records ที่ได้มาก็มีความถูกต้องอีกด้วย

4. ใช้ INNER JOINS แทนที่จะใช้ WHERE

SQL Developer บางคน ต้องการที่จะทำการ Join โดยใช้ WHERE Clause ดังตัวอย่างต่อไปนี้: 

การ Join แบบนี้จะสร้าง Cartesian Join ซึ่งบางครั้งเรียกว่า “Cartesian Product หรือ CROSS JOIN” โดย Cartesian Join เป็น Combination ที่เป็นไปได้ทั้งหมดของตัวแปรที่จะถูกสร้างขึ้น อย่างในตัวอย่างนี้ หากเรามีลูกค้า 1,000 รายที่มียอดขาย 1,000 รายการ ทำให้ Query นี้จะมีการสร้างผลลัพธ์ถึง 1,000,000 รายการก่อน จากนั้นถึงทำการ Filter 1,000 Records ที่ CustomerID ถูก Join อย่างถูกต้อง นี่ถือเป็นการใช้ Resources ของ Database อย่างไม่มีประสิทธิภาพ เนื่องจาก Database ทำงานมากกว่าที่ควรจะเป็นถึง 100 เท่า สำหรับ Cartesian Join จะสร้างปัญหาโดยเฉพาะอย่างยิ่งใน Database ที่มีขนาดใหญ่ เนื่องจาก Cartesian Join ของ 2 Table ที่มีขนาดใหญ่ สามารถสร้างผลลัพธ์ได้เป็นพันล้าน หรือ ล้านล้าน รายการเลยทีเดียว

ดังนั้น เพื่อป้องกันการสร้าง Cartesian Join เราควรใช้ INNER JOIN แทน: 

Database จะสร้าง Records ที่ต้องการจำนวน 1,000 Records จาก CustomerID ที่ตรงกัน

บาง DBMS Systems ก็รู้ได้ว่ามีการ Join กันโดยใช้ WHERE ซึ่งมันจะเปลี่ยนเป็น Run โดยใช้ INNER JOIN แทนโดยอัตโนมัติ ใน DBMS Systems เหล่านั้นจะไม่มีความแตกต่างด้าน Performance ระหว่างการ Join โดยใช้ WHERE และ INNER JOIN แต่อย่างไรก็ตาม INNER JOIN ก็ได้รับการยอมรับจาก DBMS Systems ทั้งหมด ซึ่ง DBA จะสามารถแนะนำให้คุณทราบว่า สิ่งใดดีที่สุดใน Environment ของคุณ

5. ใช้ WHERE แทนที่จะใช้ HAVING ในการ Filters

เช่นเดียวกับแนวคิดที่กล่าวไปข้างต้น เป้าหมายของการ Query ที่มีประสิทธิภาพ ก็คือ การดึงเฉพาะ Records ที่ต้องการจาก Database หากว่ากันตามลำดับการ Operations ของ SQL แล้ว HAVING Statements จะถูกคำนวณหลัง WHERE Statements หากเจตนาก็คือ การ Filter Query ตามเงื่อนไขแล้ว การใช้ WHERE statement ค่อนข้างจะมีประสิทธิภาพมากกว่า 

สมมติว่า มียอดขายจำนวน 200 รายการในปี 2016 และเราต้องการ Query จำนวนของยอดขายต่อลูกค้า ในปี 2016

Query นี้จะดึงยอดขายจำนวน 1,000 Records จาก Table “Sales” จากนั้นก็ Filter 200 Records ที่ถูกสร้างในปี 2016 และสุดท้ายก็นับ Records ใน Data Set

เมื่อเปรียบเทียบแล้ว WHERE Statements จะสามารถจำกัดจำนวนของ Records ที่ถูกดึง:

Query นี้จะดึงจำนวน 200 Records ที่ถูกสร้างในปี 2016 และจากนั้นก็นับ Records ใน Data Set โดยขั้นตอนแรกใน HAVING Clause ได้ถูกกำจัดออกไปแล้ว 

ควรใช้ HAVING เมื่อต้องการ Filter ใน Field ที่ถูกคิดเป็นยอดรวมแล้วเท่านั้น อย่างใน Query ด้านบน เราสามารถเพิ่ม Filter เข้าไป สำหรับลูกค้าที่มียอดขายมากกว่า 5 รายการ โดยใช้คำสั่ง HAVING Statement ดังตัวอย่างด้านล่างนี้

6. ควรใช้ Wildcards เฉพาะเมื่อสิ้นสุด Phrase เท่านั้น

เมื่อค้นหาข้อมูลที่เป็น Plaintext ธรรมดา อย่างเช่น Cities หรือ Names เป็นต้น การใช้ Wildcards (หมายถึง สัญลักษณ์/อักขระที่ใช้แทนตัวอักษรใด ๆ ก็ได้ในการค้นหาคำหรือข้อความ) จะช่วยสร้างการค้นหาที่กว้างที่สุดเท่าที่จะเป็นไปได้ แต่อย่างไรก็ตาม การค้นหาที่กว้างที่สุด ถือเป็นการค้นหาที่ไม่มีประสิทธิภาพมากที่สุดเช่นกัน

เมื่อใช้ Wildcards นำ โดยเฉพาะอย่างยิ่งเมื่อรวมกับ Wildcards ที่สิ้นสุดแล้ว Database จะถูกมอบหมายให้ค้นหา Records ทั้งหมดสำหรับการ Match กับตรงไหนก็ได้ภายใน Field ที่ถูกเลือก

ลองดูที่ Query นี้ ซึ่งเราต้องการดึง Cities ที่ขึ้นต้นด้วยคำว่า "Char":

Query นี้จะดึงผลลัพธ์ตามที่เราคาดหวังไว้ออกมา คือ Charleston, Charlotte และ Charlton แต่อย่างไรก็ตาม มันก็จะดึงผลลัพธ์ที่เราไม่ต้องการออกมาด้วย เช่น Cape Charles, Crab Orchard และ Richardson 

ซึ่ง Query ที่มีประสิทธิภาพและตรงความต้องการมากกว่า ควรจะเป็นดังนี้:

Query นี้จะดึงเฉพาะผลลัพธ์ที่เราคาดหวังไว้ออกมา คือ Charleston, Charlotte และ Charlton เท่านั้น

7. ใช้ LIMIT เพื่อสุ่มตัวอย่างของผลลัพธ์

ก่อนที่จะ Run Query ครั้งแรก คุณควรแน่ใจว่า ผลลัพธ์จะเป็นไปตามที่ต้องการและถูกต้อง โดยใช้ LIMIT Statement (ในบาง DBMS System อาจใช้คำว่า TOP แทน LIMIT) ซึ่ง LIMIT Statement จะ Return Records ตามจำนวนที่ระบุไว้เท่านั้น การใช้ LIMIT Statement จะช่วยป้องกันการใช้ Production Database ที่มีการ Query ข้อมูลที่มีปริมาณมาก ๆ รวมทั้งเพื่อดูว่า Query นั้น จำเป็นต้องได้รับการแก้ไขหรือปรับให้ดีขึ้นหรือไม่

จากตัวอย่างการ Query ยอดขายในปี 2016 (ในข้อ 5) เราจะทำการตรวจสอบข้อมูลโดยจำกัดไว้แค่ 10 Records เท่านั้น:

เราสามารถดูได้จากตัวอย่างข้อมูลว่า เรามี Data Set ที่สามารถนำไปใช้ประโยชน์ได้หรือไม่

8. Run Analytical Queries ในระหว่างที่มีการใช้งานน้อย

เพื่อลดผลกระทบของ Query ที่มีต่อ Production Database ให้มากที่สุด คุณควรคุยกับ DBA เรื่องการตั้ง Schedule การ Query เพื่อให้มันทำงานในช่วงเวลาที่มีการใช้งานน้อย การ Query ควร Run เมื่อ Users ที่เข้าใช้งานพร้อมกันมีจำนวนน้อยที่สุด ซึ่งโดยปกติจะเป็นตอนกลางดึก (ช่วง 3 – 5 นาฬิกา)

หากคุณจะ Run Query โดยมี Criteria ดังต่อไปนี้ สมควรเป็นอย่างยิ่งที่จะทำในช่วงกลางคืน:

• การ SELECT ข้อมูลจาก Table ที่มีขนาดใหญ่ (>1,000,000 Records)
• Cartesian Joins หรือ CROSS JOINs
• Looping Statements
• SELECT DISTINCT Statements
• Nested Subqueries
• ใช้ Wildcard ค้นหาใน Field ของ Text หรือ Memo ที่ยาว ๆ
• Multiple Schema Queries

หวังว่าคุณจะลองนำ 8 เทคนิคเหล่านี้ ไปลองปรับใช้กับ Production Database ของคุณ เพื่อให้มี Performance ที่ดียิ่งขึ้น

ที่มา:  https://www.sisense.com/