การปรับปรุงประสิทธิภาพเมื่อสั่งซื้อตามคอลัมน์ของตารางที่เข้าร่วม

Question

ฉันมีตารางหลักที่มีคีย์นอกสำหรับตารางการค้นหา (ตัวอย่างง่าย):

CREATE TABLE [dbo].[Parent] (
    [Id] [uniqueidentifier] NOT NULL,
    [LookupId] [uniqueidentifier] NULL
)

CREATE TABLE [dbo].[Lookup] (
    [Id] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](64) NOT NULL
)

ในกรณีนี้Parentตารางมีมากกว่า 10 ล้านแถวและLookupตารางมีประมาณ 5,000 Parentการนำไปใช้จริงมีการอ้างอิงคีย์ต่างประเทศหลายรายการดังกล่าวไปยังตารางอื่น ๆ และแต่ละคอลัมน์อาจมีค่า NULL

ตารางทั้งสองตัวอย่างเช่นมีการจัดทำดัชนีคลัสเตอร์ที่ไม่ซ้ำกันของพวกเขาสำหรับIdคอลัมน์Parentมีดัชนีที่ไม่ใช่คลัสเตอร์สำหรับLookupIdและมีดัชนีที่ไม่ใช่คลัสเตอร์สำหรับLookupName

ฉันกำลังเรียกใช้แบบสอบถามแบบเพจที่ฉันต้องการรวมค่าการค้นหาในผลลัพธ์: -

SELECT
    P.Id,
    L.Name
FROM Parent P
LEFT JOIN Lookup L ON P.LookupId = L.Id 
ORDER BY P.Id
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY

P.LookupIdวิ่งนี้ได้อย่างรวดเร็วเช่นเดียวกับการสั่งซื้อโดย

อย่างไรก็ตามหากฉันพยายามเรียงลำดับตามName(หรือแม้กระทั่งL.Id) ข้อความค้นหาจะทำงานช้าลงมาก:

SELECT
    P.Id,
    L.Name
FROM Parent P
LEFT JOIN Lookup L ON P.LookupId = L.Id 
ORDER BY L.Name
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY

แผนการสืบค้นสำหรับแบบสอบถามที่สองอยู่ที่นี่: https://www.brentozar.com/pastetheplan/?id=Sk3SIOvMD

คำถามอื่น ๆ ที่ดูเหมือนจะเกี่ยวข้องกับการเรียงลำดับตามคอลัมน์ในตารางแรกซึ่งสามารถแก้ไขได้โดยใช้ดัชนีที่เหมาะสม

ฉันพยายามสร้างมุมมองที่จัดทำดัชนีสำหรับแบบสอบถามนี้อย่างไรก็ตาม SQL Server ไม่อนุญาตให้ฉันจัดทำดัชนีมุมมองเนื่องจากมีการเข้าร่วมทางซ้ายซึ่งฉันต้องการเนื่องจากLookupIdอาจเป็น NULL และถ้าฉันใช้การเข้าร่วมภายในระเบียนเหล่านั้นจะถูกแยกออก

มีวิธีเพิ่มประสิทธิภาพสถานการณ์นี้หรือไม่?

แก้ไข

คำตอบของ Rob Farley (ขอบคุณ!) นั้นยอดเยี่ยมและทำงานได้อย่างสมบูรณ์แบบสำหรับคำถามตามที่ฉันถามในตอนแรกซึ่งฉันบอกเป็นนัยว่าฉันเข้าร่วมโต๊ะเดียว

ตามที่เป็นอยู่ฉันมีหลายตารางและฉันไม่สามารถปรับยอดทั้งหมดโดยใช้ INNER JOIN เพื่อใช้โซลูชันนั้น

ในขณะนี้ฉันได้แก้ไขปัญหานี้โดยการเพิ่มแถว "NULL" ในตารางการค้นหาเพื่อให้ฉันสามารถใช้ INNER JOIN ได้โดยไม่สูญเสียแถวใด ๆ ทางด้านซ้าย

ในกรณีของฉันฉันใช้uniqueidentifierข้อมูลประจำตัวดังนั้นฉันจึงสร้างมุมมองที่จัดทำดัชนีดังนี้:

CREATE VIEW [dbo].[ParentView]
WITH SCHEMABINDING
AS
SELECT
    P.Id,
    L.Name
FROM [dbo].Parent P
INNER JOIN [dbo].Lookup L ON ISNULL(P.LookupId, '00000000-0000-0000-0000-000000000000') = L.Id

จากนั้นฉันเพิ่มแถวในLookupตารางด้วยค่า00000000-0000-0000-0000-000000000000สำหรับIdดังนั้นจึงมีการจับคู่ทางด้านขวาของการรวมเสมอ

จากนั้นฉันสามารถสร้างดัชนีในมุมมองนั้นได้ตามต้องการ

นอกจากนี้เนื่องจากฉันไม่ได้ใช้ Enterprise ฉันพบว่าฉันจำเป็นต้องใช้NOEXPANDคำใบ้เพื่อให้แน่ใจว่ามีการใช้ดัชนีเหล่านั้น:

SELECT *
FROM [ParentView]
WITH (NOEXPAND)
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

Answer 1

เริ่มต้นด้วยการคิดถึงคำค้นหาแรก

คุณกำลังเข้าร่วมระหว่าง Parent และ Lookup แต่เป็นการรวมภายนอกดังนั้นผู้ปกครองจะไม่ถูกลบออกจากผลลัพธ์ ฉันจะเดาว่า Lookup.Id นั้นไม่เหมือนใครดังนั้นจึงไม่มีผู้ปกครองคนใดที่จะมีการค้นหาหลายรายการที่เข้าร่วม

ดังนั้นแถวที่ 50000 ใน Parent (เรียงลำดับโดย Parent.Id) จะเป็นแถวที่ 50000 ในผลลัพธ์ถ้าเราไม่มีประโยค OFFSET

ดังนั้นคิวรีสามารถเลื่อนผ่าน 50000 แถวสำหรับออฟเซ็ตดูที่ 50 แถวถัดไปและใช้สิ่งนี้เพื่อเข้าร่วมกับตารางการค้นหา ไม่สำคัญว่าการเข้าร่วมจะไม่พบอะไรเลยเป็นการรวมภายนอกด้านซ้ายและจะคืนค่าเป็น NULL

หากคุณเรียงลำดับตามคอลัมน์อื่นในพาเรนต์และมีการจัดทำดัชนีไว้ก็สามารถย้ายผ่าน 50000 แถวเหล่านั้นได้อย่างรวดเร็ว

ทีนี้ลองพิจารณาแบบสอบถามที่สอง

คุณต้องการให้ 50000 แถวที่คุณละเว้น (โดยออฟเซ็ต) เป็น 50000 แรกตามผลลัพธ์ของการรวม แถว 50000 เหล่านั้นอาจรวมถึงบางแถวที่เป็น NULL โดยที่ค่า Parent.LookupId ไม่มีอยู่ในตารางการค้นหา แม้ว่าคุณจะมีดัชนีที่ดีใน Parent.LookupId คุณอาจต้องเกี่ยวข้องกับแถวส่วนใหญ่เพราะถ้าคุณไม่พบ 50050 แถวที่เข้าร่วมไม่สำเร็จคุณจะต้องดำเนินการต่อไป แม้แต่ 50050 ก็เป็นมากกว่า 50 แถวที่คุณเข้าร่วมในแบบสอบถามแรก

ตอนนี้ถ้าคุณมีคีย์ต่างประเทศอยู่แล้วสิ่งต่าง ๆ อาจแตกต่างกันเล็กน้อย จากนั้นเอ็นจิ้น SQL ควรรู้ว่าหากมีค่าเลย Lookup.Name จะไม่เป็นโมฆะ ดังนั้นในทางทฤษฎีจึงสามารถเริ่มต้นด้วยการค้นหาสิ่งที่เป็นโมฆะเพื่อดูว่ามี 50000 หรือไม่ แต่นั่นก็ยังค่อนข้างยืดเยื้อและเอ็นจิ้น SQL ไม่น่าจะสร้างแผนเช่นนี้ได้

แต่คุณทำได้

ดังนั้นเพื่อแก้ปัญหาประสิทธิภาพของแบบสอบถามที่สองฉันจะทำบางอย่าง

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

แต่คุณจะต้องมีสิ่งที่ Parent.LookupID เป็นโมฆะยกเว้นสำหรับสิ่งเหล่านี้คุณไม่จำเป็นต้องเข้าร่วมเลย

หากคุณทำ UNION ALL ในสองชุดนี้ (และอาจรวมคอลัมน์ค่าคงที่ในทั้งสองเพื่อให้แน่ใจว่าแถว NULL ปรากฏก่อนแถว NOT NULL ตามลำดับของคุณโดย) คุณควรจะเห็นการปรับปรุงบางอย่าง

สิ่งนี้:

SELECT ID, Name
FROM 
(
  SELECT i.ID, i.Name, 2 as SetNumber
  FROM dbo.MyIndexedView i
  UNION ALL
  SELECT p.ID, NULL, 1 as SetNumber
  FROM dbo.Parent p
  WHERE p.LookupID IS NULL
) u
ORDER BY u.SetNumber, u.Name
OFFSET 50000 ROWS FETCH NEXT 50 ROWS ONLY;

หวังว่าแผนของคุณจะรวมตัวดำเนินการ Merge Join (Concatenation) เพื่อให้ดึงเฉพาะแถวที่ต้องการจากการสแกนดัชนีในมุมมองที่จัดทำดัชนี (ตามลำดับชื่อ) และ Index Seek on Parent (สำหรับ LookupIDs)