Intro

In relationele databases worden bewerkingen uitgevoerd op een reeks rijen. Een SELECT-instructie retourneert bijvoorbeeld een set rijen die een resultaatset wordt genoemd. Soms moet de applicatielogica met een rij tegelijk werken in plaats van met de hele resultatenset in één keer. In T-SQL is een manier om dit te doen een CURSOR gebruiken.

Als je over programmeervaardigheden beschikt, zou je waarschijnlijk een lus zoals FOR of WHILE gebruiken om door één item tegelijk te gaan, doe iets met de gegevens en de klus is geklaard. In T-SQL is een CURSOR een vergelijkbare benadering, die mogelijk de voorkeur heeft omdat deze dezelfde logica volgt. Maar wees gewaarschuwd, volg deze weg en er kunnen zich problemen voordoen.

In sommige gevallen maakt het gebruik van CURSOR niet zoveel rotzooi, maar over het algemeen moeten ze worden vermeden. Hieronder laten we enkele voorbeelden zien waarbij het gebruik van een CURSOR prestatieproblemen veroorzaakt en we zullen zien dat hetzelfde werk op veel andere manieren kan worden gedaan.

Voor deze demonstratie zullen we de AdventureWorks2012-database gebruiken, en laten we zeggen dat we wat gegevens willen ophalen van. tabel, voor elk product dat minder dan een dag nodig heeft om te vervaardigen, dat wil zeggen uit tabel ..

Een cursorvoorbeeld

Laten we beginnen met een CURSOR en de volgende syntaxis schrijven:

Na een korte koffiepauze , de query is voltooid en 833 rijen geretourneerd in de hieronder getoonde tijd. Het is belangrijk om te vermelden dat de gekozen syntaxis hierboven alleen voor demo-doeleinden is, en ik heb geen indexafstemming gemaakt om de zaken te versnellen.

Bij de uitvoering van de cursor hebben we twee stappen. Stap één, de positionering, wanneer de cursor zijn positie instelt op een rij ten opzichte van de resultatenset. Stap twee, het ophalen, wanneer het de gegevens van die specifieke rij krijgt in een bewerking genaamd FETCH.

In ons voorbeeld stelt de cursor zijn positie in op de eerste rij die wordt geretourneerd door de eerste SELECT en haalt de ProductID-waarde op die overeenkomt met de WHERE-voorwaarde in de @id-variabele. Vervolgens gebruikt de tweede SELECT de variabele waarde om gegevens uit te halen. en de volgende rij wordt opgehaald. Deze bewerkingen worden herhaald totdat er geen rijen meer zijn om mee te werken.

Ten slotte geeft de CLOSE-syntaxis de huidige resultatenset vrij en verwijdert de vergrendelingen van de rijen die door de cursor worden gebruikt, en DEALLOCATE verwijdert de cursorverwijzing.

Onze demotabellen zijn relatief klein en bevatten ongeveer 1.000 en 500 rijen. Als we tabellen met miljoenen rijen zouden moeten doorlopen, zou het een aanzienlijke hoeveelheid tijd duren en de resultaten zouden ons niet bevallen.

Laten we onze cursor nog een kans geven en de regel –LOCAL STATIC verwijderen. Er zijn veel argumenten die we kunnen gebruiken in een cursordefinitie, meer daarover op deze link CURSOR-argumenten, maar laten we ons nu concentreren op wat deze twee woorden betekenen.

Wanneer we het trefwoord LOCAL specificeren, is het bereik van de cursor lokaal ten opzichte van de batch waarin het is gemaakt en is het alleen geldig in dit bereik. Nadat de batch is voltooid, wordt de cursor automatisch ongedaan gemaakt. Er kan ook naar de cursor worden verwezen door een opgeslagen procedure, trigger of door een lokale cursorvariabele in een batch.

Het STATIC-sleutelwoord maakt een tijdelijke kopie van de gegevens die door de cursor in tempdb worden gebruikt in een tijdelijke tabel . Hier hebben we wat valstrikken. Een STATIC-cursor is alleen-lezen en wordt ook wel een snapshot-cursor genoemd omdat deze alleen werkt met de gegevens vanaf het moment dat deze werd geopend, wat betekent dat er geen wijzigingen worden weergegeven in de database op de set gegevens die door de cursor.In principe zullen geen updates, verwijderingen of invoegingen die zijn gemaakt nadat de cursor open was, zichtbaar zijn in de resultaatset van de cursor, tenzij we de cursor sluiten en opnieuw openen.

Houd hier rekening mee voordat u deze argumenten gebruikt en controleer of het overeenkomt met uw behoeften. Maar laten we eens kijken of onze cursor sneller is. Hieronder hebben we de resultaten:

12 seconden zijn veel beter dan 4 minuten en 47 seconden, maar houd rekening met de beperkingen hierboven uitgelegd.

Als we de syntaxis uitvoeren met dit argument LOCAL READ_ONLY FORWARD_ONLY, krijgen we dezelfde resultaten. READ_ONLY FORWARD_ONLY cursor kan alleen van de eerste rij naar de laatste worden geschoven. Als het STATIC-sleutelwoord ontbreekt, is de cursor dynamisch en gebruikt hij een dynamisch plan, indien beschikbaar. Maar er zijn gevallen waarin een dynamisch plan slechter is dan een statisch plan.

Wat gebeurt er als we het commentaar – LOKAAL FAST_FORWARD verwijderen?

FAST_FORWARD is gelijk aan READ_ONLY en FORWARD_ONLY cursor, maar heeft de mogelijkheid om het betere plan te kiezen uit een statisch of een dynamisch plan.

LOCAL FAST_FORWARD lijkt de beste keuze vanwege de flexibiliteit om kies tussen een statisch of dynamisch plan, maar FORWARD_ONLY doet het ook erg goed. Hoewel we het resultaat in gemiddeld 12 seconden hebben verkregen met behulp van beide methoden, moeten deze argumenten goed worden getest voordat we er een kiezen.

Er zijn veel manieren om hetzelfde resultaat te verkrijgen, veel sneller en met minder invloed op de prestaties.

Cursoralternatief

Een methode is het gebruik van een JOIN, en zoals we hierna kunnen zien, zijn de resultaten aanzienlijk beter.

Ten eerste, we moeten de statistiektijd inschakelen om de SQL Server-uitvoeringstijd te meten, en een INNER JOIN maken tussen twee tabellen,. en. op ProductID-kolom. Nadat we op de EXECUTE-knop hadden gedrukt, produceerden we dezelfde resultaten in 330 ms, vergeleken met 04:47 tijd met de cursormethode, en we hebben een glimlach op ons gezicht.

We hoeven niet elke rij te herhalen om te krijgen wat we nodig hebben, we hebben geen loops meer, geen while-clausule, geen iter ations werken we in plaats daarvan met sets gegevens, krijgen we sneller wat we willen en schrijven we minder code.

Een correct gebruik van de cursor

Nu we hebben gezien hoeveel schade een cursor kan doen, laten we een voorbeeld bekijken waarin we er gebruik van kunnen maken.

Laten we aannemen dat we de grootte en het aantal rijen willen selecteren voor alleen bepaalde tabellen uit een database. Om dit te bereiken, halen we alle tabelnamen op basis van criteria uit information_schema.tables en met behulp van een CURSOR zullen we door elk van die tabelnamen heen lopen en de opgeslagen procedure sp_spaceused uitvoeren door een tabelnaam per keer door te geven om de informatie te krijgen die we nodig hebben .

We zullen dezelfde AdventureWorks2012-database gebruiken en alle tabellen ophalen uit het Sales-schema dat de naam ‘Sales’ bevat. Voor elke geretourneerde tabelnaam willen we alle info van information_schema.tables zien.

Hieronder hebben we de T-SQL-syntaxis en de verkregen resultaten:

Dit is een methode waarbij CURSOR handig is door enkele gegevens rij voor rij te doorlopen en het gewenste resultaat te krijgen. In dit specifieke geval krijgt de cursor de klus geklaard zonder gevolgen voor de prestaties en is hij gemakkelijk te gebruiken.

Conclusies

Daar hebben we het. We hebben enkele voorbeelden laten zien met het goede, het slechte en het lelijke bij het gebruik van cursors. In de meeste gevallen kunnen we JOINS gebruiken, zelfs WHILE-clausules, SSIS pakketten of andere alternatieve methoden om sneller hetzelfde resultaat te krijgen, met minder impact op de output van de prestaties en zelfs met minder regels syntaxis.

Wanneer we te maken hebben met een OLTP-omgeving en grote reeksen gegevens die moeten worden verwerkt, mag het woord ‘CURSOR’ niet worden uitgesproken.

In SQL is het een goede gewoonte om na te denken over het maken van bewerkingen op gegevenssets in plaats van na te denken op een programmatische manier, met behulp van iteraties of loops, omdat dit soort benadering niet wordt aanbevolen of bedoeld voor dit gebruik. Het proberen om loops zoals FOR of FOREACH uit programmeertalen te gebruiken en die logica te associëren met SQL-bewerkingen, is een obstakel voor het vinden van de juiste oplossing voor onze behoeften. We moeten denken aan set-gebaseerde bewerkingen in plaats van één rij per keer om de gegevens te krijgen die we nodig hebben.

Cursors kunnen in sommige toepassingen worden gebruikt voor geserialiseerde bewerkingen, zoals weergegeven in het bovenstaande voorbeeld, maar in het algemeen zouden ze dat moeten doen worden vermeden omdat ze een negatieve invloed hebben op de prestaties, vooral wanneer u met grote hoeveelheden gegevens werkt.

• Auteur
• Recente berichten

Ik ben een DBA bij Yazaki Component Technology met 3 jaar ervaring in SQL Server.
Ik heb een bachelordiploma in computertechniek en beschik ook over vaardigheden in .NET, C # en Windows Server. Ik heb ervaring opgedaan met het afstemmen en monitoren van prestaties, T-SQL-ontwikkeling, back-upstrategie, beheer en het configureren van databases.
Ik ben gepassioneerd door IT-zaken, videogames, goede films en electro-house muziek helpt me te werken wanneer het op kantoor is noisy.
Bekijk alle berichten van Sergiu Onet

Laatste berichten van Sergiu Onet (alles bekijken)

• SQL Server-cursors gebruiken – voor- en nadelen – 23 maart 2016