การใช้งานฐานข้อมูล SQL Server ให้เกิดประสิทธิภาพ (performance) สูงสุดนั้น มีปัจจัยอยู่หลายอย่าง เนื้อหาในบทความนี้ เราจะมาเผยเทคนิค วิธีการออกแบบฐานข้อมูล ตาราง และหลักในการพิจารณาสร้าง index รวมไปจนถึงการเขียนคำสั่ง Transact-SQL และ Stored procedure เพื่อให้ SQL Server สามารถทำงานร่วมกับระบบหรือโปรแกรมที่เราพัฒนาขึ้นได้อย่างมีประสิทธิภาพ ประมวลผลเพื่อส่งต่อข้อมูลกันได้อย่างรวดเร็ว
เทคนิคและวิธีการออกแบบตาราง และสร้าง index
- ทุกตารางที่ได้รับการออกแบบควรสร้าง clustered index ไว้หนึ่งตัวเสมอ คอลัมน์ที่ใช้ในการสร้าง clustered index ควรเป็นคอลัมน์ identity หรือคอลัมน์ใดๆ ที่มีค่าไม่ซ้ำกัน (unique) โดยปกติแล้วการสร้าง primary key ก็เป็นส่วนหนึ่งที่ทำให้เกิด clustered index อัตโนมัติอยู่แล้ว
- การพิจารณาว่าควรสร้าง index อะไรบ้าง และจำนวนเท่าใดนั้น ให้พิจารณาว่ามีกลุ่มของคอลัมน์ใดบ้างที่ใช้ค้นหาหรือเปรียบเทียบบ่อยๆ ภายใต้ส่วนของคำสั่ง where, join...on, order by, group by, top และ distinct เป็นต้น
- แม้ index จะช่วยเพิ่มประสิทธิภาพในการค้นหาข้อมูล แต่หากมีมากจนเกินไปในแต่ละตาราง ก็จะส่งผลให้การเพิ่มหรือปรับปรุงข้อมูลจะมีการประมวลผลมากตามไปด้วย
- ไม่ควรสร้าง index ที่เหมือนกัน แม้ว่าชื่อ index จะไม่เหมือนกัน แต่ไม่มีความจำเป็น เป็นภาระในการประมวลผลโดยเปล่าประโยชน์
- รายละเอียดเงื่อนไขของ index ที่ไม่ควรสร้าง (ถ้ามีอยู่ให้ลบทิ้ง)
- index ที่ไม่ได้ใช้งาน
- ข้อมูลในตารางมีไม่มาก ขนาดของตารางเล็ก
- index ที่ใช้จำนวนคอลัมน์มากเกินไป เทียบเคียงกับจำนวนคอลัมน์ทั้งหมดในตาราง
- index ที่มีการใช้คอลัมน์ข้อมูลประเภท TEXT, NTEXT หรือ IMAGE
- ตารางที่ใช้งานในค้นหาข้อมูล (query) น้อยมาก แต่ปรับปรุงข้อมูล (insert / update / delete) บ่อยๆ
- การสร้าง index เป็นไปได้ให้พยายามสร้าง unique index หรือให้เข้าใจอีกแง่หนึ่งคือ index ที่มีข้อมูลในแต่ละคอลัมน์ใน index ไม่ซ้ำกัน การค้นหาจะเร็วกว่า non-unique index
- index ที่ใช้ไม่บ่อย หากมีการสร้างไว้จะส่งผลกับการ perfomance ของการปรับปรุง (insert / update / delete) ข้อมูล แนะนำให้สร้างในขั้นตอนการค้นหาข้อมูล (query) และเมื่อสิ้นสุดการค้นหาข้อมูล ค่อยลบ (drop) index ออกไป
- หลีกเลี่ยงการใช้คอลัมน์ที่เป็น FLOAT หรือ REAL เป็น primary key
- หลีกเลี่ยงประเภทข้อมูล TEXT หากความยาวของข้อมูลไม่เกิน 8,000 ตัวอักษร โดยใช้ VARCHAR แทน TEXT จะเสีย performance สูงมาก
- หลีกเลี่ยงประเภทข้อมูล NVARCHAR หรือ NCHAR หากไม่จำเป็นต้องเก็บข้อมูลที่เป็น Unicode หรือ 16-bit เพราะต้องใช้เนื้อที่มากกว่า VARCHAR และ CHAR เป็น 2 เท่า
- หากข้อมูลที่จัดเก็บมีความยาวไม่เท่ากัน ให้ใช้ VARCHAR แทนที่ CHAR จะช่วยลดเนื้อที่ในการจัดเก็บในส่วนที่ไม่ได้ใช้
- หากข้อมูลที่จัดเก็บมีความยาวเท่ากัน ให้ใช้ CHAR แทนที่ VARCHAR เพราะการประมวลผลของคอลัมน์ที่ fixed length จะเร็วกว่า
- หากข้อมูลที่จัดเก็บเป็นตัวเลขเท่านั้น ให้ใช้ประเภทข้อมูลแบบ numeric เช่น INTEGER เนื่องจากใช้พื้นที่น้อยกว่า VARCHAR และ CHAR และ perfomance ในการค้นหาในส่วนของเงื่อนไข (WHERE clause), joined, sorted ทำได้ไวกว่า
- หลีกเลี่ยงการใช้ประเภทข้อมูลที่เกินความจำเป็น ยกตัวอย่างเช่น เราจะใช้ BIGINT แต่หากดูแล้วไม่จำเป็น และเห็นว่าแค่ INT ก็เพียงพอแล้ว ให้เลือกใช้ INT เนื่องจาก BIGINT ใช้เนื้อที่ต่อข้อมูล 8 bytes มากกว่า INT ซึ่งใช้เพียง 4 bytes
- ไม่ใช้ DATETIME หรือประเภทข้อมูลที่ใช้พื้นที่มากๆ เป็น primary key (จำนวนเนื้อที่ที่ใช้จัดเก็บข้อมูล หรือใช้ในสร้าง index ยิ่งใช้มากก็จะยิ่งเสีย performance ในการเข้าถึงข้อมูลมากกว่า โดยเฉพาะ primary key ซึ่งใช้สำหรับเข้าถึงข้อมูลอยู่บ่อยๆ)
เทคนิคและวิธีการเขียน Transact-SQL และใช้งาน Stored procedure
- เริ่มต้นด้วยการใช้คำสั่ง SET NOCOUNT ON ทุกครั้ง ภายใน stored procedure
- หากมีการใช้ transaction ควบคุมการทำงานใน stored procedure พยายามเขียนขั้นตอนภายใน transaction ให้น้อยที่สุด เพื่อช่วยลดจำนวนการล็อกตาราง และความรวดเร็วในการประมวลผล
- ไม่ควรกำหนดชื่อ stored procedure ในฐานข้อมูลที่ไม่ใช่ Master โดยขึ้นต้นด้วย "sp_" ซึ่งเป็นคำนำหน้าพิเศษสำหรับ stored procedure ของระบบ เพราะอาจส่งผลให้การประมวลผลช้าลงได้
- ก่อนนำ stored procedure ไปใช้งาน ให้ตรวจสอบสิ่งที่ไม่ได้ใช้ออก เช่นอาจจะมีการกำหนดตัวแปร หรือพารามิเตอร์ที่ใช้ทดสอบ แต่ไม่ได้ใช้จริงในการประมวลผล ควรลบสิ่งเหล่านี้ออกจาก stored procedure
- ทุก objects (table, function) ที่เรียกใช้ภายใน stored procedure ให้ระบุชื่อ schema นำหน้าทุกครั้ง เช่น dbo.tableX
- ถ้าจำเป็นต้อง execute คำสั่ง Transact-SQL ควรใช้ store procedure "sp_executesql" แทนการใช้คำสั่ง EXECUTE
- การตรวจสอบความถูกต้องของ input parameters ทั้งหมดให้ดำเนินการในช่วงแรกของ stored procedure หากมีข้อผิดพลาดของ input parameters จะได้ตรวจพบก่อน และข้ามขั้นตอนการประมวลผลที่ส่งผลกับ performance ได้
- การเรียกใช้ stored procedure จากระบบหรือโปรแกรมที่เขียนขึ้น ให้เรียกใช้ด้วย qualified name เช่น exec dbo.myStored ไม่ควรใช้ exec myStored เฉยๆ
- หาก stored procedure ใด return ค่าๆ เดียวกลับมา ไม่ใช่ record set ให้กำหนด output parameter ด้วย
- ใช้ stored procedure แทนที่ view จะได้ performance ที่ดีกว่า
- ไม่ควรเขียนโค๊ด หรือกำหนดตัวแปร พารามิเตอร์ใดๆ ที่ไม่ได้ใช้ใน store procedure
- การเขียน comment ต่างๆ ใน store procedure สามารถใช้ได้โดยไม่ส่งผลกับ performance แต่อย่างใด แถมยังช่วยให้เราเข้าใจมากขึ้นในการกลับมาแก้ไขงานอีกด้วย
- หลีกเลี่ยงการใช้ SQL Server cursors ถ้าเป็นไปได้ เนื่องจากจะทำให้เสีย resources และ performance ค่อนข้างมาก
- โดยทั่วไปแล้วการใช้คำสั่ง join จะประมวลผลได้ไวกว่าการใช้ sub-query แต่ก็ไม่ใช่เสมอไป ในการค้นหาข้อมูลบางกรณี อาจจำเป็นต้องทดสอบการใช้งานทั้ง 2 คำสั่งนี้เพื่อเปรียบเทียบก่อนว่า performance ใดดีกว่า
- ถ้ามีการสร้าง temporary table ในรูปแบบ global หรือ per connection use ให้พิจารณาว่าหากใช้เก็บข้อมูลในปริมาณมาก ควรกำหนด index ให้กับ temporary table ด้วย
- การใช้ derived table แทนที่ temperary table จะได้ performance ที่ดีกว่า ให้คำนึงถึงวัตถุประสงค์และขอบเขตในการใช้งาน หากเลี่ยงไม่ได้ก็ต้องใช้ temperary table
- พยายามใช้ table variable แทนการใช้ temperary table ใน Transact-SQL
- ให้จำกัดจำนวนคอลัมน์ที่ต้องการค้นหาข้อมูล หรือปรับปรุงเฉพาะคอลัมน์ที่จำเป็นเท่านั้น เพื่อลดจำนวนของข้อมูลที่ใช้อ่านและเขียนข้อมูล
- การใช้ฟังชั่น CONVERT ให้ระบุขนาดของข้อมูลด้วย และให้จำกัดเท่าที่จำเป็นต้องใช้ จะช่วยลดหน่วยความจำในการใช้งาน
- ให้หลีกเลี่ยงการใช้คำสั่ง DISTINCT โดยไม่จำเป็น
- การเพิ่มข้อมูลลงในตารางโดยใช้คำสั่ง SELECT INTO ระหว่างที่ประมวลผล ตารางที่กำลังเพิ่มข้อมูลอยู่นั้นจะถูก lock อาจส่งผลให้ transaction อื่นๆ ไม่สามารถเข้าถึงข้อมูลได้ในชั่วขณะหนึ่ง ถ้าสามารถเลือกได้ให้ใช้คำสั่ง SELECT INTO กับข้อมูลไม่มากแต่ทำหลายๆ ครั้ง ดีกว่าทำกับข้อมูลมากๆ ในครั้งเดียว
- ในการตรวจสอบว่ามีข้อมูลอยู่แล้วหรือไม่นั้น ให้ใช้คำสั่ง IF EXISTS ให้หลีกเลี่ยงการใช้ SELECT COUNT(*) เพื่อหาค่ามาตรวจสอบ
- หากเป็นไปได้เลี่ยงใช้งานฟังชั่นกับคอลัมน์ข้อมูลใน WHERE clauses เพราะจะไม่ได้ใช้ประโยชน์ของ index การประมวลผลจำเป็นต้องสแกนข้อมูลในตารางเสียก่อน เช่น WHERE SUBSTRING(column_name, 2, 2) = 'ab'
- คำสั่ง NOT IN จะส่งผลให้ performance แย่มาก พยายามทดแทนด้วยคำสั่งต่อไปนี้ ตามลำดับ
- ใช้ EXISTS หรือ NOT EXISTS -> สามารถดูวิธีใช้งานคำสั่ง EXISTS ได้ที่http://msdn.microsoft.com/en-US/library/ms188336(v=sql.90).ASPX
- ใช้ IN
- ใช้ LEFT OUTER JOIN แล้วตรวจสอบค่า NULL
- จากหัวข้อข้างต้น เมื่อต้องเลือกระหว่างใช้คำสั่ง IN หรือ EXISTS ให้เลือกใช้ EXISTS จะประมวลผลได้ไวกว่า
- ถ้ามีการใช้คำสั่ง IN ชุดของข้อมูล (list of values) ที่ใช้ค้นหา ให้ขึ้นต้นด้วยข้อมูลที่มีโอกาสค้นหาเจอมากที่สุดก่อน ไปถึงข้อมูลที่โอกาสค้นหาเจอน้อยที่สุด จะเพิ่มความรวดเร็วในการค้นหา
- คำสั่ง LIKE ถ้าเป็นไปได้ ให้ใช้กับเงื่อนไขค้นหาคำนำหน้าเท่านั้น เช่น LIKE 'xx%' ไม่ควรใช้กับ LIKE '%xx' หรือ LIKE '%xx% (รูปแบบท้ายสุด โดยส่วนใหญ่จะเลี่ยงกันไม่ได้เนาะ - -")
- ถ้าระบบหรือโปรแกรมมีการดึงค่าผลรวม หรือผลการคำนวณอยู่บ่อยๆ หากไม่ต้องการให้การทำงานของฐานข้อมูลหนักจนเกินไป ให้สร้างตารางใหม่ขึ้นเพื่อใช้จัดเก็บผลการคำนวณ และอาจใช้ trigger ช่วยในการจัดเก็บข้อมูลได้
- ไม่ใช้ ORDER BY ในคำสั่ง SELECT หากไม่จำเป็น เป็นไปได้ควร sort ข้อมูลที่ฝั่ง client ไม่ใช่ server (คำสั่งที่ทำให้เกิดการ sort ข้อมูล ประกอบด้วย ORDER BY, GROUP BY, SELECT DISTINCT และ UNION)
- ให้พิจารณาคอลัมน์ที่มีโอกาสใช้ sort บ่อยครั้ง เป็นไปได้ให้สร้าง clusterd index ให้กับคอลัมน์เหล่านั้น
- ถ้าระบบหรือโปรแกรมมีการใช้ wildcard text search (LIKE %) มากๆ กับข้อมูลประเภท CHAR หรือ VARCHAR ให้ใช้ full-text search option ของ SQL Server จะเพิ่มความเร็วในการค้นหาข้อมูล
- ควรประมวลผลข้อมูลต่างๆ ที่ฝั่งของ SQL Server และส่งข้อมูลเฉพาะที่จำเป็นให้กับ client นำไปแสดงผลเท่านั้น ควรหลีกเลี่ยงการส่งข้อมูลทั้งหมด หรือปริมาณมากๆ ไปให้ client คัดกรองก่อนนำใช้งานอีกครั้งหนึ่ง เพื่อเป็นการลด network traffic ที่เกิดขึ้นระหว่าง client หรือ server กับ SQL Server
