Pekere kan se ut som snarveier til en utbygger. Når du har en komplisert jobb å utføre, og du trenger å manipulere rader i en tabell, den raskeste måten kan synes å gå gjennom rader, én etter én ved hjelp av en Handle-SQL-markøren. Tross alt, siden du er nødt til å gå gjennom data strukturer i din egen kode på klientsiden, kan du bli fristet til å gjøre det samme når du arbeider med SQL Server data., Men iterating gjennom data ved hjelp av Handle-SQL pekere ofte ikke skalere godt, og jeg håper å overbevise deg om at det er heller ikke en god design eller arkitektonisk praksis.

En Markør Erfaring

jeg tar opp dette fordi et par måneder siden, jeg hadde avtale med en leverandør Handle-SQL-skript som oppgradert sin database komponenter til en ny versjon av leverandørens program. De laget manuset til pivot et veldig stort bord og lagre relevante data i en ny tabell horisontalt, som sammenhengende strenger., Leverandøren ønsket å forbedre ytelsen ved å gjøre tabellen mindre, så de bestemte seg for å lagre detalj data horisontalt, som kommadelte strenger for hver av foreldrene id. Klientprogrammet kan spørre den resulterende kommaadskilt strenger raskere enn å få hver og en av dem som individuelle rader, og i den sammenheng, endringen gjorde forstand og gjorde forbedre programmets ytelse.

Imidlertid leverandørens Handle-SQL-skript for å pivot data under oppgraderingen tok 16 timer å kjøre på en testmaskin, og kunden kan ikke råd til mer enn et par timer med nedetid for oppgradering., Når vi undersøkte leverandørens script, så vi at utvikleren hadde kodet svingbare prosess i to trinn: en markøren til å gå gjennom alle de overordnede tabell-id-er å bygge en tom pre-formatert tabell, og deretter en annen skript for å sette sammen den strenger, igjen ved hjelp av en markør.

Ved hjelp av en set-basert tilnærming, vi var i stand til å redusere saksbehandlingstiden fra 16-pluss timer ned til mindre enn fem minutter. Vi fulgte developer ‘ s opprinnelige strategien, bygge tom tabell ved hjelp av SELECT-setninger, og vi redusert tid for det trinn til mindre enn to minutter., Vi så sammenhengende strenger ved hjelp av en UPDATE-setning, utført per forelder id. Våre iterasjon gjennom den overordnede ider som brukes en STUND loop, og ferdig på mindre enn tre minutter.

Uunngåelig Iterasjon

Mange har tilgang til database-data må være iterativ på noen måte for å forberede data for videre manipulering. Selv SQL Server engine-koden gjennom data når det skanner eller tiltrer data ved hjelp av ulike typer blir tilgjengelig for det. Du kan se denne når du undersøker SQL Server query plan for en spørring som returnerer mange rader fra store datasett., For en kan bli med, vil du vanligvis se en nestet loop, men noen ganger også en flette eller hash join. For enklere spørsmål, kan du se en gruppert eller ikke-gruppert index-søk. Det er bare i de tilfeller hvor SQL Server kan returnere en enkelt rad eller lite sett med rader, og bordet har en passende indeks, som du vil se en søker hjelp av en indeks.

Tenk på det: Microsoft har optimalisert og tilpasset SQL Server motor i mange år til å gå gjennom de tilgjengelige data så effektivt som mulig., Tenk deg, hvis du hadde tid og var villig til å bruke energi, du kan sikkert skrive lav-nivå tilgang til data fra database filer som ville være ganske effektiv. Det ville imidlertid være effektiv bare for den enkelte oppgave foran deg, og du ville ha til å feilsøke det og kanskje helt omskrive det hvis omfanget av dine data access var å endre. Det ville sannsynligvis ta du år for å virkelig få koden fullt optimalisert og generalisert, og selv da ville du ikke være i nærheten av effektiviteten av koden i SQL Server for lagring av motoren.,

Så hvor er det få i re-oppfinne hjulet? Det er bare fordi SQL Server-motoren er så godt optimalisert og feilsøkt, at det er bedre å la det gjøre iterating for deg og dra nytte av den omfattende utvikling og testing som allerede er innebygd i databasen.

Hvis du ser på data prosessering oppgaver nærmere, jeg tror du vil finne at det er veldig få tilfeller hvor markørene er nødvendig. Først av alt, ofte du kan oppnå dine mål ved å stole på set-basert SQL-kommandoer i Handle-SQL, og ignorerer bestilling av bord rader., For det andre, Handle-SQL markørene er bare en måte å gå gjennom en tabell, rad for rad. Hvis du kan identifisere hver rad i en tabell som du må reagere, kan du bruke en WHILE-løkke snarere enn en markør, og potensielt få bedre ytelse. La meg gå gjennom et eksempel for å vise deg hvorfor.

Sammenligne Iterasjon Strategier

Anta at du kan identifisere hver rad i en tabell, fordi tabellen har en unik nøkkel eller unik gruppe av kolonner., I en WHILE-løkke, alt du trenger å gjøre er å finne den laveste verdien av den unike tilstanden, og deretter finner du den neste høyeste verdi hver gang du iterate. Her er et eksempel fra SQL Server 2005 AdventureWorks eksempel databaser Produksjon.TransactionHistory bordet. Det har en samlet indeks på primærnøkkel, og MENS loop kan søke i rad 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 loopen ved hjelp av en SPOLE FREMOVER markøren, som er den mest effektive typen av Handle-SQL-markøren for å bare lese 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 laptop, etter at jeg kjørte et par ganger for å sikre at data er alle i hurtigbufferen, MENS loop tar ni sekunder, og markøren tar det 17 sekunder. Din egen varighet kan variere. Merk at selv om eksempelet gjør virkelig noe med data, MENS loop er raskere. Markøren tydeligvis legger mer overhead.,

markøren krever også ekstra kommandoer, som gjøre koden ser rotete. Uten å komme inn i detaljer på hvordan pekere arbeid, som Microsoft forklarer fullt i Microsoft SQL Server 2005-Bøker på Nettet, kan du legge merke til at når du bruker en STUND loop, det er ingen krav til å erklære, åpne, lukke, og deallocate noe. Logikken er enkel, og du kan selv oppdatere rader fritt langs veien. For å oppdatere rader ved hjelp av markøren, vil du endre markøren og type.

Enda en STUND loop legger overhead av iterasjon., Du kan være i stand til å erstatte den med en set-basert VELG kommandoen, eller erstatte eventuelle oppdateringer du ønsket å gjøre i loop med set-basert UPDATE-kommandoen, og la iterating til SQL Server-motoren. En enkel SELECT-setning for å få de samme data som vår markøren og MENS løkken ovenfor tar mindre enn 3 sekunder, og den returnerer rader til klienten, som er mer arbeid enn de to foregående looper gjøre.

SELECT *FROM Production.TransactionHistory

Dette VELGER er avhengig av SQL Server til å gå gjennom data, og er langt fra det raskeste av de tre metoder for tilgang til data vi har sett på.,

Fra Vesker til å Angir

noen Ganger pekere kan synes å være nødvendig. Når du rett og slett må gå gjennom data fra database, rad for rad, i deres fysiske rekkefølge, noen ganger bare en markør vil fungere. Dette oftest skjer når du har dupliserte rader og det er ingen måte å identifisere en gitt rad i tabellen. Disse tabellene er vesker, ikke-settene med data, som en «pose» gjør ikke fjerne dupliserte verdier, for eksempel et sett gjør.

Slike poser av data oppstår vanligvis når du importerer data fra en ekstern kilde, og du kan ikke helt stole på data., For eksempel, hvis AdventureWorks transaksjonen historie bordet hadde ingen gruppe med kolonner som du kan ringe unike, og/eller hadde dupliserte rader, kan du tenke at du må bruke en markør.

Men du kan alltid slå en pose med rader i en normalisert bordet. Selv om du har dupliserte rader i en tabell, eller ingen sett med kolonner du kan stole på for unikhet, kan du legge til en id-kolonne i tabellen og frø identiteten til start nummerering med 1. Dette gir en unik nøkkel til bordet, slik at du kan bruke en WHILE-løkke i stedet for markøren., Når du har en unik nøkkel, kan du fjerne duplikater ved hjelp av Handle-SQL-set-basert UPDATE-kommandoen.

Logisk API til Database Data

ved Hjelp av set-basen er bedre enn iterating data selv i minst to måter.

Først, set-basert SQL-kommandoer er mer effektiv fordi du bruker SQL Server er svært optimalisert motor å gjøre din iterasjon. Hvis du gå gjennom data selv, er du ikke bruker SQL Server for lagring av motoren optimalt. I stedet, du er peppering det med kommandoer for å hente bare én rad om gangen., Hver gang du ber om en enkelt rad, din kommando må gå gjennom SQL Server optimizer før det kan komme til lagring motor, og du ender opp med å ikke bruke SQL Server storage-motoren er optimalisert kode. Hvis du iterated selv, kan du også stole på utenforliggende fysisk opplysninger på bordet, nemlig rekkefølgen på radene, når dataene behandles. Set-base-Handle-SQL SELECT, UPDATE og DELETE kommandoer gi deg en måte å ignorere rekkefølgen på radene og bare påvirker dem basert på egenskapene av data-og de er raskere.,

Andre, set-basert kommandoer er mer logisk fordi tenker på data i sett abstracts deg bort fra uvedkommende detaljer som er mer opptatt av hvordan data er faktisk er bestilt. Faktisk, set-basert kommandoer som VELGER, OPPDATERE, og SLETT, når den brukes til tabeller og ikke i en markør, eller MENS loop, bringe deg nærmere logisk til dine data, nettopp fordi du kan ignorere den rekkefølgen av data.,

Her er en annen måte å tenke om dette andre punktet-Akkurat som lagrede prosedyrer er den mest naturlige API for programmer for å samhandle med SQL Server programmatisk, så set-basert SQL-kommandoer er riktig API for tilgang til relasjonelle data. Lagrede prosedyrer skille din søknad fra databasen innvendige, og de er mer effektive enn ad-hoc spørringer. På samme måte, set-base-SQL-kommandoer inne Handle-SQL gi en logisk grensesnitt til din relasjonelle data, og de er mer effektiv fordi du stole på SQL Server storage engine for iterating gjennom data.,

poenget er ikke at iterating gjennom data er dårlig. Faktisk, det er ofte uunngåelig. Snarere, poenget er, la lagring motor gjøre det for deg, og i stedet stole på de logiske grensesnitt av set-basert Handle-SQL-kommandoer. Jeg tror du vil finne få, om noen situasjoner der du må faktisk bruke en Handle-SQL-markøren.