kurzory mohou vypadat jako zkratky pro vývojáře. Když máte komplexní práci vykonávat a budete muset manipulovat s řádky v tabulce, nejrychlejší způsob, jak se může zdát iterovat řádky jeden po druhém pomocí Transact-SQL kurzor. Koneckonců, protože musíte iterovat prostřednictvím datových struktur ve svém vlastním kódu na straně klienta, můžete být v pokušení udělat totéž, když máte co do činění s daty serveru SQL., Ale iterace prostřednictvím dat pomocí kurzorů Transact-SQL se často nemění dobře a doufám, že vás přesvědčím, že to také není dobrý design nebo architektonická praxe.
Kurzor Zkušenosti
chci, aby to, protože před pár měsíci, jsem se musel vypořádat s prodejcem je Transact-SQL skript, který upgradovat jejich databázové součásti na novou verzi dodavatele aplikace. Navrhli skript, aby otočili velmi velkou tabulku a uložili příslušná data do nové tabulky vodorovně, jako zřetězené řetězce., Prodejce chtěl zlepšit výkon tím, že stůl menší, a tak se rozhodli pro ukládání podrobných dat vodorovně, jako oddělený čárkami řetězec pro každý rodič id. Klientská aplikace by mohla dotazovat výsledné čárkami vymezené řetězce rychleji, než dostat každý z nich jako jednotlivé řádky, a v kontextu, změna dávala smysl a zlepšila výkon aplikace.
Nicméně, prodávající je Transact-SQL skript pro kontingenční data během aktualizace trvalo 16 hodin běžet na zkušební stroj, a zákazník si nemohl dovolit víc než pár hodin prostojů pro upgrade., Když jsme zkoumali dodavatele skript, viděli jsme, že developer měl kódované otočná proces ve dvou krocích: kurzor iterovat přes všechny nadřazené tabulky ids vybudovat prázdné pre-formátované tabulky, a pak další scénář zřetězení řetězců, opět pomocí kurzoru.
pomocí nastaveného přístupu jsme dokázali zkrátit dobu zpracování z 16-plus hodin na méně než pět minut. Sledovali jsme vývojka původní strategie, budování prázdné tabulky pomocí příkazů SELECT, a snížili jsme čas na to krok za méně než dvě minuty., Poté jsme zřetězili řetězce pomocí příkazu k aktualizaci, provedeného na nadřazené id. Naše iterace přes rodičovské ids používá chvíli smyčku, a skončil za méně než tři minuty.
nevyhnutelnost iterace
mnoho přístupů k databázovým datům musí být nějakým způsobem iterativní, aby se připravila data pro další manipulaci. Dokonce i SQL Server engine iteruje prostřednictvím dat, když skenuje nebo připojí data pomocí různých typů spojení, které má k dispozici. Můžete to vidět, když prozkoumáte plán dotazů serveru SQL Server pro dotaz, který vrací mnoho řádků z velké datové sady., Pro spojit, budete nejčastěji vidět vnořené smyčky, ale někdy také sloučení nebo hash připojit. Pro jednodušší dotazy, můžete vidět seskupené nebo non-clustered index skenování. Je to pouze v případech, kdy SQL Server může vrátit jeden řádek nebo malou sadu řádků, a tabulka má odpovídající index, který uvidíte hledat pomocí indexu.
Přemýšlejte o tom: Microsoft má optimalizované a vyladěné SQL Server motor pro let k iterovat přes jeho dostupných údajů tak efektivně, jak je to možné., Představte si, že kdybyste měli čas a byli ochotni utratit energii, pravděpodobně byste mohli psát nízkoúrovňové přístupy k databázovým datovým souborům, které by byly docela efektivní. Nicméně, to by bylo efektivní pouze pro individuální úkol před vámi, a musíte ladit a možná bude muset kompletně přepsat, pokud rozsah vašich dat, přístup měl změnit. Pravděpodobně by vám trvalo roky, než byste skutečně získali kód plně optimalizovaný a zobecněný, a dokonce byste nebyli blízko účinnosti kódu uvnitř úložiště serveru SQL Server.,
takže kde je zisk v re-vynalézat kolo? Je to jen proto, že SQL Server motor je tak dobře optimalizovaný a odladěný, že je lepší nechat to udělat iterace pro vás a využít rozsáhlý vývoj a testování, který je již vložený v databázi.
Pokud se podíváte na úkoly zpracování dat podrobněji, myslím, že zjistíte, že existuje opravdu jen velmi málo příležitostí, kdy jsou vyžadovány kurzory. Za prvé, často můžete dosáhnout svého cíle tím, že se spoléháte na nastavené příkazy SQL v Transact-SQL a ignorujete pořadí řádků tabulky., Za druhé, kurzory Transact-SQL jsou jen jedním ze způsobů, jak iterovat řádek po řádku tabulky. Pokud můžete jednoznačně identifikovat každý řádek tabulky, kterou musíte opakovat, můžete použít spíše smyčku než kurzor a potenciálně získat lepší výkon. Provedu vás příkladem, abych vám ukázal proč.
porovnání iteračních strategií
Předpokládejme, že můžete jednoznačně identifikovat každý řádek tabulky, protože tabulka má jedinečný klíč nebo jedinečnou skupinu sloupců., Za chvíli smyčky, vše, co musíte udělat, je najít nejnižší hodnotu jedinečného stavu, a pak najít další nejvyšší hodnotu pokaždé, když iteraci. Zde je příklad z produkce ukázkových databází SQL Server 2005 AdventureWorks.Transakcehistorický stůl. Má seskupený index na primárním klíči, A zatímco smyčka může hledat do řádku pokaždé.,
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)ENDTady je to stejné smyčky pomocí kurzor FAST FORWARD, což je nejvíce efektivní typ Transact-SQL kurzor jen pro čtení dat:
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_CursorNa mém notebooku, poté, co projel jsem to několikrát, aby se ujistěte se, že všechna data jsou v cache, ZATÍMCO smyčka trvá devět sekund a kurzor trvá 17 sekund. Vaše vlastní trvání se může lišit. Všimněte si, že i když příklad opravdu nedělá nic s daty, zatímco smyčka je rychlejší. Kurzor zjevně přidává více nad hlavou.,
kurzor také vyžaduje další příkazy, díky nimž kód vypadá přeplněný. Aniž by se do detailů, jak kurzory práce, které Microsoft vysvětluje, plně v Microsoft SQL Server 2005 Books Online, všimněte si, že při použití WHILE, není tam žádný požadavek na deklarovat, otevřít, zavřít, a navrátit nic. Logika je jednodušší a můžete dokonce volně aktualizovat řádky po cestě. Chcete-li aktualizovat řádky pomocí kurzoru, budete muset změnit typ kurzoru.
i když smyčka přidává režii iterace., Můžete být schopni jej nahradit příkazem SELECT set-based, nebo nahradit všechny aktualizace, které chcete udělat ve smyčce s příkazem aktualizace set-based, a nechat iteraci do SQL Server engine. Jednoduchý příkaz SELECT získat stejné údaje, jako náš kurzor a WHILE výše trvá méně než 3 sekundy, a to se vrací řádky pro klienta, který je více práce, než dvě předchozí smyčky.
SELECT *FROM Production.TransactionHistoryVYBERTE spoléhá na SQL Server iterovat přes data, a je zdaleka nejrychlejší ze tří metod přístupu k datům jsme se podíval na.,
od pytlů po sady
někdy se může zdát, že kurzory jsou nezbytné. Když jednoduše musíte iterovat prostřednictvím databázových dat, řádek po řádku, v jejich fyzickém pořadí, někdy bude fungovat pouze kurzor. To se nejčastěji stává, když máte duplicitní řádky a neexistuje žádný způsob, jak jednoznačně identifikovat daný řádek v tabulce. Tyto tabulky jsou tašky, ne sady, dat, jako ‚bag‘ nevylučuje duplicitní hodnoty, jako sada dělá.
takové sáčky dat se obvykle vyskytují při importu dat z externího zdroje a nemůžete datům zcela důvěřovat., Pokud například naše tabulka historie transakcí AdventureWorks neměla skupinu sloupců, které byste mohli nazvat jedinečnými, a/nebo měli duplicitní řádky, můžete si myslet, že musíte použít kurzor.
vždy však můžete sáček řádků proměnit v normalizovanou tabulku. I když máte duplicitní řádky v tabulce, nebo ne sadu sloupců, můžete se spolehnout na jedinečnosti, můžete přidat sloupec identity tabulka a semínko identity začít číslování 1. Tím se do tabulky přidá jedinečný klíč, který vám umožní použít smyčku WHILE namísto kurzoru., Jakmile budete mít jedinečný klíč,můžete odstranit duplikáty pomocí příkazu Transact-SQL set-based UPDATE.
logické API pro databázová Data
pomocí operací set-base je lepší než iteraci dat sami alespoň dvěma způsoby.
nejprve jsou příkazy SQL založené na Nastavení efektivnější, protože k provedení iterace používáte vysoce optimalizovaný motor SQL Serveru. Pokud iterujete prostřednictvím dat sami, nepoužíváte optimálně SQL Server storage engine. Místo toho jej pepřujete příkazy k načtení pouze jednoho řádku najednou., Pokaždé, když požadujete jeden řádek, musí váš příkaz projít optimalizátorem serveru SQL, než se dostane do úložiště, a nakonec nepoužíváte optimalizovaný kód SQL Server storage engine. Pokud jste se iterovali, spoléháte se při zpracování dat také na cizí fyzické informace o tabulce, a to pořadí řádků. Příkazy set-base Transact-SQL SELECT, UPDATE a DELETE vám dávají způsob, jak ignorovat pořadí řádků a ovlivnit je pouze na základě charakteristik dat-a jsou rychlejší.,
za druhé, příkazy založené na sadě jsou logičtější, protože přemýšlení o datech v sadách vás abstrahuje od cizích detailů, které se více zabývají tím, jak jsou data skutečně uspořádána. Ve skutečnosti, příkazy založené na nastavení, jako je SELECT, UPDATE a DELETE, když jsou aplikovány na tabulky přímo a ne v kurzoru nebo ve smyčce, vás logicky přiblíží k vašim datům, právě proto, že můžete ignorovat pořadí dat.,
Tady je další způsob, jak přemýšlet o tento druhý bod-stejně jako uložené procedury jsou nejvíce přírodní API pro aplikace rozhraní s SQL Server programově, takže nastavit na bázi SQL příkazy jsou vhodné API pro přístup k relační data. Uložené procedury oddělují vaši aplikaci od vnitřních databází a jsou efektivnější než ad hoc dotazy. Podobně, příkazy set-base SQL uvnitř Transact-SQL vám logické rozhraní pro vaše relační data, a jsou efektivnější, protože se spoléháte na SQL Server storage engine pro iteraci prostřednictvím dat.,
pointa není, že iterace dat je špatná. Ve skutečnosti je to často nevyhnutelné. Spíše jde o to, ať to pro vás udělá úložný stroj a místo toho se spoléhá na logické rozhraní příkazů Transact-SQL založených na sadě. Myslím, že najdete jen málo, pokud existují situace, kdy musíte skutečně použít kurzor Transact-SQL.