Intro

I relasjonsdatabaser utføres operasjoner på et sett med rader. For eksempel returnerer en SELECT-setning et sett med rader som kalles et resultatsett. Noen ganger må applikasjonslogikken jobbe med en rad om gangen i stedet for hele resultatet satt samtidig. I T-SQL er en måte å gjøre dette på å bruke en CURSOR.

Hvis du har programmeringsevner, vil du sannsynligvis bruke en løkke som FOR eller WHILE til å gjenta gjennom ett element om gangen, gjør noe med dataene og jobben er gjort. I T-SQL er en CURSOR en lignende tilnærming, og kan være foretrukket fordi den følger samme logikk. Men vær oppmerksom på at du tar denne veien, og problemer kan følge.

Det er noen tilfeller når du ikke bruker CURSOR så mye rot, men generelt sett bør de unngås. Nedenfor vil vi vise noen eksempler der bruk av en CURSOR skaper ytelsesproblemer, og vi vil se at den samme jobben kan gjøres på mange andre måter.

For formålet med denne demonstrasjonen vil vi bruke AdventureWorks2012-databasen, og la oss si at vi ønsker å få data fra. tabell, for hvert produkt som det kreves mindre enn en dag å produsere, det vil si fra tabellen ..

Et markøreksempel

La oss starte med å bruke en PEGER og skrive følgende syntaks:

Etter en kort kaffepause , ble spørringen fullført, og returnerte 833 rader i tiden vist nedenfor. Det er viktig å nevne at de valgte syntaksen ovenfor bare er for demomål, og jeg foretok ingen indeksjustering for å øke hastigheten.

I markørutførelsen har vi to trinn. Trinn ett, posisjoneringen, når markøren setter sin posisjon til en rad fra resultatsettet. Trinn to, hentingen når den får dataene fra den spesifikke raden i en operasjon kalt FETCH.

I vårt eksempel setter markøren sin posisjon til den første raden som returneres av den første SELECT og henter ProductID-verdien som samsvarer med WHERE-tilstanden i @id-variabelen. Deretter bruker den andre SELECT variabelverdien for å hente data fra. og neste rad hentes. Disse operasjonene gjentas til det ikke er flere rader å jobbe med.

Til slutt frigjør CLOSE syntaks gjeldende resultatsett og fjerner låsene fra radene som brukes av markøren, og DEALLOCATE fjerner markørreferanse.

Demotabellene våre er relativt små som inneholder omtrent 1000 og 500 rader. Hvis vi måtte gå gjennom tabeller med millioner av rader, ville det vare lang tid, og resultatene ville ikke glede oss.

La oss gi markøren vår en ny sjanse og ikke kommentere linjen – LOKAL STATISK. Det er mange argumenter vi kan bruke i en markørdefinisjon, mer om det på denne lenken CURSOR Arguments, men foreløpig skal vi fokusere på hva disse to ordene betyr.

Når vi spesifiserer LOKALt nøkkelord, er markørens omfang lokalt for batchen det ble opprettet i, og det er bare gyldig i dette omfanget. Etter at batchen er fullført, blir markøren automatisk avdelt. Markøren kan også refereres til ved en lagret prosedyre, trigger eller ved en lokal markørvariabel i en batch.

STATIC-nøkkelordet lager en midlertidig kopi av dataene som brukes av markøren i tempdb i en midlertidig tabell . Her har vi noen gotchas. En STATISK markør er skrivebeskyttet og blir også referert til som et øyeblikksbilde fordi den bare fungerer med dataene fra det tidspunktet den ble åpnet, noe som betyr at den ikke viser noen endringer som er gjort i databasen på datasettet som brukes av markøren.I utgangspunktet vil ingen oppdateringer, slettinger eller innsatser som er gjort etter at markøren var åpen, være synlige i markørens resultatsett med mindre vi lukker og åpner markøren på nytt.

Vær oppmerksom på dette før du bruker disse argumentene, og sjekk om det samsvarer med dine behov. Men la oss se om markøren vår er raskere. Nedenfor har vi resultatene:

12 sekunder er mye bedre enn 4 minutter og 47 sekunder, men husk begrensninger forklart ovenfor.

Hvis vi kjører syntaksen ved hjelp av dette argumentet LOKAL LES_ONLY FORWARD_ONLY, får vi de samme resultatene. READ_ONLY FORWARD_ONLY-markøren kan bare rulles fra første rad til den siste. Hvis STATIC-nøkkelordet mangler, er markøren dynamisk og bruker en dynamisk plan hvis tilgjengelig. Men det er tilfeller der en dynamisk plan er verre enn en statisk.

Hva skjer når vi ikke kommenterer –LOCAL FAST_FORWARD?

FAST_FORWARD tilsvarer READ_ONLY og FORWARD_ONLY-markøren, men har muligheten til å velge den bedre planen fra enten en statisk eller en dynamisk.

LOCAL FAST_FORWARD ser ut til å være det beste valget på grunn av sin fleksibilitet til velg mellom en statisk eller dynamisk plan, men FORWARD_ONLY gjør også jobben veldig bra. Selv om vi fikk resultatet i gjennomsnitt på 12 sekunder ved å bruke begge disse metodene, bør disse argumentene testes riktig før du velger en av dem.

Det er mange måter å oppnå det samme resultatet på, mye raskere og med mindre innvirkning på ytelsen.

Markøralternativ

En metode er å bruke JOIN, og som vi kan se videre, er resultatene betydelig bedre.

Først, Vi må aktivere statistikk tid for å måle SQL Server kjøringstid, og lage en INNER JOIN mellom to tabeller. og. på ProductID-kolonnen. Etter å ha trykket på EXECUTE-knappen, produserte vi de samme resultatene i 330 ms, sammenlignet med 04:47 tid fra markørmetoden, og vi har et smil om munnen.

Vi trenger ikke å gjenta gjennom hver rad for å få det vi trenger, vi har ikke flere sløyfer, ingen tidsklausul, ingen iter arbeider vi med datasett i stedet for å få det vi ønsker raskere og skrive mindre kode.

En passende bruk av markøren

Nå som vi har sett hvor mye skade en markøren kan gjøre, la oss se et eksempel der vi kan bruke den.

La oss anta at vi vil velge størrelse og antall rader for bare bestemte tabeller fra en database. For å oppnå dette vil vi få alle tabellnavn basert på kriterier fra information_schema.tables og ved hjelp av en CURSOR vil vi gå gjennom hvert av det tabellnavnet og utføre den lagrede prosedyren sp_spaceused ved å sende ett tabellnavn om gangen for å få den informasjonen vi trenger .

Vi vil bruke den samme AdventureWorks2012-databasen, og få alle tabeller fra salgsskjemaet som inneholder navnet ‘Salg’. For hvert returnerte tabellnavn ønsker vi å se all informasjonen fra information_schema.tables.

Nedenfor har vi T-SQL-syntaksen og de oppnådde resultatene:

Dette er en metode der CURSOR er nyttig ved å itere gjennom noen data en rad om gangen og få resultatet som trengs. I dette tilfellet får markøren jobben uten å ha noen innvirkning på ytelsen og er enkel å bruke.

Konklusjoner

Der har vi det. Vi viste noen eksempler med det gode, det dårlige og det stygge når vi bruker markører. I de fleste tilfeller kan vi bruke JOINS, til og med mens klausuler, SSIS pakker eller andre alternative metoder for å få det samme resultatet raskere, med mindre innvirkning på ytelsen og til og med skrive færre syntakselinjer.

Når vi har å gjøre med OLTP-miljø og store datasett å behandle, bør ordet ‘CURSOR’ ikke snakkes.

I SQL er det en god praksis å tenke på å utføre operasjoner på datasett, i stedet for å tenke på en programmatisk måte, ved hjelp av iterasjoner eller løkker, fordi denne typen tilnærminger ikke anbefales eller er ment for denne bruken. Å prøve å bruke sløyfer som FOR eller FOREACH fra programmeringsspråk og knytte logikken til SQL-operasjoner, er et hinder for å få den rette løsningen på våre behov. Vi må tenke på settbaserte operasjoner i stedet for en rad om gangen for å få de dataene vi trenger.

Markørene kan brukes i noen applikasjoner for serialiserte operasjoner som vist i eksempelet ovenfor, men generelt sett bør de unngås fordi de påvirker ytelsen negativt, spesielt når du bruker store datasett.

• Forfatter
• Nylige innlegg

Jeg er en DBA i Yazaki Component Technology med 3 års erfaring i SQL Server.
Jeg har en bachelorgrad i datateknikk og har også ferdigheter i .NET, C # og Windows Server. Jeg utviklet erfaring innen tuning og overvåking av ytelse, T-SQL-utvikling, backup-strategi, administrasjon og konfigurering av databaser.
Jeg brenner for IT-ting, videospill, gode filmer og elektrohusmusikk hjelper meg å jobbe når kontoret er støyende.
Vis alle innlegg av Sergiu Onet

Siste innlegg av Sergiu Onet (se alt)

• Bruk av SQL Server-markører – Fordeler og ulemper – 23. mars 2016