Od: Daniel Farina | Aktualizováno: 2019-09-12 | Komentáře (2) | Související: Více > T-SQL

Máte vždy slyšel, že byste se měli vyhnout kurzory v T-SQL kódu jako SQL Server osvědčených postupů, becausecursors jsou škodlivé pro performanceand někdy způsobit problémy. Ale někdy je potřeba procházet data jeden řádek, takže v tomto tipu se podíváme na srovnání toho, jak doa smyčky bez použití kurzoru.,

Řešení

všichni víme, že SQL Server, jako každý relační databáze umožňuje uživateli, proveďte nastavení na základě operací. Také, jak mnoho dodavatelů databází, SQL Server zahrnujeprocesní rozšíření, které je jazykem T-SQL. Přidává konstrukty nalezené vprocedurální jazyky umožňující jednodušší kódování pro vývojáře. Tyto konstrukce byly přidány z nějakého důvodu a někdy je to jediný přístup kúkol po ruce.,

Použití While Místo Kurzory v SQL Serveru

Pokud jste někdy pracoval withcursors, můžete najít tento název trochu confusingbecause koneckonců, kurzory použití, zatímco konstrukce pro iteraci mezi řádky. Ale kromě toho vám chci ukázat, že za určitých okolností, když použijeme kurzor k iteraci sady řádků, můžeme jej změnit na smyčku. V takových případech je jedinou výzvoubude zvolit správnou podmínku výstupu.,

Klady a Zápory Pomocí Kurzorů Iterovat Řádky Tabulky v serveru SQL Server

Ne všechno, co je špatně s kurzory, mají také některé advantagesover další opakování techniky.

  • kurzory jsou aktualizovatelné: když vytvoříte kurzor, použijete Dotaz k definování pomocí instrukcí deklarovat kurzor. Pomocí možnosti aktualizace v kurzorukreační příkaz, můžete aktualizovat sloupce v kurzoru.,
  • kurzorem se můžete pohybovat vpřed a vzad: pomocí možnosti POSOUVÁNÍV příkazu prohlásit kurzor můžete procházet přes kurzorové záznamy inboth směry s možnostmi načtení první, poslední, předchozí, další, relativní aabsolute. Mějte na paměti, že možnost posouvání je nekompatibilní s možnostmi FORWARD_ONLYand FAST_FORWARD.
  • kurzory mohou být předány uloženým postupům: pokud používáte globální volbuvytvořit kurzor, může být použit v jakémkoli uloženém postupu nebo šarži provedenéve stejném připojení. To vám umožní používat kurzory na vnořených uložených postupech.,
  • kurzory mají mnoho různých možností: u kurzorů máte možnost použít různé možnosti, které ovlivňují, jak se budou chovat, pokud jde o zamykání.
  • kurzory nepotřebují podmínku: pomocí kurzorů, jste handlingsada řádků jako záznam. To vám umožní pohybovat se přes kurzor bezpotřeba mít booleovský stav. Například, můžete vytvořit cursorwith název databází umístěných na serveru SQL Server instance bez nutnost náhradní klíč k práci jako test stavu jako na WHILE.,

existují také některé negativní aspekty,které byste si měli být vědomi při použití kurzorůmísto jiných možností opakování.

  • Pokud používáte globální kurzory v kódu užíváte riziko facingerrors kvůli kurzor je uzavřen tím, že některé vnořené uložené procedury v yourcode.
  • kurzory mají obvykle menší výkon než ekvivalentní smyčka pomocí Whiloop nebo CTE.

klady a zápory použití While Loop k iteraci přes řádky tabulky v SQL Serveru

existují také výhody použití WHILE loop ve srovnání s kurzorem.,

  • zatímco smyčky jsou rychlejší než kurzory.
  • zatímco smyčky používají méně zámků než kurzory.
  • menší využití Tempdb: zatímco smyčky nevytvářejí kopii dat intempdb jako kurzor. Nezapomeňte, že kurzory, v závislosti na možnostech, které vymůžete je vytvořit, mohou způsobit vytvoření tabulek temp.

další seznam podrobně popisuje negativní aspekty WHILE loops.

  • pohyb vpřed nebo vzad je složitý: pro pohyb vpřed nebo vzad v aloopu je nutné dynamicky měnit stav iterace uvnitř smyčky.,To vyžaduje zvláštní péči; jinak můžete skončit v nekonečné smyčce.
  • riziko nekonečné smyčky: ve Srovnání s kurzorem, nemáš pevnou sadu dat do smyčky (tj. data se vrátil do VYBERTE statementin kurzor prohlášení), namísto při použití WHILE budete muset definovat boundarywith výraz, který je vyhodnocován na hodnotu true nebo false.

Stavební Testovací Prostředí pro Kurzory a Smyčky

tento test budu používat tabulky s sloupec identity (CursorTestID),varchar sloupec (Výplň) a bigint sloupec (RunningTotal).,

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

myšlenka je, aby smyčky koryto řádky tabulky nařídil CursorTestID columnand aktualizovat RunningTotal sloupec se součtem CursorTestID sloupec valueand hodnota RunningTotal sloupci z předchozí řady.

ale před spuštěním musíme nejprve vygenerovat některé testovací řádky s dalším skriptem.,

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

Na scénáři výše, můžete si všimnout, že jsem použil jen jediný-li vložit statementand jsem využil batch separator (JÍT 500000 velení) jako shortcutto spuštění tohoto příkazu insert 500000 krát. Můžete si přečíst více o této metoděopakovat dávkové provedení na tomto tipu: provádění dávky T-SQL vícekrát pomocí GO.

příklad základního kurzoru pro smyčku řádků tabulky v SQL Serveru

vytvoříme kurzor pro vyplnění sloupce RunningTotal. Všimněte si na nextscript, že jsem deklaroval kurzor s možností FAST_FORWARD., To se provádí nezbytného pro zvýšení výkonu kurzor protože podle Microsoft theFAST_FORWARD argument „Určuje, FORWARD_ONLY, READ_ONLY kurzor s performanceoptimizations povoleno“. Jinými slovy, učíme SQL Server k použití pouze pro čtení kurzor, který se může pohybovat pouze dopředu a být rolovat od první do poslední řady.

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

další obrázek je snímání obrazovky zobrazující provádění skriptu výše.Jak můžete vidět, trvalo to tři minuty a pět sekund na aktualizaci 500 000 rowsof náš testovací stůl.,

Příklad Základního While Cyklu přes Řádky Tabulky v serveru SQL Server

Teď jsem se přepsat předchozí skript, aby se zabránilo použití kurzoru. Nebudete vědět, že obsahuje smyčku While, která je téměř totožná s tou ve skriptu thecursor. To je, jak jsem již řekl, protože i při práci s kurzorymusíte použít iterativní řídicí strukturu.

dalším obrázkem je snímání obrazovky provedení výše uvedeného skriptu. Trvalo bez času spustit while smyčku než kurzor.,

Další SQL Server Kurzor Příklad

vezměme si například kurzor v tipStandardize SQL Server data s textem, vyhledávání a nahrazování funkce. Slovo ofadvice, Chcete-li spustit tento kód, měli byste postupovat podle pokynů v tipu, abyste vytvořili testovací prostředí.

a zde je Kód kurzoru:

Pokud tento kód rozebereme, můžeme vidět, že existuje jeden kurzor, který procházíprodukty tabulky, které jsem zkopíroval níže.,

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

SQL Server Kurzor Příklad Převést do While

Ve snaze nahradit tento kurzor s, ZATÍMCO SMYČKY, musíme vytvořit temporarytable realizovat tally tabulky. Pro všechny z vás, kteří nevědí, co je to talltable, můžeme jej definovat jako tabulku, která obsahuje pár sloupců, které se skládají z klíče a jeho hodnoty. V našem konkrétním případě použijeme sekvenční integerkey začínající od 1, takže jej můžeme použít jako iterátor. Tento klíč bude spojendo produktu z tabulky produktů.,

Na první, protože Výrobky stůl má ProductID klíč definován jako identityyou může být v pokušení přeskočit tento krok, ale musíte vzít v úvahu, že v reálném casea řádek může byly odstraněny, proto nebudete moci používat identitycolumn jako iterátor. Navíc řádek může být smazán, když běžíme ourcode a to by mohlo vést k chybám při provádění. Abychom se tomu vyhnuli, přidáme aTRY-CATCHblock. Půjdu do toho dál.

před spuštěním WHILE loop musíme nastavit jeho počáteční a zastavovací stav., Pro tuto záležitost jsem přidal dvě nové celočíselné proměnné s názvem @ Iterator a @MaxIterator.Proměnná @ MaxIterator slouží k udržení počtu položek v #TallyTabletable a její hodnotu nastavíme pouze jednou před spuštěním smyčky. Variable @ Iteratorje inicializován na 1, protože jsme jej definovali jako počáteční číslo na sekvenci a budeme zvyšovat jeho hodnotu při každé iteraci.

další kroky
  • jste novým kurzorem a potřebujete nějakou praxi? V nexttip najdete vysvětlení, snadno pochopitelný příklad kurzoru a dalšídoporučená čtení: příklad kurzoru SQL Serveru.,
  • Pokud chcete převést stávající kurzory v kódu nastavit na základě dotazů,podívejte se na tento chapterSQL Server Převést Cursorto Nastavit na Serveru theSQL Návrh Databáze Nejlepších Praktik Návod.
  • potřebujete další příklad použití While loop? Podívejte se na tento tipthat vám ukáže, jak rozdělit příkazy DML v dávkách:Optimalizovat Velké SQL ServerInsert, Aktualizovat a Odstranit Procesy, Pomocí Šarží.
  • v případě, že nevíte, jak používat zkuste…Chytit exceptionhandling, podívejte se na tento tip:SQL Server zkuste a chytit zpracování výjimek.,
  • zůstaňte naladěni na server T-SQL Tipscategory, abyste získali více nápadů na kódování.

Poslední aktualizace: 2019-09-12

O autorovi
Daniel Farina se narodil v Buenos Aires, Argentina. Sebevzdělaný, od dětství projevoval vášeň pro učení.
Zobrazit všechny moje tipy
související zdroje

  • další tipy pro vývojáře databáze…

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *