av: Daniel Farina / uppdaterad: 2019-09-12 / kommentarer (2)/relaterad: mer > T-SQL

Problem

duhar alltid hört att du bör undvika markörer idin T-SQL-kod som en SQL Server bästa praxis, eftersomkursorer är skadliga för prestandaoch ibland orsaka problem. Men ibland finns det ett behov av att slinga genomdata en rad i taget, så i det här tipset kommer vi att titta på en jämförelse av hur man gör en slinga utan att använda markören.,

lösning

vi vet alla att SQL Server, liksom varje relationsdatabas tillåter användaren toperform set baserade operationer. Också, som många databasleverantörer gör, innehåller SQL serveren procedur förlängning som är T-SQL-språket. Det lägger till konstruktioner som finns iprocedurala språk som möjliggör en enklare kodning till utvecklare. Dessa riktlinjer har lagts till av en anledning och ibland är detta det enda tillvägagångssättet för den aktuella uppgiften.,

använda en While Loop istället för markörer i SQL Server

om du någonsin har arbetat medkursorer kan du hitta den här titeln lite förvirrandeeftersom markörer trots allt använder medan de konstruerar för att iterera mellan rader. Men förutom att jag vill visa dig att under vissa omständigheter när vi använder en markör för att iterera över en uppsättning rader kan vi ändra den till ett tag slinga. I sådana fall är den enda utmaningenkommer att vara att välja ett korrekt utgångstillstånd.,

fördelar och nackdelar med att använda markörer för att iterera genom tabellrader i SQL Server

inte allt är fel med markörer, de har också några fördelaröver andra looping tekniker.

  • markörer är uppdaterbara: när du skapar en markör använder du en fråga för att definiera den med hjälp av instruktionen förklara markör. Genom att använda uppdateringsalternativet i markörkreationssatsen kan du uppdatera kolumnerna i markören.,
  • Du kan gå framåt och bakåt i en markör: genom att använda RULLNINGSALTERNATIVET i deklarera MARKÖRPOSTEN kan du navigera över markörposterna ibåde riktningar med hämtningsalternativen först, sist, före, nästa, relativ ochabsolut. Tänk på att RULLNINGSALTERNATIVET är inkompatibelt med alternativen Forward_only och FAST_FORWARD.
  • markörer kan skickas till lagrade procedurer: om du använder det globala alternativetför att skapa en markör kan den användas i alla lagrade procedurer eller batch som exekveras i samma anslutning. Detta gör att du kan använda markörer på kapslade lagrade procedurer.,
  • markörer har många olika alternativ: med markörer har du chanceatt använda olika alternativ som påverkar hur de kommer att bete sig när det gäller låsning.
  • markörer behöver inte ett villkor: genom att använda markörer, du handlinga uppsättning rader som en post. Detta gör att du kan flytta över markören utanbehovet av att ha ett booleskt tillstånd. Du kan till exempel skapa en markörmed namnet på databaserna som finns i en SQL Server-instans utan att behöva en surrogatnyckel för att fungera som ett testförhållande som på en stund loop.,

det finns också några negativa aspekter som du bör vara medveten om när du använder cursorsinstead av andra looping alternativ.

  • Om du använder globala markörer i din kod tar du risken för ansiktsfel på grund av att en markör stängs av någon lagrad procedur kapslad i yourcode.
  • vanligtvis markörer har mindre prestanda än en ekvivalent slinga med hjälp av en WHILEloop eller CTE.

fördelar och nackdelar med att använda en While Loop för att iterera genom tabellrader i SQL Server

det finns också fördelar att använda en WHILE loop jämfört med en markör.,

  • medan loopar är snabbare än markörer.
  • medan loopar använder mindre lås än markörer.
  • mindre användning av Tempdb: medan loopar inte skapar en kopia av data intempdb som en markör gör. Kom ihåg att markörer, beroende på alternativen duanvänd för att skapa dem kan orsaka att temp-tabellerna skapas.

nästa lista beskriver de negativa aspekterna av medan slingor.

  • att gå framåt eller bakåt är komplext: för att gå framåt eller bakåt i aloop måste du dynamiskt ändra iterationstillståndet inuti slingan.,Detta kräver extra omsorg; annars kan du hamna i en oändlig slinga.
  • risken för en oändlig loop: jämfört med en markör har du inte en fast uppsättning data till loop (dvs. data som returneras av SELECT-uttalandeti markördeklarationen), istället när du använder en WHILE loop måste du definiera en gränsmed ett uttryck som utvärderas till Sant eller falskt.

bygga testmiljön för markörer och loopar

för att testa detta, Jag kommer att använda en tabell med en identitetskolumn (CursorTestID),en varchar kolumn (fyllmedel) och en bigint kolumn (RunningTotal).,

CREATE TABLE CursorTest( CursorTestID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Filler VARCHAR(4000), RunningTotal BIGINT )GO

tanken är att slinga igenom tabellraderna som beställts av CursorTestID columnoch uppdatera runningtotal-kolumnen med summan av cursortestidkolumnen valueoch värdet av runningtotal-kolumnen i föregående rad.

men innan vi börjar måste vi först generera några testrader med nästa skript.,

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

på skriptet ovan kommer du att märka att jag bara använde en enda insert-statementoch jag utnyttjade batchseparator (kommandot GO 500000) som en genvägatt utföra denna insert-statement 500000 gånger. Du kan läsa mer om den här metodenför att upprepa batchkörning på det här tipset: kör en T-SQL-sats flera gånger med GO.

exempel på en grundläggande markör för att slinga genom tabellrader i SQL Server

låt oss skapa en markör för att fylla runningtotal-kolumnen. Lägg märke till på nextscript att jag förklarade markören med alternativet FAST_FORWARD., Detta görs inorder för att förbättra prestanda för markören eftersom enligt Microsoft thefast_forward argument ”anger en FORWARD_ONLY, READ_ONLY markör med performanceoptimizations aktiverat”. Med andra ord instruerar vi SQL Server att användaen skrivskyddad markör som bara kan gå framåt och rullas från den första tillDen sista raden.

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

nästa bild är en skärmdump som visar utförandet av skriptet above.As du kan se, det tog tre minuter och fem sekunder att uppdatera 500 000 rader av vårt testbord.,

exempel på en grundläggande medan slinga för att bläddra igenom tabellrader i SQL Server

Nu ska jag skriva om det tidigare skriptet för att undvika användning av en markör. Du kommer att märka att den innehåller ett tag slinga som är nästan identisk med den i thecursor script. Detta är, som jag tidigare sagt, för även när du arbetar med markördu måste använda en iterativ kontrollstruktur.

nästa bild är en skärmdump av utförandet av skriptet ovan. Det tookless tid att köra while loop än markören.,

en annan SQL Server markör exempel

låt oss ta till exempel markören i tipStandardize SQL Server data med text lookup och ersätta funktion. Ett ord avråd, för att köra den här koden, bör du följa stegen i spetsen för att skapatestmiljön.

och här är markörkoden:

om vi dissekerar den här koden kan vi se att det finns en markör som går genom tabellprodukterna som jag kopierade nedan.,

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

SQL Server Cursor exempel konverteras till ett tag Loop

för att ersätta den här markören med ett tag LOOP, måste vi skapa en temporarytable att genomföra en tally tabell. För alla er som inte vet vad en tallytable är, kan vi definiera den som en tabell som innehåller ett par kolumner som består av en nyckel och dess värde. I vårt speciella fall kommer vi att använda ett sekventiellt heltal från och med 1, Så vi kan använda det som en iterator. Denna nyckel kommer att associerastill en produkt från tabellen Produkter.,

först, eftersom produkttabellen har ProductID-tangenten definierad som en identitetdu kan frestas att kringgå det här steget, men du måste överväga att i en riktig fallrad kunde ha tagits bort, därför kommer du inte att kunna använda identitetkolumn som en iterator. Dessutom kan en rad tas bort medan vi kör ourcode och det kan leda till exekveringsfel. För att undvika detta kommer vi att lägga till aTRY-CATCHblock. Jag kommer att gå in på detta ytterligare.

innan du startar WHILE loop måste vi ställa in start-och stoppförhållandet., Fördenna fråga lade jag till två nya heltalvariabler som heter @Iterator och @MaxIterator.Variabeln @MaxIterator används för att hålla antalet objekt i # Tallytabletabletabletable och vi ställer in dess värde endast en gång innan slingan startas. @ Iterator variableinitieras till 1, som vi definierade det som startnummer på sekvensen ochvi kommer att öka sitt värde vid varje iteration.

nästa steg
  • Är du ny på markörer och behöver lite övning? I nexttip hittar du en förklaring, ett lätt att förstå markör exempel och merrekommenderade avläsningar: SQL Server markör exempel.,
  • Om du vill konvertera de befintliga markörer i din kod för att ställa baserade frågor,ta en titt på denna chapterSQL Server Konvertera Cursorto som Bygger från theSQL Server-Databas Design och Bästa Praxis för Handledning.
  • behöver du ett annat exempel på att använda en While loop? Ta en titt på det här tipset som visar dig hur du delar DML-satser i satser: optimera stora SQL ServerInsert, uppdatera och ta bort processer med hjälp av satser.
  • Om du inte vet hur du använder TRY…Fånga exceptionhandling, ta en titt på det här tipset: SQL Server försök och fånga Undantagshantering.,
  • håll ögonen öppna till theSQL Server T-SQL Tipscategory för att få fler kodningsidéer.

Senast uppdaterad: 2019-09-12

om författaren
Daniel farina föddes i Buenos Aires, Argentina. Självutbildad, sedan barndomen visade han en passion för lärande.
Visa alla mina tips
relaterade resurser

  • fler Databasutvecklartips…

Lämna ett svar

Din e-postadress kommer inte publiceras. Obligatoriska fält är märkta *