SQL Server-Loop gjennom tabellrader uten Markøren

Av: Daniel Farina | Oppdatert: 2019-09-12 | Kommentarer (2) | i Slekt: Mer > T-SQL

Problem

Youhave alltid hørt at du bør unngå pekere inyour T-SQL-koden som en SQL Server-beste praksis, becausecursors er skadelig for performanceand noen ganger føre til problemer. Men noen ganger er det behov for å gå gjennom thedata én rad om gangen, så dette tipset vi vil se på en sammenligning av hvordan doa loop uten å bruke markøren.,

Løsning

Vi vet alle at SQL Server, som hver relasjonell database lar brukeren toperform set-basert virksomhet. Også, som mange database leverandører gjør det, SQL Server includesa prosessuelle extension som er T-SQL-språk. Det legger konstruksjoner funnet inprocedural språk slik at en mer enkel koding til utviklere. Theseconstructs ble lagt for en grunn, og noen ganger er dette bare tilnærming til oppgaven.,

ved Hjelp av en While-Løkke i Stedet for Pekere i SQL Server

Hvis du har noen gang jobbet withcursors, du kan finne denne tittelen litt confusingbecause tross alt, pekere bruker mens du konstruerer for å iterere mellom radene. Men besidesthat, jeg ønsker å vise deg at i noen tilfeller, når vi bruk en markøren til iterateover et sett av rader, kan vi endre den til en stund loop. I slike tilfeller bare challengewill være å velge en riktig avslutt tilstand.,

Fordeler og Ulemper med Bruk av Pekere til å Gå Gjennom Tabellen Rader i SQL Server

det er Ikke alt som er galt med pekere, de har også noen advantagesover andre looping teknikker.

Markørene er updatable: Når du oppretter en markør, du bruker en spørring til å defineit bruke ERKLÆRER MARKØREN instruksjon. Ved hjelp av UPDATE option i cursorcreation uttalelse, kan du oppdatere kolonner i markøren.,
Du kan bevege deg forover og bakover i en markør på: Ved hjelp av BLA optionin den ERKLÆRER MARKØREN setningen du kan navigere over markøren poster inboth retninger med hente valg FØRST, SIST, FØR, NESTE, i FORHOLD andABSOLUTE. Husk at BLA-valget er i strid med FORWARD_ONLYand FAST_FORWARD valg.
Markører kan være bestått for å lagrede prosedyrer: Hvis du bruker den GLOBALE optionto opprette en markør, det kan brukes i alle lagret prosedyre eller batch executedin samme tilkobling. Dette tillater deg å bruke pekere på nestede lagrede prosedyrer.,
Markører har en rekke ulike alternativer: Med pekere du har chanceto bruke ulike valg som påvirker hvordan de vil oppføre seg i forhold til låsing.
Pekere trenger ikke en betingelse: Ved hjelp av pekere, du er handlinga sett med rader som er rekord. Dette gir deg mulighet til å bevege seg over markøren withoutthe behov for å ha en Boolsk tilstand. For eksempel, du kan opprette en cursorwith navnet på databaser som er bosatt på en SQL Server-forekomsten uten theneed av en surrogat-tasten for å fungere som en test tilstand som på en WHILE-løkke.,

Det er også noen negative sider som du bør være oppmerksom på når du bruker cursorsinstead av andre looping valg.

Hvis du bruker global pekere i koden som du risikerer facingerrors på grunn av en markør blir stengt av noen som er lagret prosedyre som er nestet i yourcode.
Vanligvis pekere har mindre ytelse enn en tilsvarende loop ved hjelp av en WHILEloop eller CTE.

Fordeler og Ulemper med å Bruke en While-Løkke til å Gå Gjennom Tabellen Rader i SQL Server

Det er også fordeler med å bruke en WHILE-løkke i forhold til markøren.,

Mens looper er raskere enn pekere.
Mens løkker bruker mindre låser enn pekere.
Mindre bruk av Tempdb: Mens looper ikke lage en kopi av data intempdb som en markør gjør. Husk at pekere, avhengig av alternativene som er youuse å lage dem kan føre til temp-tabeller for å bli opprettet.

Den neste finner du en oversikt over de negative sidene av MENS looper.

for å Flytte fremover eller bakover er sammensatte: for Å flytte fremover eller bakover i aloop du trenger å dynamisk endre iterasjon tilstand inne i loopen.,Dette krever ekstra omsorg, ellers kan du ende opp i en uendelig løkke.
risikoen for en uendelig løkke: i Forhold til markøren, trenger du ikke har en fast sett av data for å sløyfe (dvs. dataene som returneres av VELG statementin markøren erklæringen), i stedet når du bruker en STUND loop, må du definere en boundarywith et uttrykk som evalueres til true eller false.

Bygging av testmiljø for Pekere og Looper

for Å teste dette, vil jeg bruke en tabell med en identitet 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

ideen er å sløyfe gjennom tabellrader bestilt av CursorTestID columnand oppdatere RunningTotal kolonne med summen av CursorTestID kolonne valueand verdien av RunningTotal kolonne av forrige rad.

Men før du starter, må du først trenger vi for å generere noen test rader med neste script.,

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

På manuset over vil du legge merke til at jeg kun har brukt en enkelt sette inn statementand jeg tok nytte av batch separator (GO-500000-kommando) som en shortcutto utføre dette sette uttalelse 500000 ganger. Du kan lese mer om dette methodto gjenta batch-kjøring på dette tips:du Utfører en T-SQL-batch flere ganger ved hjelp av GÅ.

Eksempel på en Grunnleggende Markøren til å gå gjennom Tabellen Rader i SQL Server

La oss lage en markør for å fylle RunningTotal kolonne. Varsel på nextscript at jeg erklærte markøren med mulighet FAST_FORWARD., Dette er gjort inorder å forbedre ytelsen til markøren fordi ifølge Microsoft theFAST_FORWARD argumentet «Angir en FORWARD_ONLY, READ_ONLY markøren med performanceoptimizations aktivert». Med andre ord, vi er instruere SQL Server å usea bare lese fra markør som kan bare flyttes fremover og skyves fra første til siste rad.

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

neste bilde er et skjermbilde som viser utførelse av skriptet ovenfor.Som du kan se, så tok det tre minutter og fem sekunder for å oppdatere 500 000 rowsof våre test bordet.,

Eksempel på en Grunnleggende Mens Løkken for å Bla gjennom Tabellen Rader i SQL Server

Nå vil jeg skrive om den tidligere script for å unngå bruk av en markør. Du willnotice at den inneholder en Stund loop som er nesten identisk med den i thecursor script. Dette er, som jeg tidligere har sagt, fordi selv når du arbeider med cursorsyou trenger å bruke en iterativ kontroll struktur.

neste bilde er et skjermbilde av kjøringen av skriptet ovenfor. Det tookless tid til å kjøre mens loop enn markøren.,

en Annen SQL Server Markøren Eksempel

La oss ta for eksempel markøren i tipStandardize SQL Server data med tekst oppslag og erstatt-funksjonen. Et ord ofadvice, for å kjøre denne koden, bør du følge trinnene i spissen for å createthe testmiljø.

Og her er markøren koden:

Hvis vi dissekere denne koden, kan vi se at det er en markør som går gjennomselskapet tabell produkter som jeg kopierte nedenfor.,

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

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

for å erstatte markøren med et MENS LOOP, må vi lage en temporarytable å gjennomføre en oversikt bordet. For alle dere som ikke vet hva en tallytable er, kan vi definere det som en tabell som inneholder et par av kolonner consistingof en nøkkel og verdi. I vårt tilfelle vil vi bruke en sekvensiell integerkey starter fra 1, slik at vi kan bruke det som en iterator. Denne nøkkelen vil være associatedto en ProductID fra Produkter tabell.,

først, siden Produktene tabellen har den ProductID-tasten definert som en identityyou kan bli fristet til å omgå dette trinnet, men du trenger ikke å tenke på at i en ekte casea rad kunne ha blitt slettet, derfor vil du ikke være i stand til å bruke identitycolumn som en iterator. I tillegg rad kan bli slettet mens vi kjører ourcode og det kan føre til kjøring av feil. For å unngå dette har vi tenkt å legge aTRY-CATCHblock. Jeg vil gå inn i denne videre.

Før du starter MENS loop, vi trenger å sette sin start og stopp tilstand., Forthis saken jeg har lagt til to nye heltall variabler som heter @Iterator og @MaxIterator.Den @MaxIterator variabelen er brukt for å holde antall elementer i #TallyTabletable og vi satte sin verdi bare én gang før du starter loop. Den @Iterator variableis initialisert til 1, som vi definerte det som starter nummer på sekvensen, og vi kommer for å øke sin verdi ved hver iterasjon.

Neste Trinn

Er du ny til pekere og trenger litt praksis? I nexttip du vil finne en forklaring, en enkel å forstå markøren eksempel og morerecommended målinger:SQL Server Markøren Eksempel.,
Hvis du ønsker å konvertere eksisterende pekere i koden for å angi basert spørringer,ta en titt på denne chapterSQL Server Konvertere Cursorto Sett ut fra theSQL Server-Database Design Beste Praksis i Opplæringen.
har du behov for et annet eksempel på bruk av en Stund loop? Ta en titt på denne tipthat vil vise deg hvordan å splitte DML uttalelser i grupper:Optimalisere Store SQL ServerInsert, Oppdatere og Slette Prosesser ved Hjelp av Grupper.
I tilfelle du ikke vet hvordan du skal bruke PRØVE…TA exceptionhandling, ta en titt på dette tipset:SQL Server Prøve og Fange Unntak Håndtering.,
Stay tuned for å theSQL Server T-SQL Tipscategory å få mer koding ideer.

Sist Oppdatert: 2019-09-12

Om forfatteren

Daniel Farina ble født i Buenos Aires, Argentina. Selvlært, siden barndommen viste han en lidenskap for læring.
Vis alle mine tips

Related Ressurser

Mer-Database Utvikler Tips…