Intro

I relationsdatabaser udføres operationer på et sæt rækker. For eksempel returnerer en SELECT-sætning et sæt rækker, der kaldes et resultatsæt. Nogle gange skal applikationslogikken arbejde med en række ad gangen snarere end hele resultatet indstillet på én gang. I T-SQL er en måde at gøre dette på at bruge en CURSOR.

Hvis du har programmeringsevner, ville du sandsynligvis bruge en løkke som FOR eller WHILE til at gentage et element ad gangen, gør noget med dataene og jobbet er udført. I T-SQL er en CURSOR en lignende tilgang og kan foretrækkes, fordi den følger den samme logik. Men vær opmærksom på, at du tager denne vej, og problemer kan følge.

Der er nogle tilfælde, når du bruger CURSOR ikke gør så meget af et rod, men generelt bør de undgås. Nedenfor viser vi nogle eksempler, hvor brug af en CURSOR skaber ydeevneproblemer, og vi vil se, at det samme job kan udføres på mange andre måder.

I forbindelse med denne demonstration bruger vi AdventureWorks2012-databasen og Lad os sige, at vi vil hente nogle data fra. tabel for hvert produkt, der kræver mindre end en dag at fremstille, det er fra tabel ..

Et markøreksempel

Lad os starte med at bruge en CURSOR og skrive følgende syntaks:

Efter en kort kaffepause , forespørgslen er færdig med udførelsen og returnerer 833 rækker i den viste tid nedenfor. Det er vigtigt at nævne de valgte syntakser ovenfor kun er til demoformål, og jeg foretog ingen indeksindstilling for at fremskynde tingene.

I markørudførelsen har vi to trin. Trin et, placeringen, når markøren sætter sin position til en række fra resultatsættet. Trin to, hentningen, når den får dataene fra den specifikke række i en operation kaldet FETCH.

I vores eksempel indstiller markøren sin position til den første række, der returneres af den første SELECT, og henter den ProductID-værdi, der matcher WHERE-tilstanden i @id-variablen. Derefter bruger det andet SELECT variabelværdien til at hente data fra. og den næste række hentes. Disse operationer gentages, indtil der ikke er flere rækker at arbejde med.

Endelig frigiver CLOSE-syntaksen det aktuelle resultatsæt og fjerner låsen fra de rækker, der bruges af markøren, og DEALLOCATE fjerner markørreferencen.

Vores demo-tabeller er relativt små med ca. og 500 rækker. Hvis vi skulle løbe gennem tabeller med millioner af rækker, ville det vare lang tid, og resultaterne ville ikke behage os.

Lad os give vores markør endnu en chance og kommentere linjen – LOKAL STATIK. Der er mange argumenter, vi kan bruge i en markørdefinition, mere om det på dette link CURSOR Argumenter, men lad os nu fokusere på, hvad disse to ord betyder.

Når vi specificerer LOCAL nøgleord, er markørens rækkevidde lokalt for det batch, det blev oprettet i, og det er kun gyldigt i dette omfang. Når batchen er færdig med at udføres, placeres markøren automatisk. Markøren kan også henvises til ved en lagret procedure, trigger eller ved en lokal markørvariabel i en batch.

STATIC-nøgleordet laver en midlertidig kopi af de data, der bruges af markøren i tempdb i en midlertidig tabel . Her har vi nogle gotchas. En STATIC-markør er skrivebeskyttet og kaldes også et snapshot-markør, fordi den kun fungerer med dataene fra det tidspunkt, hvor den blev åbnet, hvilket betyder, at den ikke viser nogen ændringer foretaget i databasen på det datasæt, der bruges af cursoren.Dybest set vil ingen opdateringer, sletninger eller indsatser, der er foretaget efter markørens åbning, være synlige i markørens resultatsæt, medmindre vi lukker og åbner markøren igen.

Vær opmærksom på dette, inden du bruger disse argumenter, og kontroller, om det svarer til dine behov. Men lad os se, om vores markør er hurtigere. Nedenfor har vi resultaterne:

12 sekunder er meget bedre end 4 minutter og 47 sekunder, men husk begrænsninger forklaret ovenfor.

Hvis vi kører syntaksen ved hjælp af dette argument LOKAL LÆS_ONLY FORWARD_ONLY, får vi de samme resultater. READ_ONLY FORWARD_ONLY-markøren kan kun rulles fra den første række til den sidste. Hvis STATIC-nøgleordet mangler, er markøren dynamisk og bruger en dynamisk plan, hvis den er tilgængelig. Men der er tilfælde, hvor en dynamisk plan er værre end en statisk.

Hvad sker der, når vi ikke kommenterer –LOCAL FAST_FORWARD?

FAST_FORWARD svarer til LÆSER_LIGEN og FORWARD_ONLY-markøren, men har evnen til at vælge den bedre plan fra enten en statisk eller en dynamisk.

LOKAL FAST_FORWARD synes at være det bedste valg på grund af dets fleksibilitet til vælg mellem en statisk eller dynamisk plan, men FORWARD_ONLY gør også jobbet meget godt. Selvom vi fik resultatet i gennemsnit på 12 sekunder ved hjælp af begge disse metoder, skal disse argumenter testes ordentligt, inden vi vælger en af dem.

Der er mange måder at opnå det samme resultat på, meget hurtigere og med mindre indflydelse på ydeevnen.

Markøralternativ

En metode bruger JOIN, og som vi kan se næste, er resultaterne betydeligt bedre.

Først, Vi er nødt til at muliggøre statistiktid til at måle SQL Server – udførelsestid og oprette en INNER JOIN mellem to tabeller. og. i kolonnen ProductID. Efter at have trykket på EXECUTE-knappen producerede vi de samme resultater i 330 ms sammenlignet med 04:47 tid fra markørmetoden, og vi har et smil på ansigtet.

Vi behøver ikke at gentage hver række for at få det, vi har brug for, vi har ikke flere sløjfer, ingen tidsklausul, ingen iter ationer, vi arbejder med datasæt i stedet for at få det, vi ønsker hurtigere og skrive mindre kode.

En passende brug af markøren

Nu hvor vi har set, hvor meget skade en markøren kan gøre, lad os se et eksempel, hvor vi kan gøre brug af det.

Lad os antage, at vi kun vil vælge størrelsen og antallet af rækker til kun bestemte tabeller fra en database. For at opnå dette får vi alle tabelnavne baseret på kriterier fra information_schema.tables og ved hjælp af en CURSOR vil vi løbe igennem hvert af dette tabelnavn og udføre den lagrede procedure sp_spaceused ved at sende et tabelnavn ad gangen for at få de oplysninger, vi har brug for .

Vi bruger den samme AdventureWorks2012-database og får alle tabeller fra salgsskema, der indeholder navnet ‘Salg’. For hvert tabelnavn, der returneres, vil vi se al information fra information_schema.tables.

Nedenfor har vi T-SQL-syntaksen og de opnåede resultater:

Dette er en metode, hvor CURSOR er nyttigt ved at gentage nogle data en række ad gangen og få det nødvendige resultat. I dette særlige tilfælde får markøren jobbet gjort uden at have indflydelse på ydeevnen og er let at bruge.

Konklusioner

Der har vi det. Vi viste nogle eksempler med det gode, det dårlige og det grimme, når vi bruger markører. I de fleste tilfælde kan vi bruge JOINS, selv WHILE-klausuler, SSIS pakker eller andre alternative metoder for at få det samme resultat hurtigere med mindre indvirkning på ydeevneoutput og endda skrive færre syntakslinjer.

Når vi har at gøre med OLTP-miljø og store datasæt, der skal behandles, bør ordet ‘CURSOR’ ikke tales.

I SQL er det en god praksis at tænke på at udføre operationer på datasæt i stedet for at tænke på en programmatisk måde ved hjælp af iterationer eller sløjfer, fordi denne form for fremgangsmåde ikke anbefales eller er beregnet til denne anvendelse. Forsøg på at bruge sløjfer som FOR eller FOREACH fra programmeringssprog og knytte den logik til SQL-operationer er en hindring for at få den rigtige løsning til vores behov. Vi er nødt til at tænke på sæt-baserede operationer snarere end en række ad gangen for at få de data, vi har brug for.

Markører kan bruges i nogle applikationer til serielle operationer som vist i eksemplet ovenfor, men generelt skal de undgås, fordi de har en negativ indvirkning på ydeevnen, især når de opererer på et stort datasæt.

• Forfatter
• Seneste indlæg

Jeg er en DBA hos Yazaki Component Technology med 3 års erfaring i SQL Server.
Jeg har en bachelorgrad i computerteknik og har også færdigheder i .NET, C # og Windows Server. Jeg udviklede erfaring inden for tuning og overvågning af performance, T-SQL-udvikling, backup-strategi, administration og konfiguration af databaser.
Jeg brænder for it-ting, videospil, gode film og elektrohusmusik hjælper mig med at arbejde, når kontoret er støjende.
Se alle indlæg fra Sergiu Onet

Seneste indlæg af Sergiu Onet (se alle)

• Brug af SQL Server-markører – Fordele og ulemper – 23. marts 2016