सम्मिलित तालिका के स्तंभ द्वारा आदेश देने पर प्रदर्शन में सुधार

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और जिनके लिए Lookupएक गैर-क्लस्टर इंडेक्स है Name।

मैं एक पृष्ठांकित क्वेरी चला रहा हूँ जहाँ मैं परिणामों में लुकअप मान शामिल करना चाहता हूँ: -

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 सर्वर मुझे दृश्य को अनुक्रमणित करने की अनुमति नहीं देगा क्योंकि इसमें एक LEFT JOIN है जिसकी मुझे आवश्यकता है क्योंकि LookupIdNULL हो सकता है और यदि मैं INNER JOIN का उपयोग करता हूं तो उन रिकॉर्डों को बाहर रखा जाएगा।

क्या इस स्थिति को अनुकूलित करने का कोई तरीका है?

संपादित करें

रोब फ़ार्ले का जवाब (धन्यवाद!) महान है और सवाल के लिए पूरी तरह से काम करता है जैसा कि मैंने मूल रूप से पूछा था, जिसमें मुझे लगा कि मैं एक ही तालिका में शामिल हो रहा हूं।

जैसा कि यह है, मेरे पास ऐसी कई सारणियाँ हैं और मैं उस समाधान का उपयोग करने के लिए INNER JOINs का उपयोग करके सभी को समेटने में असमर्थ था।

फिलहाल मैंने लुकअप टेबलों में "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ताकि जॉइन के दाईं ओर हमेशा एक मैच हो।

फिर मैं आवश्यकतानुसार उस दृश्य पर अनुक्रमणिकाएँ बना सकता हूँ।

इसके अलावा, जैसा कि मैं एंटरप्राइज का उपयोग नहीं कर रहा हूं, मैंने पाया कि मुझे NOEXPANDयह सुनिश्चित करने के लिए संकेत का उपयोग करने की आवश्यकता है कि उन इंडेक्स का उपयोग किया जाता है:

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

Answer 1

चलिए उस पहली क्वेरी के बारे में सोचकर शुरू करते हैं।

आप पेरेंट और लुकअप के बीच शामिल हो रहे हैं, लेकिन यह एक बाहरी जुड़ाव है, इसलिए पेरेंट्स को कभी भी नतीजों से दूर नहीं किया जाता है। मैं अनुमान लगाने जा रहा हूं कि लुकअप। डी अद्वितीय है, इसलिए, कोई भी अभिभावक के पास एक से अधिक लुकअप होने की संभावना नहीं है।

इसलिए, यदि हम OFFSET क्लॉज नहीं रखते हैं, तो 50000 वीं पंक्ति पेरेंट (Parent.Id द्वारा आदेशित) परिणाम में 50000 वीं पंक्ति होने वाली है।

इसलिए, क्वेरी ऑफसेट के लिए 50000 पंक्तियों को स्थानांतरित कर सकती है, अगली 50 पंक्तियों को देख सकती है और लुकअप तालिका में शामिल होने के लिए इसका उपयोग कर सकती है। इससे कोई फर्क नहीं पड़ता कि ज्वाइन में कुछ नहीं मिला, यह एक बायाँ बाहरी हिस्सा है और यह सिर्फ NULL को लौटाएगा।

यदि आप जनक में एक अलग कॉलम द्वारा ऑर्डर करते हैं, और यह अनुक्रमित है, तो यह उन 50000 पंक्तियों को बस जल्दी से आगे बढ़ा सकता है।

अब दूसरी क्वेरी पर विचार करते हैं।

आप चाहते हैं कि 50000 पंक्तियाँ जिन्हें आप अनदेखा करें (ऑफसेट द्वारा) पहले 50000 में शामिल होने के परिणामों के आधार पर हो। उन 50000 पंक्तियों में कुछ ऐसे शामिल हो सकते हैं जो NULL हैं, जहाँ Parent.LookupId मान लुकअप तालिका में मौजूद नहीं है। यहां तक ​​कि अगर आपके पास Parent.LookupId पर एक अच्छा सूचकांक है, तो आपको संभवतः अधिकांश पंक्तियों को शामिल करना होगा, क्योंकि जब तक आप 50050 पंक्तियां नहीं पाते हैं जो सफलतापूर्वक शामिल नहीं होती हैं, तो आपको चलते रहना होगा। यहां तक ​​कि पहली क्वेरी में शामिल होने वाली 50 पंक्तियों की तुलना में भी 50050 अधिक है।

अब, यदि आपके पास कोई विदेशी कुंजी है तो चीजें थोड़ी भिन्न हो सकती हैं। फिर, SQL इंजन को यह जानना चाहिए कि यदि उसका मूल्य है, तो लुकअप.नाम शून्य नहीं होगा। तो यह सैद्धांतिक रूप से उन लोगों को खोजने से शुरू हो सकता है जो अशक्त हैं, यह देखने के लिए कि क्या उनमें से 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;

उम्मीद है कि आपकी योजना में एक मर्ज ज्वाइन (कॉनसेनटेशन) ऑपरेटर शामिल होगा, ताकि यह केवल उन पंक्तियों को खींचे जो इसे इंडेक्स स्कैन से अनुक्रमित दृश्य (नाम क्रम में) और इंडेक्स सीक ऑन पेरेंट (लुकअप के लिए) से चाहिए।