เคล็ดลับและเทคนิค SQL

 (github.com/ben-n93)
7 คะแนน โดย GN⁺ 2024-09-26 | 2 ความคิดเห็น | แชร์ทาง WhatsApp
  • เป็นรายการที่รวบรวม นิสัยการเขียน SQL และรูปแบบคิวรี ที่ใช้บ่อยในงานวิเคราะห์ข้อมูล โดยมีข้อสมมติว่าอาจไม่ได้ใช้ได้เหมือนกันกับ RDBMS ทุกตัว
  • ในด้านความอ่านง่าย แนะนำวิธีที่ทำให้คิวรีอ่านและแก้ไขได้ง่ายขึ้น เช่น comma นำหน้า, WHERE 1=1, การเยื้อง, CTE, คอมเมนต์ และ USING
  • ในด้านการประมวลผลข้อมูล ยกตัวอย่างไวยากรณ์ที่ใช้จริงในการกรองผลลัพธ์ สร้างผลรวม และตรวจสอบความแตกต่างของตาราง เช่น anti-join, QUALIFY, GROUP BY ROLLUP, EXCEPT
  • ในด้านประสิทธิภาพและความถูกต้อง NOT IN ที่มี NULL ปนอยู่, การแปลงชนิดข้อมูลโดยนัย, และการชนกันของ alias ของฟิลด์ที่คำนวณ อาจทำให้ผลลัพธ์หรือความเร็วของคิวรีไม่เสถียรได้
  • สำหรับคิวรีที่ซับซ้อน นิสัยพื้นฐานอย่างการเข้าใจลำดับการทำงาน ตรวจสอบเอกสาร ระบุที่มาของคอลัมน์ และตั้งชื่อคิวรีที่บันทึกไว้ มีความสำคัญต่อการดีบักและการนำกลับมาใช้ซ้ำ

นิสัยที่ช่วยให้การเขียน SQL อ่านง่ายขึ้น

  • repository นี้เป็นรายการที่สรุป เคล็ดลับและเทคนิค SQL ที่ได้เรียนรู้มาตลอดหลายปี โดยเน้นสิ่งที่มีประโยชน์ในงานประจำวันของนักวิเคราะห์ข้อมูล และสิ่งที่อยากรู้ตั้งแต่ตอนเริ่มเขียน SQL
  • เคล็ดลับบางข้ออาจไม่เหมาะกับ RDBMS ทุกตัว

  • comma นำหน้าและ AND นำหน้า

    • สำหรับการคั่นฟิลด์ใน SELECT แนะนำให้ใช้ comma นำหน้า แทน comma ต่อท้าย
    • ทำให้เห็นชัดขึ้นว่าเป็นคอลัมน์ใหม่หรือเป็นโค้ดที่ตัดบรรทัดลงมา
    • แม้ความยาวบรรทัดจะแตกต่างกัน ก็หาจุดที่ลืมใส่ comma ได้ง่ายขึ้น
    • ด้วยเหตุผลเดียวกัน สามารถใส่ AND นำหน้าเงื่อนไขใน WHERE ได้เช่นกัน

  • ทำให้ทดสอบเงื่อนไขง่ายขึ้นด้วย WHERE 1=1

    • หากใส่ เงื่อนไข dummy 1=1 ใน WHERE แม้จะคอมเมนต์เงื่อนไขออกระหว่างทดสอบ คิวรีก็จะไม่พัง
    • แม้จะคอมเมนต์เงื่อนไขทั้งหมดออกไป ก็ยังเหลือ 1=1 ทำให้คิวรียังรันต่อได้

  • การเยื้องและ formatter

    • การเยื้องช่วยให้เพื่อนร่วมงานและตัวคุณในอนาคตอ่านคิวรีได้ง่ายขึ้น
    • หากมี guideline ของทีมหรือบริษัทก็ควรทำตาม หากไม่มีก็ใช้รูปแบบที่เหมาะกับตัวเองจะดีกว่า
    • สามารถใช้ formatter ออนไลน์อย่าง poorsql หรือ linter อย่าง sqlfluff ได้

  • พิจารณาใช้ CTE สำหรับคิวรีที่ซับซ้อน

    • หากซ้อน inline view มากกว่า 2–3 ชั้น คิวรีมักกลายเป็นสิ่งที่เข้าใจยากเมื่อกลับมาดูอีกครั้งหลังผ่านไปหลายสัปดาห์
    • CTE ถูกนำเสนอเป็นวิธีทำให้คิวรียาว ๆ เป็นระเบียบขึ้น และช่วยเรื่องการนำกลับมาใช้ซ้ำกับการดีบัก

  • คอมเมนต์ควรอธิบาย “ทำไม”

    • เมื่อเวลาผ่านไป อาจจำได้ยากว่าทำไมถึงต้องประมวลผลบางอย่างแบบนั้น
    • โดยทั่วไป คอมเมนต์ควรอธิบายว่า ทำไม ถึงทำแบบนั้น มากกว่าจะอธิบายว่าโค้ดทำงาน “อย่างไร”
    • ตัวอย่างคือการใส่คอมเมนต์ให้เงื่อนไขที่ตัดเนื้อหา archive ออก เพราะ CMS ใหม่ไม่สามารถจัดการรูปแบบวิดีโอ archive ได้

  • ใช้ USING เมื่อ join คอลัมน์ชื่อเดียวกัน

    • เมื่อ join ด้วยคอลัมน์ที่มีชื่อเดียวกันในสองตาราง การใช้ USING จะเขียน join ได้กระชับกว่า ON
    • USING จะ ลบคอลัมน์ร่วมที่ซ้ำกัน ออกจากผลลัพธ์และคืนกลับมาเพียงคอลัมน์เดียว
    • หากใช้ ON แล้วไม่ได้ระบุคอลัมน์ร่วมให้ชัดเจน อาจเกิดข้อผิดพลาด ambiguous column name ได้

ไวยากรณ์ที่มีประโยชน์ต่อการประมวลผลข้อมูล

  • ใช้ anti-join เพื่อหาแถวที่ไม่มีในอีกตาราง

    • anti-join ใช้เมื่อต้องการคืนค่าแถวที่มีอยู่ในตารางหนึ่ง แต่ไม่มีแถวที่ match กันในอีกตาราง
    • ตัวอย่างกล่าวถึงสถานการณ์ที่ต้องการดึงเฉพาะ video_id ของเนื้อหาที่ยังไม่ถูก archive
    • มีหลายวิธีในการทำ
    • ใช้ LEFT JOIN แล้วกรองเฉพาะแถวที่ key ของตารางที่ match เป็น NULL
    • ใช้ NOT IN กับ subquery
    • ใช้ NOT EXISTS กับ correlated subquery
    • ไม่แนะนำให้ใช้ NOT IN เพราะค่า NULL อาจทำให้ไม่ทำงานตามที่ตั้งใจ

  • กรองผลลัพธ์ของ window function ด้วย QUALIFY

    • QUALIFY ช่วยให้กรองผลลัพธ์ของคิวรีตามผลลัพธ์ของ window function ได้
    • สามารถกรองได้โดยไม่ต้องใช้ inline view จึงลดจำนวนบรรทัดของโค้ดได้
    • ตัวอย่างเลือกตลาด 10 อันดับแรกของแต่ละผลิตภัณฑ์ด้วย DENSE_RANK() แล้วกรองด้วย QUALIFY
    • มีข้อจำกัดว่า QUALIFY ดูเหมือนจะมีให้ใช้เฉพาะใน data warehouse ขนาดใหญ่ เช่น Snowflake, Amazon Redshift, Google BigQuery

  • GROUP BY และ ORDER BY ตามตำแหน่งคอลัมน์

    • สามารถใช้ ตำแหน่งคอลัมน์ แทนชื่อคอลัมน์ได้ เช่น GROUP BY 1, ORDER BY 2
    • อาจมีประโยชน์สำหรับคิวรีชั่วคราวหรือคิวรีใช้ครั้งเดียว
    • สำหรับ production code แนะนำให้ refer ถึงชื่อคอลัมน์โดยตรงเสมอ

  • สร้างผลรวมด้วย GROUP BY ROLLUP

    • GROUP BY ROLLUP ใช้สร้าง subtotal และ grand total ได้
    • ตัวอย่างสร้างแถวผลรวมเงินเดือนทั้งหมด พร้อมกับคำนวณผลรวมเงินเดือนแยกตามแผนก
    • เอกสาร Transact-SQL อธิบายว่า ROLLUP สร้างกลุ่มตามชุดค่าผสมของนิพจน์คอลัมน์ และลดจำนวนกลุ่มจากขวาไปซ้ายเพื่อสร้าง subtotal และ grand total
    • หากใช้ COALESCE จะสามารถแสดงแถวผลรวมทั้งหมดเป็น Total ได้
    • ต้องใส่ใจคอลัมน์สำหรับ sort เพื่อให้แถวผลรวมทั้งหมดมาอยู่ท้ายผลลัพธ์

  • หา difference ระหว่าง result set สองชุดด้วย EXCEPT

    • EXCEPT คืนค่าแถวที่มีในผลลัพธ์ของคิวรีแรก แต่ไม่มีในผลลัพธ์ของคิวรีที่สอง
    • หากใช้ EXCEPT ร่วมกับ UNION ALL จะสามารถตรวจสอบได้ว่าสองตารางมีข้อมูลเหมือนกันหรือไม่
    • หากไม่มีแถวที่คืนกลับมา แสดงว่าสองตารางเหมือนกัน
    • หากมีแถวที่คืนกลับมา แถวเหล่านั้นคือสาเหตุที่ทำให้เกิดความแตกต่าง

รูปแบบที่บั่นทอนประสิทธิภาพและความถูกต้อง

  • สำหรับคอลัมน์ที่อาจเป็น NULL ใช้ NOT EXISTS ดีกว่า NOT IN

    • หากคอลัมน์ที่ใช้เปรียบเทียบอนุญาตให้เป็น NULL ได้ NOT IN มักอาจช้ากว่า NOT EXISTS
    • ผู้เขียนพบปรากฏการณ์นี้ใน Snowflake และ PostgreSQL Wiki หน้า Don’t Do This ระบุว่า NOT IN (SELECT ...) ไม่ได้รับการ optimize ที่ดี
    • หากค่าที่ใช้เปรียบเทียบมี NULL อยู่ NOT IN จะไม่ทำงานตามที่ตั้งใจ
    • การที่คอลัมน์อนุญาตให้เป็น NULL ไม่ได้แปลว่ามีค่า NULL จริง ๆ แต่เมื่อทำงานกับตารางที่แก้ไขไม่ได้ NOT EXISTS อาจช่วยปรับปรุงความเร็วได้

  • การแปลงชนิดข้อมูลโดยนัยอาจทำให้ช้าลงหรือล้มเหลวได้

    • หากใส่ค่าที่มี data type ต่างจากคอลัมน์ลงในเงื่อนไข ฐานข้อมูลอาจพยายามทำ การแปลงชนิดข้อมูลโดยนัย
    • ตัวอย่างกล่าวถึงกรณีเปรียบเทียบคอลัมน์ video_id ที่เป็นชนิด string กับ integer 200050
    • การพึ่งพาการแปลงชนิดข้อมูลโดยนัยอาจก่อปัญหาได้
    • หากมีค่าที่แปลงไม่ได้ อาจเกิด error
    • คิวรีอาจช้าลงเพราะมีงานเพิ่มเติมในการแปลงแต่ละค่าให้เป็นชนิดที่กำหนด
    • ควรใช้ data type เดียวกับคอลัมน์ หรือหากต้องการหลีกเลี่ยง error สามารถใช้ฟังก์ชันอย่าง TRY_TO_NUMBER ของ Snowflake ได้
    • ผลกระทบด้านความเร็วขึ้นอยู่กับขนาด dataset ที่ประมวลผล

ข้อผิดพลาดที่พบบ่อย

  • NOT IN กับ NULL

    • NOT IN จะไม่ทำงานหากค่าที่ใช้เปรียบเทียบมี NULL
    • NULL แทนค่า Unknown ดังนั้น SQL engine จึงไม่สามารถยืนยันได้ว่าค่าที่ตรวจสอบไม่ได้อยู่ใน list
    • ในกรณีนี้ การใช้ NOT EXISTS เป็นทางเลือกหนึ่ง

  • การชนกันของ alias ของฟิลด์ที่คำนวณ

    • หากตั้งชื่อฟิลด์ที่คำนวณให้เหมือนกับคอลัมน์เดิม อาจเกิดพฤติกรรมที่ไม่คาดคิดได้
    • เอกสาร GROUP BY ของ Snowflake ระบุว่า หากชื่อใน GROUP BY match ได้ทั้งชื่อคอลัมน์และ alias จะใช้ชื่อคอลัมน์
    • ในตัวอย่าง หากสร้าง alias ด้วย LEFT(product, 1) AS product แล้วใช้ GROUP BY product จะถูก group ด้วยคอลัมน์ product เดิม ไม่ใช่ตัวอักษรแรก จึงคืนค่ากลับมา 3 แถว
    • วิธีแก้มีสองแบบ
    • ใช้ alias ที่ไม่ซ้ำ เช่น product_letter
    • ระบุนิพจน์ให้ชัดเจน เช่น GROUP BY LEFT(product, 1)
    • ใน window function ก็อาจเกิดปัญหา alias ได้เช่นกัน
    • ในตัวอย่าง แม้จะใช้ CASE เปลี่ยน revenue ของ Robot เป็น 0 แต่การเปลี่ยนแปลงถูกนำไปใช้หลังจาก window function ทำงานแล้ว ทำให้อันดับไม่เป็นไปตามที่คาด
    • หากทำได้ ควรใช้ alias ที่ไม่ซ้ำ หรือใส่นิพจน์คำนวณโดยตรงใน ORDER BY ของ window function

  • ระบุว่าคอลัมน์เป็นของตารางใด

    • ในคิวรีซับซ้อนที่มีหลาย join ควรสามารถ trace ปัญหาของค่ากลับไปถึงตารางต้นทางได้
    • เมื่อสองตารางมีชื่อคอลัมน์เดียวกัน หากไม่ระบุว่าคอลัมน์เป็นของตารางใด RDBMS อาจแจ้ง error
    • ตัวอย่างใส่ table alias เช่น vc.video_id, metadata.season เพื่อทำให้ที่มาของคอลัมน์ชัดเจน

ลำดับการทำงาน เอกสาร และชื่อที่บันทึก

  • เข้าใจลำดับการทำงานของ SQL

    • หนึ่งในคำแนะนำที่สำคัญที่สุดสำหรับผู้เรียน SQL คือการเข้าใจ ลำดับการทำงานของ clause
    • หากรู้ลำดับการทำงาน วิธีเขียนคิวรีอาจเปลี่ยนไปอย่างมาก
    • แนะนำแหล่งอ้างอิง A beginner’s guide to the true order of SQL operations

  • อ่านเอกสารให้จบ

    • มีกรณีที่ใช้ GREATEST() ใน Snowflake เพื่อคืนค่าวันที่ล่าสุดจากหลายคอลัมน์วันที่
    • GREATEST() จะคืนค่า NULL หาก argument ตัวใดตัวหนึ่งเป็น NULL
    • หากอ่านเอกสารต่ออีกหน่อย ก็สามารถใช้ GREATEST_IGNORE_NULLS() แทน COALESCE(GREATEST(...), ...) ได้
    • ในหลายกรณี การกวาดตาดูเอกสารใช้เวลาไม่ถึง 1 นาที และช่วยลดความพยายามในการหาสาเหตุที่ทำงานไม่ตรงกับที่คาดได้

  • ใช้ชื่อที่อธิบายได้สำหรับคิวรีที่บันทึกไว้

    • เพื่อหลีกเลี่ยงสถานการณ์ที่หาคิวรีที่ต้องรันซ้ำหรือใช้อ้างอิงไม่เจอ ควรบันทึกด้วย ชื่อที่อธิบายได้
    • ชื่อที่บันทึกมักประกอบด้วยหัวข้อของคิวรี เดือนที่รัน และชื่อผู้ร้องขอ
    • ตัวอย่างคือรูปแบบ Lapsed users analysis - 2023-09-01 - Olivia Roberts

บทความที่เกี่ยวข้อง

2 ความคิดเห็น

 
hiyama 2024-09-26

เครื่องหมายจุลภาคนำหน้าในโพสต์นี้ถูกเขียนเป็นจุลภาคต่อท้ายทั้งหมดเลยครับ ในต้นฉบับใส่เป็นแบบนำหน้าไว้

-- Good:  
SELECT   
timeslot_date  
, timeslot_channel   
, overnight_fta_share  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7, -- First argument of IFF.  
	LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity), -- Second argument of IFF.  
		NULL) AS C7_fta_share -- Third argument of IFF.  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,   
		LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),   
			NULL) AS C28_fta_share  
FROM timeslot_data  
;
 
GN⁺ 2024-09-26
ความคิดเห็นจาก Hacker News

  • ทิปที่ฉันอยากเสริมคือ: ต้องเรียนรู้ DB server ที่ใช้อยู่ให้ดี และตรวจ execution plan บ่อย ๆ เพราะอาจได้ผลลัพธ์ที่คาดไม่ถึง ควรปรับแล้วตรวจใหม่เสมอ
    โดยทั่วไป EXISTS มักเร็วกว่า IN และ NOT EXISTS ก็ทำงานต่างจาก EXCEPT ในการจัดการ NULL แทนที่จะ join ตารางแล้วค่อยกรองแถวออกด้วยอะไรอย่าง DISTINCT การใช้ subquery column ในรายการ SELECT บางครั้งอาจเร็วกว่าได้มาก แม้จะต้องดึงค่ามากกว่า 10 ค่าจากตารางเดียวกันก็ยังเป็นแบบนั้นได้ และอาจยังจริงอยู่แม้ DB server จะรองรับ lateral join ก็ตาม แต่ subquery ต้องคืนค่าได้ไม่เกินหนึ่งแถว
    ถ้าไม่ใช่ query ที่รันครั้งเดียว ก็ควรหลีกเลี่ยงไม่ให้มี table scan ทั้งตาราง table scan ของวันนี้อาจกลายเป็นเหตุขัดข้องของวันพรุ่งนี้ได้ จึงควรเพิ่ม index ไว้ และอย่าลืมว่าปกติแล้ว clause GROUP BY มักมีผลอย่างมากต่อการใช้ index
    ถ้าจำเป็นต้อง filter ด้วย expression เช่น ต้องตรวจว่าบาง substring เท่ากับค่าที่กำหนดหรือไม่ ก็สามารถเพิ่ม computed column แล้วสร้าง index บนคอลัมน์นั้นได้ บาง DB รองรับ expression index โดยตรงด้วย การใช้ UNION ALL แทน OR ก็มักทำให้เร็วขึ้นมากใน query ที่ซับซ้อนหรือมีหลายเงื่อนไข OR
    ถ้า DB ไม่สามารถจัดลำดับการ filter ได้อย่างฉลาดพอ การ JOIN subquery เพื่อบังคับลำดับก็มีประโยชน์เช่นกัน

    • สิ่งที่มีประโยชน์ที่สุดคือ เรียนรู้ตัว DBMS เอง เพราะแต่ละ DB มีจุดเฉพาะของตัวเองทั้งเรื่องประสิทธิภาพ ระดับการแยกธุรกรรม และฟีเจอร์เสริม จึงเลี่ยงไม่ได้
      สิ่งที่น่าสนใจใน Postgres คือ และ DB อื่นก็น่าจะเป็นได้เหมือนกัน การทำ manual sharding ให้กับงาน INSERT (SELECT ...) ตามจำนวน CPU core สามารถทำให้เร็วขึ้นเกือบเป็นเส้นตรงได้ แม้จะมี join ราว 10 ตัวก็ยังทำได้ ให้ดู EXPLAIN ก่อน หา join ที่อยู่ชั้นในสุดหรือชั้นนอกสุด แล้วรัน query แบบขนานแยกตามช่วงแถว (id >= start AND id < end) ได้เลย เมื่อ 6 ปีก่อนฉันใช้วิธีนี้บ่อยมากในงานหนึ่งด้วยเหตุผลประหลาดบางอย่าง Postgres 10+ เพิ่มความสามารถด้าน parallelism เข้ามาแล้ว แต่เท่าที่รู้ก็ยังไม่ก้าวหน้าถึงระดับนี้
    • ฉันไม่แน่ใจจริง ๆ ว่าการบอกให้ใช้ subquery “column” ในรายการ SELECT หมายถึงอะไรแน่
      เช่น ถ้ารัน SELECT column1, (SELECT column2, column3, ... FROM table_b WHERE table_a.id = table_b.a_id) FROM table_a ก็จะได้ข้อความ “subquery must return only one column” ตามคาด หมายถึงให้คืนหลายคอลัมน์มาเป็น record/composite type เหรอ?
      ส่วนที่บอกว่า clause GROUP BY มักมีผลต่อการใช้ index ทำไมถึงเป็นอย่างนั้น ฉันยังนึกภาพไม่ค่อยออก แต่สำหรับคนที่สงสัย บทความนี้อธิบายเป็นขั้นตอนได้ดีมาก: https://www.brentozar.com/archive/2015/06/indexing-for-group...
    • เห็นด้วย ควรใช้ EXPLAIN และเรียนรู้วิธีตีความมันด้วยเครื่องมือที่คุณถนัด และควร monitor query ด้วย
      ที่สตาร์ตอัปก่อนหน้านี้ฉันติดตั้ง PgHero ไว้ ซึ่งช่วยมากจริง ๆ ทั้งในการปรับจูนประสิทธิภาพและจัดลำดับความสำคัญ
    • แม้ query จะออกแบบมาดี ก็ยังทำงานต่างจากที่คาดได้บ่อย สาเหตุที่พบบ่อยคือสถิติของคอลัมน์ไม่ได้อัปเดต หรือข้อมูลในตารางใหญ่เกิด fragmentation ตัวอย่างเช่นกรณี การ insert primary key แบบสุ่ม
    • ฉันไม่เห็นด้วยกับประโยคที่ว่า “ถ้าไม่ใช่ query ที่รันซ้ำ ๆ ไม่ควรมี table scan ทั้งตาราง table scan ของวันนี้อาจกลายเป็นเหตุขัดข้องของวันพรุ่งนี้”
      มี query ที่ table scan ทั้งตาราง เป็นกลยุทธ์ที่มีประสิทธิภาพที่สุดอยู่เหมือนกัน โดยมากคือ query วิเคราะห์/สรุปผลที่อ่านทั้งตาราง และบางครั้งแม้จะดึงมาเพียง 50% ของทั้งตาราง table scan ก็ยังดีกว่า
      และฉันก็ไม่ค่อยเข้าใจว่า table scan แบบอ่านอย่างเดียวจะนำไปสู่เหตุขัดข้องได้อย่างไร เพราะมันไม่ได้บล็อกการเข้าถึงพร้อมกัน ข้อเสียก็มีแค่เพิ่มภาระ I/O เท่านั้น ซึ่งถ้าเซิร์ฟเวอร์รับแค่นี้ไม่ไหว ก็ถือว่าสเปกต่ำเกินไปอย่างหนักตั้งแต่แรก

  • ตัวอย่าง 3 อันในส่วน “ความอ่านง่าย” ดูแปลก ๆ สองอันแรกพยายามทำให้เขียนง่ายขึ้นโดย ยอมลดความอ่านง่าย แบบตรงตัว ส่วนอันสุดท้ายก็ดูเป็นสัตว์ประหลาดอ่านยากที่แทบจะกู้ไม่ได้แม้จะจัดย่อหน้าแล้วก็ตาม

    • รูปแบบ comma นำหน้ามีข้อดีนอกจากเรื่องความอ่านง่ายด้วย เช่น ในระบบควบคุมเวอร์ชัน การเขียนแบบ หนึ่งอาร์กิวเมนต์ต่อหนึ่งบรรทัด + comma นำหน้า ทำให้การแก้อาร์กิวเมนต์แสดงเป็น diff แค่บรรทัดเดียว
      ฉันคิดว่านักพัฒนาดูประวัติ commit กันมากพอ ๆ กับที่ดูซอร์สโค้ดจริง
    • ฉันไม่ได้ชอบหน้าตาของสองแบบแรกมากนัก แต่ก็เป็นรูปแบบที่คนเขียน SQL ใช้กันจริง ๆ และก็พอเข้าใจได้ว่าทำไมมันถึงมีอยู่
      เจอบ่อยพอจนตอนนี้ไม่ได้รู้สึกขัดตาเท่าไรแล้ว
    • อีกทางเลือกคือ เขียน SQL ให้เละเหมือนเด็กสามขวบเพิ่งค้นพบ MSPaint แล้วกดปุ่ม “beautifier” ก่อนออกไปกินมื้อเที่ยงเร็ว ๆ
    • ไม่เข้าใจว่าทำไมถึงคิดว่ามันแย่กว่า
      ฉันไม่เห็นว่ามีปัญหาอะไร
      และก็ดูไม่เห็นว่ามีอะไรผิด
    • ใครกันที่แยกคอลัมน์ในบล็อก SELECT เป็นบรรทัดละคอลัมน์ แต่กลับปล่อยบรรทัดยาว 150 ตัวอักษรไว้แบบเดิม? นี่คือ นิยามของความอ่านง่ายที่พังไปแล้ว เรื่อง comma ยังไม่ต้องเริ่มเลย
      ใน code review ไม่มีใครอ่านบรรทัดยาว ๆ ได้จริงจัง นั่นคือปัญหาใหญ่ที่สุดของ AngularJS merge ถูกทำผิดและทุกอย่างพัง เพราะพอสายตาไปถึงคอลัมน์ที่ 90 ก็เริ่มเบลอแล้ว ฉันผ่านทีมที่มี code review มาเกินครึ่งโหลและเจอเหมือนกันทุกครั้ง ต่อให้ตั้งใจระวังและพยายามหลีกเลี่ยงปัญหานี้มากแค่ไหน ฉันเองก็ยังพลาดอยู่ด้วยความถี่ประมาณครึ่งหนึ่งของคนอื่น
      แบ่งบรรทัดกันเถอะ โดยเฉพาะถ้าจะเอาตัวอย่างไปให้คนอื่นดู ยิ่งควรทำแบบนั้น

  • เคล็ดลับในการจัดการ stored procedure ที่ซับซ้อนมีดังนี้

    1. ที่จุดเริ่มต้นของ procedure ให้คัดลอกตารางถาวรไปยังตารางชั่วคราวทันที แล้วระบุ/จำกัด/กรองเฉพาะแถวที่จำเป็น
    2. ระหว่างทางให้จัดการตารางชั่วคราวตามที่ต้องการ
    3. ช่วงท้ายให้อัปเดตตารางถาวรภายในทรานแซกชัน หากตรวจพบข้อผิดพลาดให้ rollback ทรานแซกชันทันทีและจบ procedure การทำตามสามขั้นตอนนี้ช่วยให้ concurrency ดีขึ้น และสามารถเริ่ม procedure ใหม่ได้โดยไม่ต้องมานั่งล้างข้อมูลตกค้างเอง
    4. เวลาจัดการตารางระยะไกลต้องระวังอย่างยิ่ง เพราะตารางระยะไกลไม่ได้อยู่ใน RDBMS ปัจจุบัน จึงมีโอกาสสูงที่จะใช้สถิติหรือดัชนีของ RDBMS นั้นแทบไม่ได้เลย ในหลายกรณีการ dump/คัดลอกตารางระยะไกลทั้งก้อนไปไว้ในตารางชั่วคราวก่อนแล้วค่อยทำงานจะเร็วกว่า สิ่งสูงสุดที่พอคาดหวังได้จากตารางระยะไกลคือการรัน WHERE clause หากพยายามทำ JOIN หรือทำงานซับซ้อน มีโอกาสสูงที่จะ timeout
    5. execution plan ทำให้สับสนได้ง่าย ในบางกรณี execution plan อาจตกไปเป็นการประมวลผลแบบทีละแถวจนประสิทธิภาพหยุดชะงักได้ บ่อยครั้งการแยก stored procedure ที่ซับซ้อนออกเป็นขั้นเล็ก ๆ โดยใช้ตารางชั่วคราวจะดีกว่า
    6. หากต้องการดูว่า RDBMS กำลังทำอะไรอยู่จริง ๆ ต้องตรวจสอบ execution plan เสมอ
    • ผมเคยปรับปรุงประสิทธิภาพของคิวรีได้มากด้วยการย้อนโค้ดที่ใช้ข้อ 5 ทั้งที่ไม่จำเป็น บางครั้งการแยกคิวรีออกเป็นคิวรีย่อยหลายตัวกลับไร้ประสิทธิภาพกว่ามาก เมื่อเทียบกับการให้ query optimizer เห็นคิวรีทั้งหมดแล้วหาทางที่เหมาะที่สุดเอง
      ถ้าทำข้อ 5 โดยไม่ทำข้อ 6 ก็มีโอกาสสูงที่จะไม่เห็นว่าตัวเองกำลังทำสิ่งที่ไม่เหมาะสม คำแนะนำของผมคือหลีกเลี่ยงการ optimize เร็วเกินไป เขียนในแบบที่ตรงไปตรงมาที่สุดก่อน แล้วค่อย optimize เมื่อจำเป็น สิ่งสำคัญที่สุดคืออย่าเขียน SQL แบบเชิงกระบวนการ คุณกำลังอธิบายข้อมูลที่ต้องการ ไม่ได้สั่ง engine ว่าควรไปดึงมาอย่างไร
    • ไม่ชอบที่ต้องใช้ตารางชั่วคราวจำนวนมาก แต่ก็มักเจอคิวรีที่ถ้าปล่อยให้ query planner จัดการเองจะไม่มีวันจบ ความสามารถของ query planner ก็ถูกประเมินสูงเกินจริงมากพอ ๆ กับคอมไพเลอร์
      ในทางกลับกัน Microsoft ก็ชอบเตือนตลอดว่าอย่าพยายามจูนมัน ราวกับว่า query planner รู้ดีที่สุด
    • กฎเหล่านี้อาจใช้ได้ตรงเป๊ะกับ DB ของบาง vendor แต่กับ DB อื่น ๆ ลำดับความสำคัญ คุณลักษณะ และ trade-off อาจต่างกันมาก
      เวอร์ชัน ของ DB ก็อาจมีผลได้เช่นกัน
    • ข้อ 1~3 ใช้ได้ถ้ารับประกันได้ว่าขนาดข้อมูลยังสมเหตุสมผล แต่ถ้าข้อมูลใหญ่เกินกว่าฮาร์ดแวร์จะรับไหว งานคัดลอกชุดข้อมูลใหญ่และอัปเดตชุดข้อมูลใหญ่อีกครั้งก็อาจเพิ่ม overhead อย่างมีนัยสำคัญ

  • ผมไม่ชอบการพัฒนาแบบ “เผื่อไว้ก่อน” ทั้งเรื่องอินเทอร์เฟซและ placeholder อย่าง where 1=1 ก็เหมือนกัน
    ให้ทำเมื่อจำเป็น อย่าทำเพียงเพราะคิดว่าวันหนึ่งในอนาคตอาจต้องใช้ โค้ดโปรดักชันไม่ใช่ที่สำหรับทิ้งตัวช่วยของฝั่งพัฒนาไว้ ระหว่างพัฒนาจะทำแบบไหนก็ได้ แต่ในโค้ดโปรดักชัน ความอ่านง่ายและเจตนาที่ชัดเจน สำคัญกว่ามาก

    • ปกติคุณอ้างอิงชื่อตารางและชื่อคอลัมน์แบบ fully qualified ทั้งหมดไหม? หลายครั้งผมพบว่ามันช่วยให้การอ่านดีขึ้นแบบก้าวกระโดด แต่ก็ยืดยาวมากอย่างรวดเร็ว และน่าเบื่อแบบเหลือเชื่อเวลาเขียน

  • ขอเสริมเรื่อง “anti join” อีกอย่างหนึ่ง ถ้าแค่ต้องการตรวจว่ามีแถวที่ตรงเงื่อนไขอยู่ในตารางใหญ่อื่นหรือ subquery หรือไม่ การใช้ EXISTS จะดีกว่า IN หรือ LEFT JOIN
    EXISTS จะคืนค่าเป็นจริงทันทีที่เจอรายการที่ตรงกัน ส่วน LEFT JOIN และ IN นั้น engine จะรวบรวมผลทั้งหมดก่อนประเมิน

    • ตรงนั้นผมว่าน่าสับสนนิดหน่อย ในทุกกรณีที่ผมทดสอบ (NOT) EXISTS ให้ execution plan ที่ดีกว่าหรืออย่างน้อยก็เท่ากับ (LEFT) JOIN หรือ (NOT) IN
      แถมยังสื่อเจตนาได้ชัดเจนกว่า

  • เรื่อง “ใส่คอมเมนต์ในโค้ด” มีคำแนะนำบ่อย ๆ ว่าอย่างน้อยใน MSSQL ควรใช้ /**/ แทน -- ในคอมเมนต์ เพราะฟีเจอร์อย่าง Query Store มักเก็บคิวรีโดยไม่มีการขึ้นบรรทัดใหม่ ทำให้พอดึงคิวรีออกมาจากตรงนั้นแล้ว แทนที่จะใช้ตัวจัดรูปแบบของ IDE ได้ทันที กลับต้องมานั่งแก้ทั้งหมดด้วยมือ

    • ฟังดูเหมือนเป็นบั๊กของ Query Store
    • cast เป็น XML ได้ไหม? ผมใช้แบบนั้นกับ OBJECT_DEFINITION
      select name,cast((select OBJECT_DEFINITION(object_id) for xml path('')) as xml) from sys.procedures
      เพราะมันเก็บบรรทัดใหม่ไว้ จึงอาจจัดระเบียบได้ง่ายขึ้น แต่ตัวอักษร XML อื่น ๆ จะเสีย เช่น > ถูกเปลี่ยนเป็น > อีกทางเลือกหนึ่งคือใช้ VARBINARY แล้วหาอะไรสักอย่างมาแปลงกลับ

  • ทุกคนดูจะเดือดกับข้อเสนอเรื่อง comma กันมาก แล้ว 1=1 ใน WHERE clause นี่ถือว่าเป็นความคิดที่ดีหรือ? ถ้าเห็นใน code review ผมไม่รู้จริง ๆ ว่าควรคิดยังไงกับคนเขียน

    • จะให้เหตุผลแบบเดียวกับ comma ท้ายบรรทัดก็พอได้ คือการแก้ WHERE statement จะไม่กระทบบรรทัดอื่น เลยทำให้ code review ง่ายขึ้น
      แต่ถ้าใช้ด้วยเหตุผลแบบในกรณีนี้ คือเพื่อเพิ่มเงื่อนไขแบบไดนามิก ที่ที่ผมทำงานอยู่คงโดนไล่ออกแน่

  • มีใครพอจะแชร์แนวทางทั่วไปได้ไหมว่า ควรขีดเส้นระหว่างการเพิ่มความเร็วด้วยการตั้งค่า DB ซึ่งแทบจะเป็นแนวทางแบบ “ซื้อ” กับการ “สร้าง” ด้วยการลงมือทำเองเกือบทั้งหมดไว้ตรงไหน? จากประสบการณ์อันจำกัดของฉัน DBA เก่ง ๆ มักได้ค่าตอบแทนสูงกว่ามากและไปทำงานที่อื่นกัน เลยทำให้งานนี้มักตกมาที่นักพัฒนาแอป อย่างที่พูดไว้ข้างบน การรู้เรื่อง DB จึงสำคัญ
    ตัวอย่างที่พบบ่อยคือข้อมูลที่สะสมจำนวนมากตามกาลเวลา และข้อมูลล่าสุดคือส่วนที่ถูกเข้าถึงบ่อยที่สุด DBA อาจทำให้การเข้าถึงยังคงเร็วได้ด้วยการทำ partitioning หรือ partial index แต่นักพัฒนาแอปก็อาจเลือกย้ายเรคคอร์ดเก่าไปไว้ในตาราง archive แยกต่างหากแบบเบื้องหลัง พร้อมทั้งยังรองรับความสามารถอย่างการค้นหาครอบคลุมชุดข้อมูลทั้งหมดในท้ายที่สุดได้ด้วย บางครั้งก็รู้สึกว่าเครื่องมือน่าจะช่วยทำงานตั้งต้นอย่างการแยกตารางหนึ่งออกเป็นหลายตารางในจังหวะที่เหมาะสมได้ค่อนข้างอัตโนมัติ เช่นในกรณีที่ถูกจำกัดด้วยการขาดฟีเจอร์ของ cloud DB
    อีกทางเลือกหนึ่งด้านการจัดการคือเก็บ blob/ไฟล์ขนาดใหญ่ทั้งหมดไว้ในฐานข้อมูลแยกต่างหาก หรือไม่ก็ใน file system เพื่อใช้การตั้งค่าการจัดเก็บอีกแบบหนึ่ง ซึ่งเรื่องนี้ก็เป็นได้ทั้งสิ่งที่ DB รองรับให้ หรือสิ่งที่ต้องจัดการเองแบบแมนนวล
    ในกรณีสุดโต่ง คุณอาจไปไกลถึงขั้นทำดัชนีขึ้นมาเองก็ได้ โดยมีตารางขนาดมหึมาหนึ่งตารางที่มีเพียง primary key แบบ auto-increment กับคอลัมน์จำนวนมาก และสร้างอีกตารางแยกที่มี ID นั้นพร้อมคอลัมน์ไม่กี่ตัวที่ใช้ค้นหาได้ อาจไปไกลถึง full-text search หรือ vector ก็ได้
    เคล็ดลับที่มีประโยชน์สำหรับการทำ materialized view pattern แบบแมนนวลบน MSSQL 2016+ คือใช้ partition switching ร่วมด้วย ซึ่งมีอธิบายและทำไว้ดีใน https://github.com/cajuncoding/SqlBulkHelpers?tab=readme-ov-... มันเป็นไลบรารีเล็ก ๆ ที่ฉันเจอโดยบังเอิญ แต่กลับมีประโยชน์เชิงพาณิชย์มากที่สุดตัวหนึ่ง ทั้งที่อันดับค้นหาไม่เด่นและดาวก็น้อย โดยโฟกัสที่การ bulk insert เข้า MSSQL ด้วย .NET ฉันคิดว่านี่เป็นตัวอย่างที่ดีของการขีดเส้นระหว่างการซื้อ/สร้างได้อย่างเหมาะสมผ่านการทำ automation ให้กับ partition switching

  • สิ่งที่ขาดไป: ควรเลิกใช้ SELECT * ได้แล้ว แทบจะแน่นอนว่าคุณไม่ได้ต้องการความกว้างทั้งตาราง และการทำแบบนั้นจะเพิ่มทั้งข้อมูลที่ต้องกรองและส่งต่อ แถมยังไปขัดขวางฟีเจอร์ดี ๆ อย่าง semi join ด้วย

    • คนที่ใช้ SQL แบ่งใหญ่ ๆ ได้เป็นสองพวก คือ นักวิเคราะห์ กับนักพัฒนา
      ถ้าเป็นนักพัฒนา ก็ใช่เลย SELECT * มีหลุมพราง และเกือบทุกครั้งควรระบุคอลัมน์ให้ชัด หรือใช้ query builder ที่ช่วยทำให้แทน
      แต่ถ้าเป็นนักวิเคราะห์ ชีวิตมันสั้น และบางครั้งคุณก็อาจไม่อยากพิมพ์ทุกคอลัมน์ทั้งหมด SELECT * ก็พอรับได้

  • อาจจะนอกประเด็นไปหน่อย แต่การที่ผู้ดูแล ปิด pull request ไปเฉย ๆ โดยไม่มีคอมเมนต์หรือการพูดคุยใด ๆ ถือเป็นแนวทางที่ยอมรับได้ไหม?
    ถามในฐานะคนที่เคยหรือพยายามจะมีส่วนร่วมกับ repository นี้เป็นครั้งคราว
    ตัวอย่าง: https://github.com/ben-n93/SQL-tips-and-tricks/pulls?q=is%3A...