markører kan ligne genveje til en udvikler. Når du har et komplekst job at udføre, og du skal manipulere rækkerne i en tabel, kan den hurtigste måde synes at gentage gennem rækkerne en efter en ved hjælp af en Transact-s .l-markør. Når alt kommer til alt, da du er nødt til at gentage gennem datastrukturer i din egen kode på klientsiden, kan du blive fristet til at gøre det samme, når du har at gøre med s .l Server-data., Men iterering gennem data ved hjælp af Transact-s .l markører skalerer ofte ikke godt, og jeg håber at overbevise dig om, at det heller ikke er et godt design eller arkitektonisk praksis.

en Markøroplevelse

Jeg bringer dette op, fordi jeg for et par måneder siden var nødt til at beskæftige mig med en sælgers Transact-s .l-script, der opgraderede deres databasekomponent til en ny version af sælgerens applikation. De designet scriptet til at dreje en meget stor tabel og gemme de relevante data i ny tabel vandret, som sammenkædede strenge., Sælgeren ønskede at forbedre ydeevnen ved at gøre tabellen mindre, så de besluttede at gemme detaljedataene vandret, som kommaseparerede strenge for hvert forælder-id. Klientapplikationen kunne forespørge de resulterende kommaseparerede strenge hurtigere end at få hver af dem som individuelle rækker, og i sammenhængen var ændringen fornuftig og forbedrede applikationens ydeevne.

sælgerens Transact-s .l-script til at dreje dataene under opgraderingen tog dog 16 timer at køre på en testmaskine, og kunden havde ikke råd til mere end et par timers nedetid til opgraderingen., Da vi undersøgte leverandørens script, så vi, at udvikleren havde kodet drejeprocessen i to trin: en markør til at gentage gennem alle de overordnede tabel-id ‘ er for at opbygge en tom forformateret tabel og derefter et andet script til at sammenkæde strengene igen ved hjælp af en markør.

ved at bruge en set-baseret tilgang var vi i stand til at reducere behandlingstiden fra 16-plus timer ned til mindre end fem minutter. Vi fulgte udviklerens oprindelige strategi og byggede den tomme tabel ved hjælp af udvalgte udsagn, og vi reducerede tiden for det trin til mindre end to minutter., Vi sammenkædede derefter strengene ved hjælp af en OPDATERINGSERKLÆRING, udført pr. Vores iteration gennem forældrenes ID ‘ er brugte et stykke tid loop og sluttede på mindre end tre minutter.

Uundgåeligheden af Iteration

mange adgang til databasedata skal være iterative på en eller anden måde for at forberede dataene til yderligere manipulation. Selv S .l Server-motoren gentager gennem data, når den scanner eller tilslutter data ved hjælp af de forskellige typer sammenføjninger, der er tilgængelige for den. Du kan se dette, når du undersøger s .l Server forespørgselsplanen for en forespørgsel, der returnerer mange rækker fra et stort datasæt., For en join, vil du oftest se en indlejret loop, men nogle gange også en fusion eller hash deltage. For enklere forespørgsler kan du muligvis se en grupperet eller ikke-grupperet indeksscanning. Det er kun i de tilfælde, hvor S .l Server kan returnere en enkelt række eller lille sæt rækker, og tabellen har en passende indeks, at du vil se en søge hjælp af et indeks.

tænk over det: Microsoft har optimeret og indstillet s .l Server-motoren i årevis for at gentage gennem sine tilgængelige data så effektivt som muligt., Forestil dig, hvis du havde tid og var villig til at bruge energien, kunne du sandsynligvis skrive adgang på lavt niveau til databasedatafiler, der ville være ret effektive. Det ville dog kun være effektivt for den enkelte opgave foran dig, og du bliver nødt til at fejle den og måske nødt til at omskrive den fuldstændigt, hvis omfanget af din datatilgang skulle ændre sig. Det vil sandsynligvis tage dig år at få koden fuldt optimeret, og generaliseret, og selv da ville du ikke være tæt på effektiviteten af koden inde i SQL Server storage engine.,

så hvor er gevinsten ved at genopfinde hjulet? Det er bare fordi SQL Server motor er så godt optimeret og fejlrettet, at det er bedre at lade den gøre det iteration for dig og drage fordel af den omfattende udvikling og-test, der allerede er indlejret i databasen.

Hvis du ser nærmere på dine databehandlingsopgaver, tror jeg, du vil opdage, at der virkelig er meget få lejligheder, hvor markører er påkrævet. Først og fremmest kan du ofte nå dit mål ved at stole på de sætbaserede s .l-kommandoer i Transact-s .l og ignorere rækkefølgen af en Bords rækker., For det andet er Transact-s .l markører kun en måde at gentage gennem en tabel række for række. Hvis du entydigt kan identificere hver række i en tabel, som du skal gentage, kan du bruge et stykke tid løkke snarere end en markør, og potentielt få bedre ydeevne. Lad mig lede dig gennem et eksempel for at vise dig hvorfor.

sammenligning af Iterationsstrategier

Antag, at du entydigt kan identificere hver række i en tabel, fordi tabellen har en unik nøgle eller en unik gruppe af kolonner., I et stykke tid loop, alt hvad du skal gøre er at finde den laveste værdi af den unikke tilstand, og find derefter den næste højeste værdi hver gang du gentager. Her er et eksempel fra S .l Server 2005 Adventure .orks prøve databaser produktion.TransactionHistory tabel. Det har et grupperet indeks på den primære nøgle, og mens løkken kan søge ind i rækken hver gang.,

USE AdventureWorksGODECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int SET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory)WHILE @TransactionID IS NOT NULLBEGINSET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory WHERE TransactionID > @TransactionID)END

Her er den samme sløjfe ved hjælp af en SPOLE FREM markøren, som er den mest effektive type af Transact-SQL markør for bare at læse data:

DECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int DECLARE AW_Cursor CURSOR FORWARD_ONLYFORSELECT TransactionID, TransactionType, QuantityFROM Production.TransactionHistory OPEN AW_Cursor FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @Quantity WHILE @@FETCH_STATUS = 0BEGIN FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @QuantityEND CLOSE AW_Cursor DEALLOCATE AW_Cursor

På min bærbare computer, efter at jeg kørte det et par gange for at sikre, at data er alle i cache, MENS løkke tager ni sekunder og markøren tager 17 sekunder. Dine egne varigheder kan variere. Bemærk, at selvom eksemplet virkelig ikke gør noget med dataene, er WHILEHILE-løkken hurtigere. Markøren tilføjer åbenbart mere overhead.,

markøren kræver også yderligere kommandoer, som får koden til at se rodet ud. Uden at komme nærmere ind på, hvordan markører arbejde, som Microsoft forklarer fuldt ud i Microsoft SQL Server 2005 Bøger Online, bemærke, at når du bruger en WHILE-løkke, der er ingen krav til at erklære, åbne, lukke, og deallocate noget. Logikken er enklere, og du kan endda opdatere rækker frit undervejs. For at opdatere rækkerne ved hjælp af markøren skal du ændre markørtypen.

selv et stykke tid loop tilføjer overhead af iteration., Du kan muligvis erstatte den med en sætbaseret SELECT-kommando eller erstatte opdateringer, du ønskede at gøre i din løkke med den sætbaserede OPDATERINGSKOMMANDO, og overlade iterationen til S .l Server-motoren. En simpel SELECT-sætning for at få de samme data som vores markør og Mens loop ovenfor tager mindre end 3 sekunder, og det returnerer rækkerne til klienten, hvilket er mere arbejde end de to foregående sløjfer gør.

SELECT *FROM Production.TransactionHistory

dette valg er afhængig af, AT S .l Server gentager dataene, og er langt den hurtigste af de tre metoder til datatilgang, vi har set på.,

fra poser til sæt

nogle gange kan markører synes at være nødvendige. Når du blot skal gentage gennem databasedata, række for række, i deres fysiske rækkefølge, nogle gange kun en markør vil arbejde. Dette sker oftest, når du har dublerede rækker, og der er ingen måde at entydigt identificere en given række i tabellen. Disse tabeller er poser, ikke sæt, af data, da en ‘taske’ ikke eliminerer duplikatværdier, som et sæt gør.

sådanne poser med data opstår normalt, når du importerer data fra en ekstern kilde, og du kan ikke helt stole på dataene., For eksempel, hvis vores Adventure .orks transaktion historie tabel havde ingen gruppe af kolonner, som du kunne kalde unikke, og/eller havde dublerede rækker, du måske tror, at du skal bruge en markør.

Du kan dog altid omdanne en pose med rækker til et normaliseret bord. Selv hvis du har dublerede rækker i en tabel, eller intet sæt kolonner, du kan stole på for unikhed, kan du tilføje en identitetskolonne til tabellen og frø identiteten for at starte nummerering med 1. Dette tilføjer en unik nøgle til tabellen, så du kan bruge et stykke tid løkke i stedet for en markør., Når du har en unik nøgle, kan du fjerne dubletter ved hjælp af kommandoen Transact-s .l set-based UPDATE.

den logiske API til databasedata

brug af set-base-operationer er bedre end at gentage dataene selv på mindst to måder. for det første er sætbaserede s .l-kommandoer mere effektive, fordi du bruger s .l servers stærkt optimerede motor til at udføre din iteration. Hvis du selv gentager data, bruger du ikke s .l Server storage engine optimalt. I stedet pepper du det med kommandoer for at hente kun en enkelt række ad gangen., Hver gang du anmoder om en enkelt række, skal din kommando gå gennem s .l Server optimi .er, før den kan komme til lagringsmotoren, og du ender med ikke at bruge S .l Server storage engine ‘ s optimerede kode. Hvis du gentager dig selv, er du også afhængig af fremmede fysiske oplysninger om tabellen, nemlig rækkefølgen af rækkerne, når du behandler dataene. Set-base Transact – s .l SELECT, UPDATE og DELETE kommandoer giver dig en måde at ignorere rækkefølgen af rækkerne og bare påvirke dem baseret på dataens egenskaber-og de er hurtigere.,

for det andet er sætbaserede kommandoer mere logiske, fordi det at tænke på data i sæt abstraherer dig væk fra fremmede detaljer, der er mere optaget af, hvordan dataene faktisk bestilles. Faktisk, set-baserede kommandoer som SELECT, UPDATE og DELETE, når de anvendes til tabeller direkte og ikke i en markør eller Mens loop, bringe dig tættere logisk på dine data, netop fordi du kan ignorere rækkefølgen af data.,

Her er en anden måde at tænke på dette andet punkt-ligesom lagrede procedurer, der er den mest naturlige API for programmer til at kommunikere med SQL Server ved hjælp af programmering, så sæt-baseret SQL-kommandoer, der er relevante API for adgang til relationelle data. Lagrede procedurer afkoble din ansøgning fra databasen internals, og de er mere effektive end ad hoc-forespørgsler. Ligeledes, set-base SQL-kommandoer inde i Transact-SQL give dig et logisk interface til din relationelle data, og de er mere effektiv, fordi du er afhængige af SQL Server-storage engine for iterere gennem data.,

den nederste linje er ikke, at iterering gennem data er dårlig. Faktisk er det ofte uundgåeligt. Snarere er pointen, lad lagringsmotoren gøre det for dig og stole i stedet på den logiske grænseflade af de sætbaserede Transact-s .l-kommandoer. Jeg tror, du finder få, hvis nogen situationer, hvor du faktisk skal bruge en Transact-s .l-markør.