markörer kan se ut som genvägar till en utvecklare. När du har ett komplext jobb att utföra och du måste manipulera raderna i en tabell, kan det snabbaste sättet verkar iterera genom raderna en efter en med hjälp av en Transact-SQL-markör. Eftersom du måste iterera genom datastrukturer i din egen kod på klientsidan kan du bli frestad att göra detsamma när du arbetar med SQL Server-data., Men iteration genom data med Transact-SQL markörer ofta inte skala väl, och jag hoppas att övertyga dig om att det inte heller är en bra design eller arkitektonisk praxis.

en Markörupplevelse

jag tar upp det här eftersom jag för några månader sedan var tvungen att hantera en leverantörs Transact-SQL-skript som uppgraderade sin databaskomponent till en ny version av leverantörens ansökan. De utformade skriptet för att pivot en mycket stor tabell och lagra relevanta data i nya tabellen horisontellt, som sammanfogade strängar., Säljaren ville förbättra prestanda genom att göra tabellen mindre, så de bestämde sig för att lagra detaljdata horisontellt, som kommaavgränsade strängar för varje överordnat id. Klientprogrammet kunde fråga de resulterande kommaavgränsade strängarna snabbare än att få var och en av dem som enskilda rader, och i sammanhanget var förändringen meningsfull och förbättrade programmets prestanda.

leverantörens Transact-SQL-skript för att pivot data under uppgraderingen tog 16 timmar att köra på en testmaskin, och kunden hade inte råd med mer än några timmars driftstopp för uppgraderingen., När vi undersökte leverantörens skript såg vi att utvecklaren hade kodat pivoteringsprocessen i två steg: en markör för att iterera genom alla överordnade tabellens ID för att bygga en tom förformaterad tabell och sedan ett annat skript för att sammanfoga strängarna, igen med en markör.

genom att använda ett set-baserat tillvägagångssätt kunde vi minska bearbetningstiden från 16-plus timmar till mindre än fem minuter. Vi följde utvecklarens ursprungliga strategi, byggde den tomma tabellen med utvalda uttalanden, och vi minskade tiden för det steget till mindre än två minuter., Vi sammanfogade sedan strängarna med hjälp av en uppdateringssats, utförd per överordnat id. Vår iteration genom föräldrarnas ID använde en stund slinga, och slutade på mindre än tre minuter.

Iterationens oundviklighet

många åtkomst till databasdata måste vara iterativa på något sätt för att förbereda uppgifterna för ytterligare manipulation. Även SQL Server-motorn itererar genom data när den skannar eller ansluter data med hjälp av olika typer av kopplingar tillgängliga för den. Du kan se detta när du undersöker SQL Server query plan för en fråga som returnerar många rader från en stor datamängd., För en koppling, du kommer oftast se en kapslade slinga, men ibland också en sammanfogning eller hash ansluta. För enklare frågor kan du se en grupperad eller icke-grupperad indexskanning. Det är bara i de fall där SQL Server kan returnera en enda rad eller liten uppsättning rader, och tabellen har ett lämpligt index, som du ser en sökning med hjälp av ett index.

Tänk på det: Microsoft har optimerat och ställt in SQL Server-motorn i flera år för att iterera genom sina tillgängliga data så effektivt som möjligt., Tänk dig, om du hade tid och var villig att spendera energi, du kan förmodligen skriva låg nivå åtkomst till databas datafiler som skulle vara ganska effektiv. Det skulle dock vara effektivt endast för den enskilda uppgiften framför dig, och du måste felsöka den och kan behöva helt skriva om den om omfattningen av din dataåtkomst skulle ändras. Det skulle förmodligen ta dig år att verkligen få koden helt optimerad och generaliserad, och även då skulle du inte vara nära effektiviteten av koden inuti SQL Server-lagringsmotorn.,

så var är vinsten i att uppfinna hjulet igen? Det är bara för att SQL Server-motorn är så väl optimerad och debugged, att det är bättre att låta det göra iteration för dig och dra nytta av den omfattande utveckling och testning som redan är inbäddad i databasen.

om du tittar närmare på dina databehandlingsuppgifter, tror jag att du kommer att upptäcka att det finns väldigt få tillfällen där markörer krävs. Först av allt, ofta kan du uppnå ditt mål genom att förlita sig på set-baserade SQL-kommandon i Transact-SQL, och ignorera ordningen på en tabells rader., För det andra är Transact-SQL-markörer bara ett sätt att iterera genom en tabellrad för rad. Om du unikt kan identifiera varje rad i en tabell som du måste iterera, kan du använda ett tag loop snarare än en markör, och potentiellt få bättre prestanda. Låt mig gå igenom ett exempel för att visa dig varför.

jämföra Iterationsstrategier

anta att du unikt kan identifiera varje rad i en tabell eftersom tabellen har en unik nyckel eller unik grupp av kolumner., På ett tag loop, allt du behöver göra är att hitta det lägsta värdet av det unika tillståndet, och sedan hitta nästa högsta värdet varje gång du iterera. Här är ett exempel från SQL Server 2005 AdventureWorks provdatabaser produktion.Transaktionhistoriskt bord. Den har en grupperad index på den primära nyckeln, och medan slingan kan söka i raden varje gång.,

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

här är samma slinga med en snabbspolning framåt markör, vilket är den mest effektiva typen av Transact-SQL markör för att bara läsa 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ärbara dator, efter att jag körde det några gånger för att se till att data är alla i cache, medan slingan tar nio sekunder och markören tar 17 sekunder. Din egen varaktighet kan variera. Observera att även om exemplet verkligen inte gör något med data, medan slingan är snabbare. Markören lägger uppenbarligen mer overhead.,

markören kräver också ytterligare kommandon, vilket gör att koden ser rörig ut. Utan att komma in i detaljerna om hur markörer fungerar, vilket Microsoft förklarar helt i Microsoft SQL Server 2005 Books Online, märker att när du använder en stund loop, det finns inget krav på att deklarera, öppna, stänga, och deallocate något. Logiken är enklare, och du kan till och med uppdatera rader fritt längs vägen. För att uppdatera raderna med markören måste du ändra markörtypen.

även ett tag loop lägger overhead av iteration., Du kanske kan ersätta den med ett set-baserat SELECT-kommando eller ersätta alla uppdateringar du ville göra i din loop med kommandot set-based UPDATE och lämna iteration till SQL Server-motorn. Ett enkelt SELECT-uttalande för att få samma data som vår markör och medan loop ovan tar mindre än 3 sekunder, och det returnerar raderna till klienten, vilket är mer arbete än de två tidigare slingorna gör.

SELECT *FROM Production.TransactionHistory

den här väljaren bygger på SQL Server för att iterera genom data, och är överlägset den snabbaste av de tre metoderna för dataåtkomst vi har tittat på.,

från påsar till uppsättningar

Ibland kan markörer tyckas vara nödvändiga. När du helt enkelt måste iterera genom databasdata, rad för rad, i sin fysiska ordning, ibland fungerar bara en markör. Detta händer oftast när du har dubbla rader och det finns inget sätt att unikt identifiera en viss rad i tabellen. Dessa tabeller är väskor, inte uppsättningar, av data, som en ”väska” eliminerar inte dubbla värden, som en uppsättning gör.

sådana datasäckar uppstår vanligtvis när du importerar data från en extern källa och du kan inte helt lita på data., Till exempel, om vår AdventureWorks transaktionshistoriktabell inte hade någon grupp kolumner som du kan kalla unika och/eller hade dubbla rader, kanske du tror att du måste använda en markör.

Du kan dock alltid vända en påse med rader till en normaliserad tabell. Även om du har dubbla rader i en tabell, eller ingen uppsättning kolumner som du kan lita på för unikhet, kan du lägga till en identitetskolumn i tabellen och frö identiteten för att börja numrera med 1. Detta lägger till en unik nyckel i tabellen, så att du kan använda en WHILE loop istället för en markör., När du har en unik nyckel kan du ta bort dubbletter med kommandot Transact-SQL set-based UPDATE.

den logiska API till databasdata

använda set-base operationer är bättre än att iterera data själv på minst två sätt.

först, set-baserade SQL-kommandon är mer effektiva eftersom du använder SQL Server högoptimerad motor för att göra din iteration. Om du itererar genom data själv använder du inte SQL Server-lagringsmotorn optimalt. Istället pepprar du det med kommandon för att hämta bara en enda rad åt gången., Varje gång du begär en enda rad måste ditt kommando gå igenom SQL Server optimizer innan det kan komma till lagringsmotorn, och du slutar inte använda SQL Server-lagringsmotorns optimerade kod. Om du itererade dig själv, förlitar du dig också på extern fysisk information om tabellen, nämligen radernas ordning, vid behandling av data. Kommandona set-base Transact-SQL SELECT, UPDATE och DELETE ger dig ett sätt att ignorera ordningen på raderna och bara påverka dem baserat på dataens egenskaper-och de är snabbare.,

andra, set-baserade kommandon är mer logiska eftersom tänkande om data i uppsättningar abstraherar dig bort från främmande detaljer som är mer oroade över hur data faktiskt beställs. Faktum är att set – baserade kommandon som SELECT, UPDATE och DELETE, när de appliceras på tabeller direkt och inte i en markör eller medan loop, tar dig närmare logiskt till dina data, just för att du kan ignorera dataordningen.,

Här är ett annat sätt att tänka på den här andra punkten – precis som lagrade procedurer är det mest naturliga API för applikationer att interagera med SQL Server programmatiskt, så set-baserade SQL-kommandon är lämpliga API för att komma åt relationsdata. Lagrade procedurer frikoppla din ansökan från Databas internal, och de är mer effektiva än ad hoc-frågor. På samma sätt ger set-base SQL-kommandon inuti Transact-SQL dig ett logiskt gränssnitt till dina relationsdata, och de är effektivare eftersom du är beroende av SQL Server – lagringsmotorn för iterering genom data.,

summan av kardemumman är inte att iterera genom data är dåligt. Faktiskt, ofta är det oundvikligt. Snarare är poängen, låt lagringsmotorn göra det för dig och lita istället på det logiska gränssnittet för de set-baserade Transact-SQL-kommandona. Jag tror att du hittar några om några situationer där du faktiskt måste använda en Transact-SQL-markör.