door: Daniel Farina | bijgewerkt: 2019-09-12 | Comments (2) | verwant: meer > T-SQL

probleem

u hebt altijd gehoord dat u cursors in uw T-SQL-code moet vermijden als een SQL-Server best practice, omdat cursors schadelijk zijn voor prestaties en soms problemen veroorzaken. Maar soms is het nodig om rij voor rij door de gegevens te Lusen, dus in deze tip zullen we kijken naar een vergelijking van hoe je een lus kunt doen zonder cursor te gebruiken.,

Solution

we weten allemaal dat SQL Server, net als elke relationele database, de gebruiker toestaat om op format gebaseerde bewerkingen uit te voeren. Ook, zoals veel databaseverkopers doen, bevat SQL Server een procedurele extensie die de T-SQL-taal is. Het voegt constructies gevonden inprocedural talen waardoor een meer eenvoudige codering voor ontwikkelaars. Dezeconstructies werden niet voor niets toegevoegd en soms is dit de enige benadering van de taak die voor handen is.,

met behulp van een While Loop in plaats van Cursors in SQL Server

Als u ooit met cursors hebt gewerkt, kunt u deze titel Een beetje verwarrend vinden omdat cursors tenslotte while constructs gebruikt om tussen rijen te herhalen. Maar daarnaast wil ik jullie laten zien dat we in sommige omstandigheden, wanneer we een cursor gebruiken om een reeks rijen te herhalen, deze kunnen veranderen in een while-lus. In dergelijke gevallen zal de enige uitdaging zijn om een goede exitvoorwaarde te kiezen.,

voors en tegens van het gebruik van Cursors om door tabelrijen te itereren in SQL Server

niet alles is verkeerd met cursors, ze hebben ook een aantal voordelen ten opzichte van andere loopingtechnieken.

  • Cursors kunnen worden bijgewerkt: wanneer u een cursor maakt, gebruikt u een query om het te definiëren met behulp van de cursor-instructie declareren. Met de optie Bijwerken in het cursorcreation statement kunt u de kolommen binnen de cursor bijwerken.,
  • u kunt vooruit en achteruit gaan in een cursor: met behulp van de SCROLLOPTIE in het cursor-statement declareren kunt u in beide richtingen door de cursor-records navigeren met de opties First, LAST, PRIOR, NEXT, RELATIVE enabsolute. Houd er rekening mee dat de SCROLL optie niet compatibel is met de Forward_only en FAST_FORWARD opties.
  • Cursors kunnen worden doorgegeven aan opgeslagen procedures: als u de Globale optie gebruikt om een cursor aan te maken, kan deze worden gebruikt in elke opgeslagen procedure of batch die in dezelfde verbinding wordt uitgevoerd. Hiermee kunt u cursors gebruiken op geneste opgeslagen procedures.,
  • Cursors hebben veel verschillende opties: met cursors heb je de kans om verschillende opties te gebruiken die van invloed zijn op hoe ze zich zullen gedragen met betrekking tot vergrendeling.
  • Cursors hebben geen voorwaarde nodig: door cursors te gebruiken, wordt een reeks rijen als record behandeld. Hiermee kunt u over de cursor bewegen zonder dat u een Booleaanse voorwaarde hoeft te hebben. U kunt bijvoorbeeld een cursormaken met de naam van de databases die zich op een SQL Server-instantie bevinden zonder dat een surrogaatsleutel nodig is om als testvoorwaarde te werken, zoals op een WHILE-lus.,

er zijn ook enkele negatieve aspecten die u moet weten wanneer u cursorsin plaats van andere looping Opties Gebruikt.

  • Als u globale cursors in uw code gebruikt, neemt u het risico van fouten als gevolg van het sluiten van een cursor door een opgeslagen procedure genest in uw code.
  • gewoonlijk hebben cursors minder prestaties dan een gelijkwaardige lus met behulp van een WHILEloop of CTE.

voors en tegens van het gebruik van een While Loop om door tabelrijen in SQL Server te itereren

Er zijn ook voordelen om een WHILE loop te gebruiken in vergelijking met een cursor.,

  • terwijl lussen sneller zijn dan cursors.
  • terwijl lussen minder sloten gebruiken dan cursors.
  • minder gebruik van Tempdb: terwijl lussen geen kopie van data maken intempdb als een cursor dat doet. Denk eraan dat cursors, afhankelijk van de opties die u gebruikt om ze te maken, ervoor kunnen zorgen dat de temp-tabellen worden gemaakt.

de volgende lijst beschrijft de negatieve aspecten van WHILE loops.

  • vooruit of achteruit gaan is complex: om vooruit of achteruit te gaan in aloop moet u dynamisch de iteratieconditie binnen de lus wijzigen.,Dit vereist extra zorg, anders kun je in een oneindige lus eindigen.
  • het risico van een oneindige lus: vergeleken met een cursor, heb je geen vaste verzameling gegevens om te lus (dat wil zeggen de gegevens die worden geretourneerd door de SELECT statement in de cursor declaratie), in plaats daarvan moet je bij het gebruik van een WHILE lus een boundary definiëren met een expressie die wordt geëvalueerd naar true of false.

het bouwen van de testomgeving voor Cursors en Loops

om dit te testen, zal ik een tabel gebruiken met een identiteitskolom (CursorTestID),een varchar-kolom (Filler) en een bigint-kolom (RunningTotal).,

CREATE TABLE CursorTest( CursorTestID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Filler VARCHAR(4000), RunningTotal BIGINT )GO

het idee is om de tabelrijen geordend door de CursorTestID-kolom te lusenen de lopende totale kolom bij te werken met de som van de CursorTestID-kolomwaarde en de waarde van de lopende totale kolom van de vorige rij.

maar voordat we beginnen, moeten we eerst wat testrijen genereren met het volgende script.,

INSERT INTO dbo.CursorTest ( Filler, RunningTotal )VALUES ( REPLICATE('a', 4000), 0 )GO 500000

in het bovenstaande script zult u zien dat ik slechts een enkele insert-statement heb gebruikt en dat ik gebruik heb gemaakt van het batchscheidingsteken (de opdracht GO 500000) als sneltoets om dit insert-statement 500000 keer uit te voeren. U kunt meer lezen over deze methode om batch-uitvoering te herhalen op deze tip:Het meerdere keren uitvoeren van een T-SQL-batch met behulp van GO.

voorbeeld van een Basiscursor om door tabelrijen te Lusenin SQL Server

laten we een cursor maken om de kolom RunningTotal te vullen. Merk op dat ik de cursor heb aangegeven met de optie FAST_FORWARD., Dit wordt gedaan om de prestaties van de cursor te verbeteren omdat volgens Microsoft hetfast_forward argument “specificeert een FORWARD_ONLY, READ_ONLY cursor met performanceoptimizations ingeschakeld”. Met andere woorden, we instrueren SQL Server om een alleen-lezen cursor te gebruiken die alleen vooruit kan gaan en van de eerste naar de laatste rij kan worden gescrolld.

DECLARE @CursorTestID INT;DECLARE @RunningTotal BIGINT = 0; DECLARE CUR_TEST CURSOR FAST_FORWARD FOR SELECT CursorTestID RunningTotal FROM CursorTest ORDER BY CursorTestID; OPEN CUR_TESTFETCH NEXT FROM CUR_TEST INTO @CursorTestID WHILE @@FETCH_STATUS = 0BEGIN UPDATE dbo.CursorTest SET RunningTotal = @RunningTotal + @CursorTestID WHERE CursorTestID = @CursorTestID; SET @RunningTotal += @CursorTestID FETCH NEXT FROM CUR_TEST INTO @CursorTestIDENDCLOSE CUR_TESTDEALLOCATE CUR_TESTGO

de volgende afbeelding is een schermopname die de uitvoering van het script laat zien above.As het duurde drie minuten en vijf seconden om de 500.000 rijen van onze testtabel bij te werken.,

voorbeeld van een Basic While Loop om door tabelrijen te bladeren in SQL Server

nu zal ik het vorige script herschrijven om het gebruik van een cursor te vermijden. Je zult merken dat het een While lus bevat die bijna identiek is aan die in het cursor script. Dit is, zoals ik eerder zei, omdat zelfs bij het werken met cursorsyou nodig hebt om een iteratieve controlestructuur te gebruiken.

de volgende afbeelding is een schermopname van de uitvoering van het bovenstaande script. Het tookless tijd om de while lus dan de cursor uit te voeren.,

een ander SQL Server Cursor voorbeeld

laten we bijvoorbeeld de cursor in de tipstandard SQL Server data Met text lookup en replace functie. Een woord van advies, om deze code uit te voeren, moet u de stappen in de tip volgen om de testomgeving aan te maken.

en hier is de cursor code:

als we deze code ontleden, kunnen we zien dat er één cursor door de tabelproducten gaat die ik hieronder heb gekopieerd.,

DECLARE load_cursor CURSOR FOR SELECT ProductID, ProductName FROM dbo.Products

SQL Server Cursor voorbeeld geconverteerd naar een While Loop

om deze cursor te vervangen door een WHILE LOOP, moeten we een temporarytabel maken om een tally tabel te implementeren. Voor iedereen die niet weet wat een tabel is, kunnen we het definiëren als een tabel die een paar kolommen bevat die bestaan uit een sleutel en zijn waarde. In ons specifieke geval gebruiken we een sequentiële integerkey vanaf 1, zodat we het kunnen gebruiken als een iterator. Deze sleutel zal worden gekoppeld aan een product uit de Productentabel.,

omdat in de Productentabel de ProductID-sleutel is gedefinieerd als een identiteit, zult u misschien geneigd zijn om deze stap te omzeilen, maar u moet er rekening mee houden dat in een echt geval een rij had kunnen worden verwijderd, zodat u de identiteits-kolom niet als een iterator kunt gebruiken. Bovendien kan een rij worden verwijderd terwijl we ourcode uitvoeren en het kan leiden tot uitvoeringsfouten. Om dit te voorkomen gaan we aTRY-CATCHblock toevoegen. Ik zal hier verder op ingaan.

voordat we de while lus starten, moeten we de start-en stopvoorwaarde instellen., Voor deze materie voegde ik twee nieuwe integer variabelen genaamd @Iterator en @MaxIterator.De variabele @MaxIterator wordt gebruikt om het aantal items in de #TallyTabletable te behouden en we stellen de waarde slechts één keer in voordat we de lus starten. De @ Iterator variableis geïnitialiseerd naar 1, zoals we het gedefinieerd als het startnummer op de reeks en we gaan zijn waarde bij elke iteratie verhogen.

volgende stappen
  • bent u nieuw bij cursors en hebt u wat oefening nodig? In de nexttip vindt u een uitleg, een gemakkelijk te begrijpen cursor voorbeeld en meer aanbevolen lezingen:SQL Server Cursor voorbeeld.,
  • als u de bestaande cursors in uw code wilt converteren naar set based queries, bekijk dan deze chapterSQL Server Convert Cursorto Set Based from theSQL Server Database Design Best Practices Tutorial.
  • heeft u nog een voorbeeld nodig over het gebruik van een While loop? Neem een kijkje op deze tip die je zal laten zien hoe je DML statements in batches splitsen: optimaliseer grote SQL ServerInsert, Update en verwijder processen met behulp van Batches.
  • voor het geval u niet weet hoe u TRY moet gebruiken…CATCH exceptionhandling, neem een kijkje op deze tip: SQL Server probeer en Catch Exception Handling.,
  • blijf op de hoogte van de QL Server T-SQL Tipscategorie om meer coderingsideeën te krijgen.

Laatst Bijgewerkt: 2019-09-12

Over de auteur
Daniel Farina werd geboren in Buenos Aires, Argentinië. Zelfopgeleid, vanaf zijn kindertijd toonde hij een passie voor leren.
Bekijk al mijn tips
gerelateerde bronnen

  • meer tips voor Databaseontwikkelaars…

Geef een reactie

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