ห้าวิธีในการปรับปรุงประสิทธิภาพ SQL ของคุณ
บทความนี้คัดลอกมาจากบทความ MSDN "ห้าวิธีในการปรับปรุงประสิทธิภาพ SQL" และเสนอวิธีปรับปรุงประสิทธิภาพการทำงานของแอปพลิเคชันบน SQL Server ขอแนะนำอย่างยิ่ง สำหรับระบบแอปพลิเคชันบางระบบที่มีการรับส่งข้อมูลสูง วิธีปรับปรุงและปรับปรุงคำสั่ง SQL มีความสำคัญมากและเป็นจุดก้าวหน้าที่ดี
*บทความนี้ส่วนใหญ่มีเนื้อหาดังต่อไปนี้ (หากคุณสนใจ โปรดไปที่ URL ต่อไปนี้โดยตรงเพื่ออ่านเอกสารภาษาจีนและอังกฤษฉบับสมบูรณ์):
1. ส่ง IDENTITY จาก INSERT
SELECT @@IDENTITY
2 มุมมองแบบฝังและตารางชั่วคราว
ตารางชั่วคราว – ตารางชั่วคราวใน tempdb อาจทำให้เกิดการสอบถามเพื่อดำเนินการ I/O จำนวนมากและการเข้าถึงดิสก์ และตารางชั่วคราวอาจใช้ทรัพยากรจำนวนมาก
มุมมองแบบอินไลน์ - ใช้มุมมองแบบอินไลน์แทนตารางชั่วคราว มุมมองแบบอินไลน์เป็นเพียงแบบสอบถามที่สามารถเข้าร่วมในส่วนคำสั่ง FROM ได้ หากคุณเพียงแค่ต้องรวมข้อมูลเข้ากับคำค้นหาอื่นๆ คุณสามารถลองใช้มุมมองแบบอินไลน์เพื่อประหยัดทรัพยากรได้
3. หลีกเลี่ยง LEFT JOIN และ NULL
LEFT JOIN ต้องใช้ทรัพยากรมากเนื่องจากมีข้อมูลที่ตรงกับข้อมูล NULL (ไม่มีอยู่จริง) ในบางกรณีเป็นสิ่งที่หลีกเลี่ยงไม่ได้ แต่ค่าใช้จ่ายอาจสูงมาก LEFT JOIN ใช้ทรัพยากรมากกว่า INNER JOIN ดังนั้นหากคุณสามารถเขียนแบบสอบถามใหม่เพื่อไม่ให้ใช้ LEFT JOIN ใด ๆ คุณจะได้รับรางวัลที่ดีมาก
เทคนิคหนึ่งในการเร่งการสืบค้นที่ใช้ LEFT JOIN คือการสร้างประเภทข้อมูล TABLE โดยแทรกแถวทั้งหมดในตารางแรก (ตารางทางด้านซ้ายของ LEFT JOIN) จากนั้นอัปเดตประเภทข้อมูล TABLE ด้วยค่าจาก โต๊ะที่สอง เทคนิคนี้เป็นกระบวนการสองขั้นตอน แต่สามารถประหยัดเวลาได้มากเมื่อเทียบกับ LEFT JOIN มาตรฐาน กฎที่ดีคือการลองใช้เทคนิคต่างๆ ที่หลากหลาย และบันทึกเวลาที่จำเป็นสำหรับแต่ละเทคนิคจนกว่าคุณจะได้รับข้อความค้นหาที่มีประสิทธิภาพดีที่สุดสำหรับแอปพลิเคชันของคุณ
DECLARE @tblMonths TABLE (sMonth VARCHAR(7))
4 การใช้ผลิตภัณฑ์คาร์ทีเซียนอย่างยืดหยุ่น
ฉันจะอธิบายรายละเอียดอย่างละเอียดเกี่ยวกับเทคนิคนี้และสนับสนุนการใช้ผลิตภัณฑ์คาร์ทีเซียนในบางกรณี ด้วยเหตุผลบางประการ ผลิตภัณฑ์คาร์ทีเซียน (CROSS JOIN) จึงมีช่องโหว่จำนวนมาก และนักพัฒนามักถูกเตือนไม่ให้ใช้เลย ในหลายกรณี พวกเขาใช้ทรัพยากรมากเกินไปเพื่อที่จะใช้อย่างมีประสิทธิภาพ แต่เช่นเดียวกับเครื่องมืออื่นๆ ใน SQL สิ่งเหล่านี้สามารถมีคุณค่าได้หากใช้อย่างถูกต้อง
หนึ่งในโค้ดตัวอย่างมีค่าดังต่อไปนี้:
-- สินค้าคาร์ทีเซียนส่งคืนลูกค้าทั้งหมดทุกเดือน โดยทั่วไปผลคูณคาร์ทีเซียนจะคูณตารางแรกด้วยตารางที่สอง ทำให้เกิดชุดแถวที่มีจำนวนแถวในตารางแรกคูณด้วยจำนวนแถวในตารางที่สอง ดังนั้น ผลิตภัณฑ์คาร์ทีเซียนจะส่งกลับ 12 (ทุกเดือน) * 81 (ลูกค้าทั้งหมด) = 972 แถวไปที่ตาราง @tblFinal ขั้นตอนสุดท้ายคือการอัปเดตตาราง @tblFinal ด้วยยอดรวมยอดขายรายเดือนสำหรับลูกค้าแต่ละรายในช่วงวันที่นี้ และเลือกชุดแถวสุดท้าย
ประกาศตาราง @tblMonths (sMonth VARCHAR (7))
ประกาศตาราง @tblCustomers (รหัสลูกค้า CHAR (10)
ชื่อบริษัท วาร์ชาร์(50)
ชื่อผู้ติดต่อ วาร์ชาร์(50))
ประกาศ @tblFinal TABLE (sMonth VARCHAR(7)
รหัสลูกค้า CHAR(10)
ชื่อบริษัท วาร์ชาร์(50)
ชื่อผู้ติดต่อ วาร์ชาร์(50)
mSalesMONEY)
ประกาศ @dtStartDate DATETIME
@dtEndDate DATETIME,
@dtDate DATETIME,
@i ชุดจำนวนเต็ม
@dtEndDate = '5/5/1997'
SET @dtEndDate = DATEADD(DD, -1, CAST(CAST((MONTH(@dtEndDate) + 1) AS
VARCHAR(2)) + '/01/' + นักแสดง(YEAR(@dtEndDate) AS VARCHAR(4)) + ' 23:59:59' AS DATETIME))
SET @dtStartDate = DATEADD(MM, -1 * 12, @dtEndDate)
-- นำเดือนทั้งหมดมาไว้ในตารางแรก
SET @i = 0
ในขณะที่ (@i <12)
เริ่ม
SET @dtDate = DATEADD(มม., -1 * @i, @dtEndDate)
แทรกลงใน @tblMonths เลือกนักแสดง (ปี (@dtDate) AS VARCHAR (4)) + '-' +
กรณี
เมื่อเดือน(@dtDate) < 10
จากนั้น '0' + นักแสดง (เดือน (@dtDate) เป็น VARCHAR (2))
นักแสดงอื่น(เดือน(@dtDate) AS VARCHAR(2))
สิ้นสุดเป็นเดือน
SET @i = @i + 1
END
-- นำลูกค้าทั้งหมดที่มียอดขายในช่วงเวลานั้นมาไว้ในตาราง "y"
ใส่เข้าไปใน @tblCustomers
เลือกความแตกต่าง
ค.รหัสลูกค้า
ค.ชื่อบริษัท
ค.ชื่อผู้ติดต่อ
จากลูกค้าค
คำสั่งซื้อ INNER JOIN o ON c.CustomerID = o.CustomerID
โดยที่ o.OrderDate ระหว่าง @dtStartDate และ @dtEndDate
INSERT INTO @tblFinal
เลือก m.sMonth
ค.รหัสลูกค้า
ค.ชื่อบริษัท
ค.ชื่อผู้ติดต่อ
0
จาก @tblMonths m CROSS JOIN @tblCustomers c
อัปเดต @tblFinal SET
mSales = mydata.mSales
จาก @tblFinal f เข้าร่วมภายใน
-
เลือก c.รหัสลูกค้า
นักแสดง(ปี(o.วันที่สั่งซื้อ) AS VARCHAR(4)) + '-' +
กรณีเมื่อเดือน(o.วันที่สั่งซื้อ) < 10
จากนั้น '0' + นักแสดง (เดือน (o.OrderDate) AS VARCHAR (2))
นักแสดงอื่น (เดือน (o. วันที่สั่งซื้อ) AS VARCHAR (2))
สิ้นสุดเป็นเดือน
SUM(od.Quantity * od.UnitPrice) AS mSales
จากลูกค้าค
คำสั่งซื้อ INNER JOIN o ON c.CustomerID = o.CustomerID
INNER JOIN [รายละเอียดการสั่งซื้อ] od ON o.OrderID = od.OrderID
โดยที่ o.OrderDate ระหว่าง @dtStartDate และ @dtEndDate
จัดกลุ่มตาม
ค.รหัสลูกค้า
นักแสดง(ปี(o.วันที่สั่งซื้อ) AS VARCHAR(4)) + '-' +
กรณีเมื่อเดือน(o.วันที่สั่งซื้อ) < 10
จากนั้น '0' + นักแสดง (เดือน (o.OrderDate) AS VARCHAR (2))
นักแสดงอื่น (เดือน (o. วันที่สั่งซื้อ) AS VARCHAR (2))
จบ
) mydata บน f.CustomerID = mydata.CustomerID และ f.sMonth =
mydata.sเดือน
เลือก f.sMonth
ฉ. รหัสลูกค้า
ฉ.ชื่อบริษัท
ฉ.ชื่อผู้ติดต่อ
f.mSales
จาก @tblFinal f
สั่งซื้อโดย
ฉ.ชื่อบริษัท
ฉ.sเดือน
5. หยิบส่วนที่ขาดมาชดเชยส่วนที่ขาด
ต่อไปนี้เป็นเทคนิคทั่วไปอื่นๆ ที่สามารถช่วยปรับปรุงประสิทธิภาพของคำสั่ง SQL ได้ สมมติว่าคุณต้องการจัดกลุ่มพนักงานขายทั้งหมดตามภูมิภาคและรวมยอดขายย่อย แต่คุณต้องการให้พนักงานขายเหล่านั้นทำเครื่องหมายว่าใช้งานอยู่ในฐานข้อมูลเท่านั้น คุณสามารถจัดกลุ่มพนักงานขายตามภูมิภาคและกำจัดผู้ที่ไม่ได้ใช้งานอยู่โดยใช้ส่วนคำสั่ง HAVING หรือคุณสามารถทำสิ่งนี้ได้ในส่วนคำสั่ง WHERE การทำเช่นนี้ในส่วนคำสั่ง WHERE จะช่วยลดจำนวนแถวที่ต้องจัดกลุ่ม ดังนั้นจึงมีประสิทธิภาพมากกว่าการทำในส่วนคำสั่ง HAVING การกรองตามเงื่อนไขของแถวในส่วนคำสั่ง HAVING จะบังคับให้แบบสอบถามจัดกลุ่มข้อมูลที่จะถูกลบออกในส่วนคำสั่ง WHERE
เคล็ดลับประสิทธิภาพอีกประการหนึ่งคือการใช้คีย์เวิร์ด DISTINCT เพื่อค้นหารายงานแยกสำหรับแถวข้อมูล แทนที่จะใช้คำสั่งย่อย GROUP BY ในกรณีนี้ SQL ที่ใช้คีย์เวิร์ด DISTINCT จะมีประสิทธิภาพมากกว่า โปรดใช้ GROUP BY เมื่อคุณต้องการคำนวณฟังก์ชันรวมเท่านั้น (SUM, COUNT, MAX ฯลฯ) นอกจากนี้ หากข้อความค้นหาของคุณส่งคืนแถวที่ไม่ซ้ำกันเสมอ อย่าใช้คำสำคัญ DISTINCT ในกรณีนี้ คีย์เวิร์ด DISTINCT จะเพิ่มโอเวอร์เฮดของระบบเท่านั้น
-
URL ภาษาจีน:
http://www.microsoft.com/china/MSDN/library/data/sqlserver/FiveWaystoRevupYourSQLPerformanCE.mspx
URL ภาษาอังกฤษ:
http://msdn.microsoft.com/msdnmag/issues/02/07/DataPoints/