Úvod

V relačních databázích se operace provádějí na sadě řádků. Například příkaz SELECT vrací sadu řádků, která se nazývá sada výsledků. Logika aplikace někdy potřebuje pracovat s řádkem najednou, nikoli s celou sadou výsledků najednou. V T-SQL je jedním ze způsobů, jak toho dosáhnout, použití KURZORU.

Pokud máte programátorské dovednosti, pravděpodobně byste k iteraci po jedné položce použili smyčku jako FOR nebo WHILE, udělejte něco s data a práce je hotová. V T-SQL je CURSOR podobný přístup a může být upřednostňován, protože se řídí stejnou logikou. Mějte však na paměti, vydejte se touto cestou a problémy mohou následovat.

Existují některé případy, kdy používání CURSORU nezpůsobí tolik nepořádku, ale obecně by se jim mělo zabránit. Níže ukážeme několik příkladů, kde použití KURZORU vytváří problémy s výkonem a uvidíme, že stejnou práci lze provést mnoha jinými způsoby.

Pro účely této ukázky použijeme databázi AdventureWorks2012 a řekněme, že chceme získat nějaká data. tabulka, pro každý produkt, jehož výroba vyžaduje méně než jeden den, tedy z tabulky.

Příklad kurzoru

Začněme pomocí KURZORU a napíšeme následující syntaxi:

Po krátké přestávce na kávu , dotaz byl dokončen a vrátil 833 řádků v čase uvedeném níže. Je důležité zmínit výše vybrané syntaxe, které slouží pouze k ukázkovým účelům, a neprovedl jsem žádné vyladění indexu, abych věci urychlil.

Při provádění kurzoru máme dva kroky. Krok 1, umístění, když kurzor nastaví svou pozici na řádek ze sady výsledků. Krok dva, načítání, když získá data z daného konkrétního řádku v operaci zvané FETCH.

V našem příkladu kurzor nastaví svou pozici na první řádek vrácený prvním příkazem SELECT a načte hodnotu ProductID, která odpovídá podmínce WHERE v proměnné @id. Pak druhý SELECT použije k získání dat proměnnou hodnotu. a načte se další řádek. Tyto operace se opakují, dokud nebudou k dispozici žádné další řádky.

Nakonec syntaxe CLOSE uvolní aktuální sadu výsledků a odstraní zámky z řádků použitých kurzorem a DEALLOCATE odebere odkaz na kurzor.

Naše ukázkové tabulky jsou relativně malé a obsahují zhruba 1 000 a 500 řádků. Pokud bychom museli procházet tabulkami s miliony řádků, trvalo by to značně dlouho a výsledky by nás nepotěšily.

Pojďme dát našemu kurzoru další šanci a odkomentovat řádek – LOCAL STATIC. Existuje mnoho argumentů, které můžeme použít v definici kurzoru, více na tomto odkazu CURSOR Argumenty, ale prozatím se zaměřme na to, co tato dvě slova znamenají.

Když zadáme LOCAL klíčové slovo, rozsah kurzoru je lokální pro dávku, ve které byl vytvořen, a je platný pouze v tomto rozsahu. Po dokončení spuštění dávky se kurzor automaticky uvolní. Na kurzor lze také odkazovat pomocí uložené procedury, spouštěče nebo proměnné lokálního kurzoru v dávce.

Klíčové slovo STATIC vytvoří dočasnou kopii dat použitých kurzorem v tempdb v dočasné tabulce . Tady máme pár gotchas. STATICKÝ kurzor je pouze pro čtení a je také označován jako kurzor snímku, protože pracuje pouze s daty od okamžiku, kdy byl otevřen, což znamená, že nebude zobrazovat žádné změny provedené v databázi na sadě dat používaných kurzor.V zásadě nebudou v sadě výsledků kurzorů viditelné žádné aktualizace, mazání ani vložení provedené po otevření kurzoru, pokud kurzor nezavřeme a znovu neotevřeme.

Před použitím těchto argumentů si toho uvědomte a zkontrolujte, zda to odpovídá vašim potřebám. Ale podívejme se, jestli je náš kurzor rychlejší. Níže máme výsledky:

12 sekund je mnohem lepší než 4 minuty a 47 sekund, ale mějte na paměti omezení vysvětlená výše.

Pokud spustíme syntaxi pomocí tohoto argumentu LOCAL READ_ONLY FORWARD_ONLY, získáme stejné výsledky. Kurzor READ_ONLY FORWARD_ONLY lze posouvat pouze z prvního řádku do posledního. Pokud klíčové slovo STATIC chybí, je kurzor dynamický a použije dynamický plán, pokud je k dispozici. Existují však případy, kdy je dynamický plán horší než statický.

Co se stane, když odkomentujeme –LOKÁLNÍ RYCHLE_FORWARD?

FAST_FORWARD je ekvivalentní kurzoru READ_ONLY a FORWARD_ONLY, ale má schopnost vybrat si lepší plán ze statického nebo dynamického.

LOKÁLNÍ FAST_FORWARD se zdá být nejlepší volbou kvůli své flexibilitě vyberte si mezi statickým nebo dynamickým plánem, ale FORWARD_ONLY také dělá svou práci velmi dobře. Přestože jsme oběma metodami dostali výsledek v průměru za 12 sekund, měly by být tyto argumenty před výběrem jedné z nich správně otestovány.

Existuje mnoho způsobů, jak získat stejný výsledek, mnohem rychleji as menší dopad na výkon.

Alternativa kurzoru

Jedna metoda používá JOIN a jak vidíme dále, výsledky jsou podstatně lepší.

Za prvé, musíme povolit statistický čas pro měření času spuštění serveru SQL a provést VNITŘNÍ PŘIPOJENÍ mezi dvěma tabulkami,. a . ve sloupci ProductID. Po stisknutí tlačítka PROVÉST jsme za 330 ms vytvořili stejné výsledky ve srovnání s časem 04:47 pomocí metody kurzoru a máme úsměv na tváři.

Abychom dostali to, co potřebujeme, nemusíme iterovat v každém řádku, nemáme žádné další smyčky, klauzuli while, žádnou iteraci pracujeme místo toho se sadami dat, rychleji získáváme to, co chceme, a píšeme méně kódu.

Vhodné použití kurzoru

Nyní, když jsme viděli, jak velké poškození kurzor umí, podívejme se na příklad, kde jej můžeme využít.

Předpokládejme, že chceme vybrat velikost a počet řádků pouze pro určité tabulky z databáze. Abychom toho dosáhli, získáme všechny názvy tabulek na základě kritérií z information_schema.tables a pomocí CURSORu projdeme každý z těchto názvů tabulek a provedeme uloženou proceduru sp_spaceused předáním jednoho názvu tabulky najednou, abychom získali informace, které potřebujeme .

Použijeme stejnou databázi AdventureWorks2012 a všechny tabulky získáme ze schématu prodeje, které obsahuje název „Prodej“. Pro každý vrácený název tabulky chceme vidět všechny informace z tabulky information_schema.tables.

Níže máme syntaxi T-SQL a získané výsledky:

Toto je jedna metoda, při které je CURSOR užitečný iterací některých dat po jednom řádku a získává požadovaný výsledek. V tomto konkrétním případě kurzor provede práci bez dopadů na výkon a je snadno použitelný.

Závěry

Tady to máme. Ukázali jsme několik příkladů s dobrými, špatnými a ošklivými při používání kurzorů. Ve většině případů můžeme použít JOINS, i když klauzule WHILE, SSIS balíčky nebo jiné alternativní metody k dosažení stejného výsledku rychleji, s menším dopadem na výkon a dokonce i se zápisem méně řádků syntaxe.

Když jsme mají co do činění s prostředím OLTP a velkými soubory dat ke zpracování, nemělo by se mluvit o slově „CURSOR“.

V SQL je dobrým zvykem myslet na operace se soubory dat, než přemýšlet programově pomocí iterací nebo smyček, protože tento druh přístupu se pro toto použití nedoporučuje ani není určen. Pokus o použití smyček jako FOR nebo FOREACH z programovacích jazyků a spojit tuto logiku s operacemi SQL je překážkou pro získání správného řešení našich potřeb. Abychom získali data, která potřebujeme, musíme myslet spíše na operace založené na sadách než na jednom řádku.

Kurzory lze použít v některých aplikacích pro serializované operace, jak je ukázáno v příkladu výše, ale obecně by měly je třeba se vyhnout, protože mají negativní dopad na výkon, zejména při provozu na velkých souborech dat.

• Autor
• Poslední příspěvky

Jsem DBA ve společnosti Yazaki Component Technology s 3letou praxí na serveru SQL Server.
Mám bakalářský titul v oboru počítačového inženýrství a také dovednosti v .NET, C # a Windows Server. Vyvinul jsem zkušenosti s laděním a sledováním výkonu, vývojem T-SQL, strategií zálohování, správou a konfigurací databází.
Jsem nadšený z IT věcí, videoher, dobrých filmů a elektro-house hudby mi pomáhá pracovat, když je kancelář hlučný.
Zobrazit všechny příspěvky od Sergiu Onet

Poslední příspěvky od Sergiu Oneta (zobrazit všechny)

• Používání kurzorů serveru SQL – Výhody a nevýhody – 23. března 2016