Intro

I relationsdatabaser görs operationer på en rad rader. Till exempel returnerar en SELECT-sats en uppsättning rader som kallas en resultatuppsättning. Ibland måste applikationslogiken fungera med en rad i taget snarare än hela resultatuppsättningen på en gång. I T-SQL är ett sätt att göra detta att använda en CURSOR.

Om du har programmeringskunskaper skulle du förmodligen använda en loop som FOR eller WHILE för att itera igenom ett objekt i taget, gör något med uppgifterna och jobbet är gjort. I T-SQL är en CURSOR ett liknande tillvägagångssätt och kan föredras eftersom det följer samma logik. Men tänk på att ta den här vägen och problem kan följa.

Det finns vissa fall när CURSOR inte gör så mycket röra, men i allmänhet bör de undvikas. Nedan visar vi några exempel där användning av en CURSOR skapar prestandafrågor och vi kommer att se att samma jobb kan utföras på många andra sätt.

För denna demonstrations syfte kommer vi att använda AdventureWorks2012-databasen och låt oss säga att vi vill hämta lite data från. tabell, för varje produkt som kräver mindre än en dag att tillverka, det vill säga från tabellen ..

Ett markörsexempel

Låt oss börja med att använda en PILJÄR och skriva följande syntax:

Efter en kort kaffepaus , slutförde frågan och körde 833 rader under tiden som visas nedan. Det är viktigt att nämna de valda syntaxerna ovan endast är för demoändamål, och jag gjorde ingen indexjustering för att påskynda saker.

I markörkörningen har vi två steg. Steg ett, positioneringen, när markören ställer in sin position till en rad från resultatuppsättningen. Steg två, hämtningen, när den hämtar data från den specifika raden i en operation som heter FETCH.

I vårt exempel ställer markören sin position till den första raden som returneras av den första SELECT och hämtar ProductID-värdet som matchar WHERE-tillståndet i @id-variabeln. Sedan använder den andra SELECT variabelvärdet för att hämta data från. och nästa rad hämtas. Dessa operationer upprepas tills det inte finns fler rader att arbeta med.

Slutligen släpper CLOSE syntax den aktuella resultatuppsättningen och tar bort lås från raderna som används av markören, och DEALLOCATE tar bort markörreferensen.

Våra demotabeller är relativt små som innehåller ungefär 1000 och 500 rader. Om vi var tvungna att gå igenom tabeller med miljontals rader skulle det ta lång tid och resultaten skulle inte behaga oss.

Låt oss ge vår markör en ny chans och avmarkera raden – LOKAL STATIK. Det finns många argument vi kan använda i en markördefinition, mer om det på den här länken CURSOR Arguments, men låt oss nu fokusera på vad dessa två ord betyder.

När vi anger LOCAL-nyckelord är markörens omfång lokalt för det parti som det skapades i och det är endast giltigt i detta omfång. När körningen är klar körs markören automatiskt om. Markören kan också hänvisas till genom en lagrad procedur, trigger eller genom en lokal markörvariabel i en sats.

STATIC-nyckelordet gör en tillfällig kopia av de data som används av markören i tempdb i en tillfällig tabell . Här har vi några gotchas. En STATIC-markör är skrivskyddad och kallas också en ögonblicksbildsmarkör eftersom den bara fungerar med data från det att den öppnades, vilket innebär att den inte visar några ändringar som gjorts i databasen på den uppsättning data som används av markören.I grund och botten kommer inga uppdateringar, raderingar eller inlägg som gjorts efter att markören var öppen att visas i markörens resultatuppsättning om vi inte stänger och öppnar markören igen.

Var medveten om detta innan du använder dessa argument och kontrollera om det matchar dina behov. Men låt oss se om vår markör är snabbare. Nedan har vi resultaten:

12 sekunder är mycket bättre än 4 minuter och 47 sekunder, men kom ihåg att begränsningar som förklarats ovan.

Om vi kör syntaxen med hjälp av detta argument LOKALT LÄS FÖRVAREND_ONLY får vi samma resultat. READ_ONLY FORWARD_ONLY-markören kan endast rullas från första raden till den sista. Om STATIC-nyckelordet saknas är markören dynamisk och använder en dynamisk plan om tillgänglig. Men det finns fall där en dynamisk plan är sämre än en statisk.

Vad händer när vi avmarkerar –LOCAL FAST_FORWARD?

FAST_FORWARD motsvarar markören READ_ONLY och FORWARD_ONLY men har förmågan att välja en bättre plan från antingen en statisk eller en dynamisk.

LOKAL FAST_FORWARD verkar vara det bästa valet på grund av dess flexibilitet att välj mellan en statisk eller dynamisk plan, men FORWARD_ONLY gör också jobbet mycket bra. Även om vi fick resultatet på i genomsnitt 12 sekunder med båda dessa metoder, bör dessa argument testas ordentligt innan vi väljer en av dem.

Det finns många sätt att få samma resultat, mycket snabbare och med mindre påverkan på prestanda.

Marköralternativ

En metod är att använda en JOIN, och som vi kan se nästa är resultaten betydligt bättre.

Först, Vi måste möjliggöra statistiktid för att mäta SQL Server körtid och göra en INNER JOIN mellan två tabeller. och. på kolumnen ProductID. Efter att ha tryckt på EXECUTE-knappen producerade vi samma resultat i 330 ms, jämfört med 04:47 från markörmetoden, och vi har ett leende i ansiktet.

Vi behöver inte itera igenom varje rad för att få det vi behöver, vi har inga fler slingor, ingen tidsklausul, ingen iter vi jobbar med datauppsättningar istället för att få det vi vill ha snabbare och skriva mindre kod.

En lämplig användning av markören

Nu när vi har sett hur mycket skada en markören kan göra, låt oss se ett exempel där vi kan använda det.

Låt oss anta att vi vill välja storlek och antal rader för endast vissa tabeller från en databas. För att uppnå detta kommer vi att få alla tabellnamn baserat på kriterier från information_schema.tables och med hjälp av en CURSOR kommer vi att slinga igenom vart och ett av det tabellnamnet och utföra den lagrade proceduren sp_spaceused genom att skicka ett tabellnamn åt gången för att få den information vi behöver .

Vi kommer att använda samma AdventureWorks2012-databas och hämta alla tabeller från försäljningsschemat som innehåller namnet ”Försäljning”. För varje tabellnamn som returneras vill vi se all information från information_schema.tables.

Nedan har vi T-SQL-syntaxen och de erhållna resultaten:

Detta är en metod där CURSOR är till hjälp genom att itera igenom vissa data en rad i taget och få det resultat som behövs. I detta specifika fall får markören jobbet gjort utan att det påverkar prestandan och är lätt att använda.

Slutsatser

Där har vi det. Vi visade några exempel med det goda, det dåliga och det fula när vi använder markörer. I de flesta fall kan vi använda JOINS, även medan satser, SSIS paket eller andra alternativa metoder för att få samma resultat snabbare, med mindre inverkan på prestanda och till och med skriva färre syntaxrader.

När vi har att göra med OLTP-miljö och stora datauppsättningar att bearbeta, bör ordet ”CURSOR” inte talas.

I SQL är det bra att tänka på att göra operationer på datamängder, snarare än att tänka på ett programmatiskt sätt med hjälp av iterationer eller loopar, eftersom denna typ av tillvägagångssätt inte rekommenderas eller är avsedd för denna användning. Att försöka använda loopar som FOR eller FOREACH från programmeringsspråk och koppla den logiken till SQL-operationer är ett hinder för att få rätt lösning på våra behov. Vi måste tänka på uppsättningsbaserade operationer snarare än en rad i taget för att få den data vi behöver.

Markörerna kan användas i vissa applikationer för seriella operationer som visas i exemplet ovan, men i allmänhet borde de undvikas eftersom de påverkar prestandan negativt, särskilt när de körs på stora datamängder.

• Författare
• Senaste inlägg

Jag är en DBA på Yazaki Component Technology med 3 års erfarenhet av SQL Server.
Jag har en kandidatexamen i datateknik och har även kunskaper i .NET, C # och Windows Server. Jag utvecklade erfarenhet av prestandajustering och övervakning, T-SQL-utveckling, backup-strategi, administration och konfigurering av databaser.
Jag brinner för IT-saker, videospel, bra filmer och electro-house-musik hjälper mig att arbeta när kontoret är bullriga.
Visa alla inlägg av Sergiu Onet

Senaste inlägg av Sergiu Onet (se alla)

• Använda SQL Server-markörer – Fördelar och nackdelar – 23 mars 2016