SQL Server Loop gennem Tabel-Rækker, uden at Markøren

Af: Daniel Hansen | Opdateret: 2019-09-12 | Kommentarer (2) | Relateret: Mere > T-SQL

Problem

Youhave altid hørt, at du bør undgå markører inyour T-SQL-kode, som en SQL-Server bedste praksis, becausecursors er til skade for ydelseog nogle gange give problemer. Men nogle gange er der behov for at sløjfe igennemdata en række ad gangen, så i dette tip vil vi se på en sammenligning af, hvordan man gøren løkke uden at bruge markøren.,

løsning

Vi ved alle, at s .l Server, ligesom hver relationsdatabase tillader brugeren atudform set baserede operationer. Som mange databaseleverandører gør, inkluderer s .l serveren proceduremæssig udvidelse, som er T-s .l-sproget. Det tilføjer konstruktioner findes inprocedural sprog giver en mere ligetil kodning til udviklere. Disse konstruktioner blev tilføjet af en grund, og nogle gange er dette den eneste tilgang til opgaven.,

brug af et stykke tid Loop i stedet for markører I S .l Server

Hvis du nogensinde har arbejdet withithcursors, kan du finde denne titel lidt forvirrende, fordi markører bruger, mens de konstruerer at gentage mellem rækker. Men derudover vil jeg vise dig, at under nogle omstændigheder, når vi bruger en markør til at iterateover et sæt rækker, kan vi ændre det til et stykke tid loop. I sådanne tilfælde er den eneste udfordringvil være at vælge en ordentlig e .it tilstand.,

fordele og ulemper ved at bruge markører til at gentage gennem tabelrækker i s .l Server

ikke alt er galt med markører, de har også nogle fordeleover andre looping teknikker.markører kan opdateres: når du opretter en markør, bruger du en forespørgsel til at definere det ved hjælp af DEKLAR CURSORR MARKØRINSTRUKTIONEN. Ved at bruge opdateringsindstillingen i cursorcreation-erklæringen kan du opdatere kolonnerne i markøren.,

du kan bevæge dig frem og tilbage i en markør: ved at bruge RULLEMULIGHEDENI erklæringen markør kan du navigere over markørposterne i begge retninger med hentningsindstillingerne først, sidst, før, næste, relativ ogabsolut. Husk, at SCROLL-indstillingen er uforenelig med for .ard_onlyand FAST_FOR .ard-indstillingerne.

markører kan overføres til lagrede procedurer: hvis du bruger den globale mulighedFor at oprette en markør kan den bruges i enhver lagret procedure eller batch, der udføres i den samme forbindelse. Dette giver dig mulighed for at bruge markører på indlejrede lagrede procedurer.,markører har mange forskellige muligheder: med markører har du chancenat bruge forskellige muligheder, der påvirker, hvordan de vil opføre sig med hensyn til låsning.markører behøver ikke en betingelse: ved at bruge markører håndterer du et sæt rækker som en post. Dette giver dig mulighed for at bevæge dig over markøren udenbehovet for at have en boolsk tilstand. For eksempel kan du oprette en markørmed navnet på de databaser, der er bosiddende på en s .l Server-instans udenbehov for en surrogatnøgle til at fungere som en testtilstand som på et stykke tid loop.,

Der er også nogle negative aspekter, som du bør være opmærksom på, når du bruger cursorsi stedet for andre looping muligheder.

hvis du bruger globale markører i din kode, risikerer du at støde på fejl, fordi en markør lukkes af en lagret procedure, der er indlejret i din kode.
normalt markører har mindre ydeevne end en tilsvarende løkke ved hjælp af en Whilhileloop eller CTE.

fordele og ulemper ved at bruge et stykke tid Loop til at gentage gennem tabel rækker I S .l Server

Der er også fordele ved at bruge et stykke tid loop i forhold til en markør.,

mens sløjfer er hurtigere end markører.
mens sløjfer bruger mindre låse end markører.
mindre brug af Tempdb: mens sløjfer ikke opretter en kopi af data intempdb som en markør gør. Husk at markører, afhængigt af de muligheder, dubruge til at oprette dem kan forårsage temp tabeller, der skal oprettes.

Den næste liste beskriver de negative aspekter af WHILEHILE loops.

at bevæge sig fremad eller bagud er kompleks: for at bevæge sig fremad eller bagud i aloop skal du dynamisk ændre iterationstilstanden inde i løkken.,Dette kræver ekstra pleje; ellers kan du ende i en uendelig løkke.
risikoen for en uendelig løkke: sammenlignet med en markør har du ikke et fast sæt data til loop (dvs.de data, der returneres af SELECT statementin markørerklæringen), i stedet for når du bruger et stykke tid loop, skal du definere en grænsemed et udtryk, der evalueres til sandt eller falsk.

Opbygning af testmiljø for Markører og Sløjfer

for At teste dette, vil jeg bruge en tabel med en id-kolonne (CursorTestID),en varchar-kolonne (Filler) og en bigint kolonne (RunningTotal).,

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

tanken er, at løkke gennem den tabelrække, bestilt af CursorTestID columnand opdatere RunningTotal kolonne med summen af CursorTestID kolonne valueand værdien af RunningTotal kolonne af forrige række.men før vi starter, skal vi først generere nogle testrækker med det næste script.,

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

scriptet ovenfor, vil du bemærke, at jeg kun brugt en enkelt indsætte statementand jeg tog fordel af batch-separator (GO 500000 kommando) som en shortcutto udføre dette sæt erklæring 500000 gange. Du kan læse mere om denne metodefor at gentage batchudførelse på dette tip: udførelse af en T-s .l-batch flere gange ved hjælp af GO.

eksempel på en grundlæggende markør for at gennemløbe tabelrækker i s .l Server

lad os oprette en markør for at udfylde RunningTotal-kolonnen. Bemærk På ne .tscript, at jeg erklærede markøren med indstillingen FAST_FORFORWARDARD., Dette gøres for at forbedre markørens ydeevne, fordi ifølge Microsoft thefast_for .ard argument “angiver en for .ard_only, READ_ONLY markør med performanceoptimi .ations aktiveret”. Med andre ord instruerer VI s .l Server til at brugeen skrivebeskyttet markør, der kun kan bevæge sig fremad og rulles fra den første til den sidste række.

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

Det næste billede er et skærmbillede, der viser udførelsen af scriptet ovenfor.Som du kan se, at det tog tre minutter og fem sekunder til at opdatere de 500.000 rowsof vores test tabel.,

eksempel på en grundlæggende mens løkke for at cykle gennem tabelrækker I s .l Server

nu vil jeg omskrive det forrige script og undgå brugen af en markør. Du vil bemærke, at den indeholder et stykke tid loop, som er næsten identisk med den i thecursor script. Dette er, som jeg tidligere sagde, fordi selv når du arbejder med markørerdu skal bruge en iterativ kontrolstruktur.

det næste billede er et skærmbillede af udførelsen af scriptet ovenfor. Det tookless tid til at køre mens løkken end markøren.,

en Anden SQL Server Markøren Eksempel

Lad os tage for eksempel markøren i tipStandardize SQL Server-data med tekst opslag og erstat funktion. Et ord afrådgivning, for at køre denne kode, skal du følge trinnene i tipet for at oprette testmiljøet.

og her er markørkoden:

Hvis vi dissekerer denne kode, kan vi se, at der er en markør, der går igennemde tabelprodukter, som jeg kopierede nedenfor.,

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

SQL Server Markøren Eksempel Omdannes til en While-Løkke

for at erstatte denne markør med en WHILE-LØKKE, er vi nødt til at skabe en temporarytable til at gennemføre en optælling bordet. For alle jer, der ikke ved, hvad en tallytable er, kan vi definere det som en tabel, der indeholder et par kolonner bestående af en nøgle og dens værdi. I vores særlige tilfælde bruger vi en sekventiel heltalstart fra 1, så vi kan bruge den som en iterator. Denne nøgle vil blive knyttet til en ProductID fra tabellen produkter.,

først, da Produkttabellen har ProductID-nøglen defineret som en identitydu kan blive fristet til at omgå dette trin, men du skal overveje, at i et rigtigt tilfældeen række kunne være blevet slettet, derfor vil du ikke kunne bruge identitycolumn som en iterator. Derudover kan en række slettes, mens vi kører ourcode, og det kan føre til eksekveringsfejl. For at undgå dette vil vi tilføje aTRY-CATCHblock. Jeg vil gå videre ind i dette.

før vi starter WHILEHILE-løkken, skal vi indstille dens start-og stoptilstand., Fordenne sag tilføjede Jeg to nye heltalsvariabler ved navn @Iterator og @Ma .iterator.@Ma .iterator-variablen bruges til at holde antallet af elementer i #TallyTabletable, og vi indstiller dens værdi kun beforen gang, før vi starter løkken. @Iterator variableer initialiseret til 1, Da vi definerede det som startnummeret på sekvensen, og vi vil øge dens værdi ved hver iteration.

næste trin

er du ny til markører og har brug for lidt øvelse? I ne .ttip finder du en forklaring, en let at forstå cursor eksempel og mereanbefalede aflæsninger:s .l Server Cursor eksempel.,
hvis du vil konvertere de eksisterende markører i din kode til at indstille baserede forespørgsler,skal du tage et kig på dette kapitlerol Server Convert Cursorto Set Based from thes .l Server Database Design Best Practices Tutorial.
har du brug for et andet eksempel på at bruge et stykke tid loop? Se på dette tipdet viser dig, hvordan du opdeler DML-sætninger i batches:Optimer Store s .l-Serverindsæt, Opdater og Slet processer ved hjælp af Batches.
hvis du ikke ved, hvordan du bruger TRY…CATCH e .ceptionhandling, tag et kig på dette tip:s .l Server prøv at fange undtagelseshåndtering.,
Stay tuned til thes .l Server T-S .l Tipscategory at få flere kodning ideer.

Senest Opdateret: 2019-09-12

Om forfatteren

Daniel Hansen blev født i Buenos Aires, Argentina. Selvuddannet, siden barndommen viste han en passion for at lære.
Se alle mine tips

relaterede ressourcer

flere Database Developer Tips…