Meerdere volledige indexscans die worden uitgevoerd wanneer een voorwaardelijke join-instructie wordt gebruikt

Ik heb problemen bij het opvragen van een tabel met rijen met een bovenliggende/onderliggende relatie. Bij het opwerken van een vereenvoudigd voorbeeld, realiseerde ik me dat het stackexchange-schema erg op elkaar lijkt.

Stel je voor dat ik de stackoverflowposten-tabel doorzoek via stackexchange data explorer. Ik probeer een subset van alle berichten en de bijbehorende antwoorden te krijgen.

Zie http: //data.stackexchange. com/stackoverflow/query/121981/a-subset-of-questions-en-associated-antwoorden voor voorbeeldquery's.

De subset van berichten wordt gedefinieerd in een weergave, die een redelijk ingewikkeld en duur queryplan heeft. In het onderstaande voorbeeld is het vereenvoudigd om eenvoudig de bovenste twee rijen te selecteren.

De eerste manier, met behulp van een vakbond:

with ExpensiveView as (select top 2 ID from Posts order by ID)

select Posts.*
from ExpensiveView
left outer join Posts
  ON ExpensiveView.Id = Posts.Id 

union all

select Posts.*
from ExpensiveView
left outer join Posts
  ON ExpensiveView.Id = Posts.ParentId

Ik zou dit graag willen vermijden, omdat ExpensiveView twee keer wordt geëvalueerd. Uiteraard geen probleem voor de vereenvoudigde versie hierboven, maar veroorzaakt problemen voor een complexere versie.

De tweede manier, met behulp van een enkele selectie met een conditionele join-clausule:

with ExpensiveView as (select top 2 ID from Posts order by ID)

select Posts.*
from ExpensiveView
left outer join Posts
  ON ExpensiveView.Id = Posts.Id or ExpensiveView.Id = Posts.ParentId

Dit voorkomt dat ExpensiveView tweemaal wordt geëvalueerd, maar veroorzaakt een belachelijk grote geclusterde index-scan. Het lijkt de hele index te scannen per ID in ExpensiveView (dus 2 * 14977623 = ~ 30 miljoen rijen). Dit gaat erg langzaam.

Twee vragen

Waarom resulteert de conditionele join in de tweede query in zo'n grote indexscan?

Is er een manier om de resultaten te krijgen waarnaar ik op zoek zonder dat ExpensiveView meer dan eens wordt geëvalueerd?

3
ja ru de
Raadpleeg Heeft een 'OF' in een INNER JOIN-conditie een slecht idee? . Enige reden waarom u ExpensiveView niet in een tabel #temp kunt plaatsen?
toegevoegd de auteur Martin Smith, de bron
Raadpleeg Heeft een 'OF' in een INNER JOIN-conditie een slecht idee? . Enige reden waarom u ExpensiveView niet in een tabel #temp kunt plaatsen?
toegevoegd de auteur Martin Smith, de bron
Ook ExpensiveView.Id IN (Posts.Id, Posts.ParentId) kan niet worden voldaan door een indexzoekopdracht op Posts . In principe zou het kunnen doen twee index zoekt op Posts.Id en vervolgens op Posts.ParentId vervolgens alle dubbele rijen verwijderen die overeenkwamen met beide zoekt, hoewel ik niet zeker weet SQL Server zal geef dat plan ooit in de praktijk. (Komt u mij voor dat u waarschijnlijk iets soortgelijks zou kunnen simuleren met BUITENBRENGEN )
toegevoegd de auteur Martin Smith, de bron
Ook ExpensiveView.Id IN (Posts.Id, Posts.ParentId) kan niet worden voldaan door een indexzoekopdracht op Posts . In principe zou het kunnen doen twee index zoekt op Posts.Id en vervolgens op Posts.ParentId vervolgens alle dubbele rijen verwijderen die overeenkwamen met beide zoekt, hoewel ik niet zeker weet SQL Server zal geef dat plan ooit in de praktijk. (Komt u mij voor dat u waarschijnlijk iets soortgelijks zou kunnen simuleren met BUITENBRENGEN )
toegevoegd de auteur Martin Smith, de bron
Hoe presteert dit tegen uw gegevens? met ExpensiveView als (selecteer top 2 ID van Posts sorteer op ID) selecteer Posts. * van ExpensiveView OUTER APPLY (SELECT * FROM Posts WHERE Id = ExpensiveView.Id UNION SELECT * FROM Posts WHERE ParentId = ExpensiveView.Id) Berichten
toegevoegd de auteur Martin Smith, de bron
Hoe presteert dit tegen uw gegevens? met ExpensiveView als (selecteer top 2 ID van Posts sorteer op ID) selecteer Posts. * van ExpensiveView OUTER APPLY (SELECT * FROM Posts WHERE Id = ExpensiveView.Id UNION SELECT * FROM Posts WHERE ParentId = ExpensiveView.Id) Berichten
toegevoegd de auteur Martin Smith, de bron
Het gekoppelde antwoord zegt dat wanneer een of in een join-component wordt gebruikt, de join niet kan worden samengevoegd of hash en als zodanig een lus-join wordt - wat impliceert dat de lusverbinding de oorzaak is van de grote-tafelscan. Maar als ik een van de join-clausules verwijder, blijft de lus-join over, maar wordt de grote scan niet uitgevoerd.
toegevoegd de auteur John, de bron
Het gekoppelde antwoord zegt dat wanneer een of in een join-component wordt gebruikt, de join niet kan worden samengevoegd of hash en als zodanig een lus-join wordt - wat impliceert dat de lusverbinding de oorzaak is van de grote-tafelscan. Maar als ik een van de join-clausules verwijder, blijft de lus-join over, maar wordt de grote scan niet uitgevoerd.
toegevoegd de auteur John, de bron
Wat betreft het gebruik van een tijdelijke tabel maak ik me een beetje zorgen over de overheadkosten. Dit is een query die redelijk vaak wordt uitgevoerd (tot enkele keren per seconde).
toegevoegd de auteur John, de bron
Wat betreft het gebruik van een tijdelijke tabel maak ik me een beetje zorgen over de overheadkosten. Dit is een query die redelijk vaak wordt uitgevoerd (tot enkele keren per seconde).
toegevoegd de auteur John, de bron
Dat presteert iets beter, bedankt. Je vorige reactie gaf me de inspiratie om het een beetje te tweaken. De unie binnen de buitenste toepassing is niet nodig - een enkele selectie met een 'of' in de where-component genereert een plan met twee afzonderlijke indexopvragingen. met ExpensiveView as (selecteer top 2 ID van Posts sorteer op ID) selecteer Posts. * van ExpensiveView OUTER APPLY (SELECT * FROM Posts WHERE Id = ExpensiveView.Id of ParentId = ExpensiveView.Id) Posts . Hartelijk dank voor de hulp.
toegevoegd de auteur John, de bron
Dat presteert iets beter, bedankt. Je vorige reactie gaf me de inspiratie om het een beetje te tweaken. De unie binnen de buitenste toepassing is niet nodig - een enkele selectie met een 'of' in de where-component genereert een plan met twee afzonderlijke indexopvragingen. met ExpensiveView as (selecteer top 2 ID van Posts sorteer op ID) selecteer Posts. * van ExpensiveView OUTER APPLY (SELECT * FROM Posts WHERE Id = ExpensiveView.Id of ParentId = ExpensiveView.Id) Posts . Hartelijk dank voor de hulp.
toegevoegd de auteur John, de bron

2 antwoord

probeer dit

with
ExpensiveView as (select top 2 ID from Posts order by ID),
CTE_Posts as (
    select *, NP.Id as New_Post_ID
    from Posts as P
        outer apply (select P.Id union all select P.ParentId) as NP
)
select
    P.*
from ExpensiveView as E
    left outer join CTE_Posts as P on P.New_Post_ID = E.ID
0
toegevoegd

probeer dit

with
ExpensiveView as (select top 2 ID from Posts order by ID),
CTE_Posts as (
    select *, NP.Id as New_Post_ID
    from Posts as P
        outer apply (select P.Id union all select P.ParentId) as NP
)
select
    P.*
from ExpensiveView as E
    left outer join CTE_Posts as P on P.New_Post_ID = E.ID
0
toegevoegd