SQLShack (Nederlands)

Inleiding

hebt u ooit een complexe query geschreven met behulp van Common Table Expressions (CTE ‘ s) alleen om teleurgesteld te zijn door de prestaties? Heb je de CTE de schuld gegeven? Dit artikel kijkt naar dat probleem om te laten zien dat het een beetje dieper dan een bepaalde syntaxis keuze en biedt een aantal tips over hoe de prestaties te verbeteren.

CTEs Revisited

gemeenschappelijke Tabeluitdrukkingen verschenen voor het eerst in SQL Server 2005., In combinatie met het WITH statement, bieden ze een manier om een ingewikkelde hiërarchie van sub queries te reorganiseren in een eenvoudig te lezen lineaire vorm. CTE ‘ s maken ook recursief gedrag mogelijk, maar dat valt buiten het bereik van dit artikel. Citeren uit boeken Online, een CTE:

specificeert een tijdelijke benoemde result set, bekend als een common table expression (CTE). Dit is afgeleid van een eenvoudige query en gedefinieerd binnen het uitvoeringsbereik van een enkele statement selecteren, invoegen, bijwerken of verwijderen. Deze clausule kan ook worden gebruikt in een Create VIEW statement als onderdeel van zijn defining SELECT statement., Een gemeenschappelijke tabeluitdrukking kan verwijzingen naar zichzelf bevatten. Dit wordt aangeduid als een recursieve gemeenschappelijke tabeluitdrukking.

in het Algemeen Stel je een query op met CTE ’s als volgt:

je kunt een willekeurig aantal CTE’ s hebben en de uiteindelijke instructie (die elk geldig DML kan zijn) kan alle, sommige of geen ervan gebruiken., Er van uitgaande dat er slechts twee, als boven, dit kan worden herschreven met behulp van subquery ‘ s:

1
2
3
4
5
6
7
8
9
10

SELECTEREN …
van (
selecteren …
) CTE1
JOIN (
SELECT …
) CTE2
op cte1.,<join column> = cte2.<join column>

Er is geen verschil tussen de eerste query en de tweede voor zover het wordt ontleed, gecompileerd en uitgevoerd. De uitvoeringsplannen zullen identiek zijn. Het verschil zit hem in leesbaarheid. Wanneer subqueries complex zijn, kan het makkelijker zijn om ze eruit te trekken en ze in CTE ‘ s te zetten en ze vervolgens te combineren in een met statement.

prestaties?,

misschien hebt u een CTE geschreven of debuggen die langzaam lijkt te lopen. Of, misschien heb je berichten gezien in sommige SQL Server forum klagen dat sommige CTE langzaam loopt. Wat de klager echt suggereert is dat een of andere manier wanneer SQL Server compileert een query gebouwd van CTEs het doet het anders dan dezelfde query met behulp van sub-queries en een of andere manier doet een slechter werk van het. Dat is gewoon niet het geval., In feite, als het Forum poster had geformatteerd de query in (eventueel geneste) subqueries en niet gebruik maken van de met … CTE structuur, dezelfde prestatiekenmerken zou ongetwijfeld zijn waargenomen.

dus, waarom draait het zo langzaam! Goede vraag. Om te proberen te begrijpen waarom, laten we eens kijken naar een eenvoudig voorbeeld:

wat doet dit? Na de tabel variabele declaratie, het vult de tabel met de eerste een miljoen rijen van het Cartesiaanse product van de sys.all_columns-weergave., Afhankelijk van hoe groot de database is, kunnen er duizenden, tienduizenden en misschien nog veel meer rijen in die tabel staan. Een miljoen rijen in het resultaat is genoeg voor onze doeleinden. Na het vullen van de tabel variabele, het script gaat verder in A met statement met behulp van een CTE die gewoon rijen van de tabel variabele trekt. Ziet er niet slecht uit, toch? Voordat u het uitvoeren op een aantal handige productie-systeem, hoewel, Lees verder.

een beetje wiskunde zal je helpen te zien wat er op het spel staat., Als ik gewoon lopen:

1
2
3

SELECT COUNT(*)FROM sys.all_columns;

Ik krijg een resultaat van 7364 op mijn systeem – en dat is gewoon LocalDB! Aangezien dat wordt samengevoegd met het Cartesiaanse product van dezelfde mening, kan dat resulteren in 7,364 miljard rijen. Ik laat het lopen op mijn eigen (niet-productie!) systeem., Ik moest het stoppen voordat het kon voltooien. Ik geef toe dat ik een beetje ongeduldig ben!

nieuwsgierig naar wat er achter het gordijn gebeurt, liet ik het uitvoeringsplan zien. Het ziet er als volgt uit:

merk op dat een geneste Loops operator is geselecteerd en dat het Geschatte aantal rijen afkomstig van mijn tabel variabele 1 is! Ook al heb ik het net geladen met 1 miljoen rijen! Hoe kan dit? De trieste waarheid is dat SQL Server geen statistieken voor tabel variabelen bijhoudt, dus op het moment dat de query wordt gecompileerd, weet het niet hoe rijen er echt zijn., Als ik maar één rij had, zou een geneste lus prima zijn! Hier is hoe dat wordt gedefinieerd in BOL:

de geneste lussen join, ook geneste iteratie genoemd, gebruikt één join input als de buitenste invoertabel (getoond als de bovenste invoer in het grafische uitvoerplan) en één als de binnenste (onderste) invoertabel. De buitenste lus verbruikt de buitenste invoertabel rij voor rij. De binnenste lus, uitgevoerd voor elke buitenste rij, zoekt naar overeenkomende rijen in de binnenste invoertabel.

dus, voor elk van de miljoen rijen in mijn tabel variabele, zal SQL een scan uitvoeren van de objectcatalogusweergave. Niet! Goed!,

mijn tweede poging vervangt de tabel variabele door een temp tabel. Het script is identiek, behalve dat het begint met:

1
2
3

CREATE TABLE #t (id INT, naam SYSNAME);

En vervangt @t #t in de rest van het script., Deze keer ziet het uitvoeringsplan er zo uit:

Dit ziet er beter uit! Ten eerste is het Geschatte aantal rijen uit de tijdelijke tabel correct. Voor een ander, de complier koos voor een hash match-een veel betere keuze voor deze join dan de tabel scans die we eerder hadden.

Ik laat deze query uitvoeren tot het einde. Het liep voor iets meer dan 3 minuten op mijn (toegegeven Oud en langzaam!) laptop en produceerde 16.7 miljard rijen voor het raken van een uit het geheugen fout (dat is veel rijen!,) Door het aantal rijen in de tijdelijke tabel te verminderen tot 100.000 kon het in een comfortabele negen seconden eindigen.

dit alles betekent dat er niets mis is met de CTE-constructie; er moet iets anders aan de hand zijn. Dat iets anders is statistieken. De SQL Server compiler gebruikt statistieken (bijvoorbeeld rijtellingen) om de optimizer te informeren. Statistieken worden echter niet gemaakt of bijgewerkt voor tabelvariabelen. Dit is de “Aha!”moment! Het veranderen van het script om een tijdelijke tabel te gebruiken in plaats van een tabel variabele betekende dat er statistieken beschikbaar waren om een beter uitvoeringsplan te kiezen.,

dieper graven

dat er iets anders in de kleine zin in de buurt van het begin van het citaat uit BOL staat:

specificeert een tijdelijke benoemde resultaatset

Waarom is dat significant? Kunt u een ander type tijdelijke named result set bedenken? Wat dacht je van een tabel variabele! We hebben al ontdekt dat tabelvariabelen, die geen statistieken hebben, ravage kunnen aanrichten met uitvoeringsplannen vanwege onjuiste rijtellingen. Echter, een CTE bevat iets dergelijks in een tijdelijke result set. Bij het samenstellen van een complexe CTE heeft het mogelijk niet genoeg informatie beschikbaar om een optimaal plan af te leiden., Als ik denk dat dat gebeurt, dan is dit wat ik doe:

  1. Test elke CTE afzonderlijk van boven naar beneden om te zien of/waar uitvoeringstijden of rijtellingen exploderen., Dit is gemakkelijk te doen in SSMS door het toevoegen van een
    1
    2
    3

    SELECT * FROM &lt;CTE naam&gt;

    Net voor de hoofdquery dat trekt de CTEs samen en het benadrukken van en het uitvoeren van het script naar dat punt.,

  2. Controleer of de dader correct is geschreven met de juiste predicaten.
  3. zorg ervoor dat de predicaten worden geïndexeerd, indien mogelijk (daarover later meer).
  4. hervat het testen vanaf die CTE tot het einde.

er is tenminste één probleem dat niet kan worden opgelost met dit proces. Het is niet altijd mogelijk om ervoor te zorgen dat alle predicaten worden geïndexeerd, vooral voor afgeleide resultaten., In dat geval is er een andere optie om het knelpunt te doorbreken:

splits de CTE uit in een tijdelijke tabel

Dit betekent een herstructurering van uw zoekopdracht. In principe neemt u de bovenste helft van de query en schrijft u de resultaten naar een tijdelijke tabel, indexeert u de tabel en hervat u de query, te beginnen met de tijdelijke tabel. Stel dat ik bijvoorbeeld:

veronderstel verder dat het probleem CTE10 is.,

Nu hebben we de resultaten tot nu toe in een tijdelijke tabel, index:

1
2
3

CREATE INDEX IX_#CTE9 OP #CTE9(col1, col2, …)

laten we Nu de afwerking van de query:

er rekening mee dat u mogelijk nog doorgaan met het proces hierboven beschreven totdat u uw prestatiedoelstellingen.,

en een ander ding

wat je ook doet, raak gewend aan het lezen van uitvoeringsplannen. Er is een schat aan informatie daar en honderden, zo niet duizenden grote middelen om ze te begrijpen. Als laatste sanity check, echter, klik met de rechtermuisknop op de meest linkse vermelding in het grafische uitvoeringsplan. Dat zal waarschijnlijk een SELECT, UPDATE, invoegen of verwijderen. Kies nu in het contextmenu eigenschappen. Zorg ervoor dat u dit ongeveer halverwege ziet:

Als u Optimalisatieniveau niet vol ziet, is het een indicator dat uw zoekopdracht te complex is., Overweeg om het wat meer op te breken, gebruikmakend van de tijdelijke tabel techniek hierboven beschreven.

conclusie

gemeenschappelijke Tabeluitdrukkingen zijn gewoon een andere manier om subqueries te schrijven. Ze zijn niet magisch, noch behandelt SQL Server ze anders dan normale, geneste subqueries, met uitzondering van hun recursieve neven. CTEs kan code gemakkelijker te lezen en te onderhouden, omdat, wanneer goed gebruikt, kunt u zorgen scheiden door CTE. (Zie je hoe ik een objectgeoriënteerd programmeerprincipe binnensmokkelde?)

als ze slecht presteren, geef de CTE echter niet de schuld., Graaf dieper om te begrijpen wat er onder de dekens gebeurt. Het uitvoeringsplan is de perfecte manier om dat te doen.

  • Auteur
  • Laatste Berichten
Gerald Britton is een Senior SQL Server-Oplossing Ontwerper, Auteur, Software Ontwikkelaar, Docent en een Microsoft Data Platform MVP. Hij heeft vele jaren ervaring in de IT-industrie in verschillende rollen.,
Gerald is gespecialiseerd in het oplossen van SQL Server query prestaties problemen vooral als ze betrekking hebben op Business Intelligence oplossingen. Hij is ook een coauteur van het eBook “aan de slag met Python” en een fervent Python Ontwikkelaar, leraar, en Pluralsight auteur.
Je kunt hem vinden op LinkedIn, op Twitter op twitter.,com/GeraldBritton of @GeraldBritton, en op Pluralsight
Bekijk alle berichten van Gerald Britton

Laatste berichten door Gerald Britton (zie all)
  • Isolatie Momentopname in de SQL Server – augustus 5, 2019
  • het Krimpen van uw database met behulp van de opdracht DBCC SHRINKFILE – augustus 16, 2018
  • Gedeeltelijke stored procedures in SQL Server – juni 8, 2018

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *