SQL Server

Jednoduchý batch generátor a jeho praktické využití

V praxi se často můžeme setkat s nutností provádět některé operace po částech či ve více transakcích, protože pokud bychom je vykonali jako jedinou operaci, bude vše trvat neúměrně dlouho, případně si provedení operace může vyžádat systémové prostředky, které nemáme. Nejčastěji se s tímto scénářem setkáme v případě mazání řádků z velkých tabulek, kdy se při vykonání jediného DELETE příkazu musíme vyrovnat v následujícími omezeními:

  • operace je časově náročná, protože mazání každého jednotlivého řádku se zapisuje do transakčního logu
  • nárůst transakčního logu může být opravdu enormní a přesáhnout jak velikost původní tabulky tak i dostupné místo na úložišti
  • po celou dobu provádění transakce jsou nad tabulkou zámky (nad celou tabulkou, případně oddílem tabulky (partition) nebo konkrétními řádky)
  • celkový výkon systému je snížen, jsou-li intenzivně využívání systémové prostředky, zejména IO

Stojíme-li před podobným úkolem, jedním z doporučených postupů je mazat data z tabulky po menších částech pomocí jednoduchého batch generátoru. V článku si ukážeme jednu z možných implementací takového generátoru i její praktické využití.

Základem generátoru je využití rekurze, kterou umožňuje Common Table Expression (CTE):

BatchGenerator_Result

Nejprve jsme vytvořili dvě proměnné:

  • @BatchSize je velikost jednotlivé dávky, tedy kolik řádků např. budeme později chtít odmazat v jednom kole
  • @BatchCount je počet dávek

V našem příkladě definujeme 8 dávek po 50-ti členech, celkem tedy 400 jednotlivých členů.

Rekurzivní CTE funguje tak, že nejprve nadefinujeme tzv. kotvu:

a k ní postupně pomocí UNION ALL přidáváme řádky, které získáváme rekurzivním voláním již samotné tabulky BatchList:

Důležité je nezapomenout připojit k dotazu, který nám vrací data z CTE BatchList, tabulkový hint MAXREXURSION 0, specifikující, kolikrát SQL Server provede rekurzivní operaci, než dojde k jejímu přerušení. MAXRECURSION může nabývat hodnot 0 až 32767, kde 0 znamená neomezený počet cyklů. Pokud nastavení MAXRECURSION neprovedeme, použije se výchozí hodnota 100 a po tomto počty cyklů SQL Server transakci přeruší s chybou a provede její rollback.

Praktická aplikace: Vymazání velké tabulky

Nyní si ukážeme, jak můžeme náš batch generátor využít pro smazání dat z velké tabulky po částech a vyhnout se tím možným problém zmíněným výše. Nejprve si vytvoříme testovací tabulku dbo.SampleData, kterou naplníme 100369 řádky. K naplnění využijeme CROSS JOIN mezi systémovými tabulkami spt_values master databáze:

Kontrolní COUNT(*) na v posledním příkazu nám musí vrátit očekávaný počet řádků.

Nyní rozšíříme batch generátor tak, aby si sám zjistil z tabulky dbo.SampleData, kolik řádků obsahuje, připravil dávky pro mazání o velikosti 30.000 řádků. V následujícím WHILE cyklu provedeme iteraci připravených dávek v tabulce @Batches a samotné mazání.

BatchGenerator_DeleteMsg

Ze stavových zpráv vidíme, že došlo ke smazání všech řádků z tabulek. Generátor správně vytvořil i čtvrtou dávku, která pokryje zbývající řádky po smazání předchozích 90 tisíc řádků.

V našem ideálním testu jsme prováděli vyhledání řádků ke smazání pomocí podmínky, která předpokládá, že sloupec ID v tabulce dbo.SampleData obsahuje unikátní hodnoty číslované od 1 a nejvyšší hodnota je shodná s počtem řádků v tabulce:

Podobný scénář není vždy možný a mnohem jednodušší může být odmazávat data z tabulky vždy po dávce, kdy řádky ke smazání nebudeme vybírat  pomocí selektivní podmínky, ale využijeme klauzuli TOP(počet_řádků), která vždy smaže daný počet_řádků. Pokud nebudeme provádět explitní řazení řádků ke smazání pomocí ORDER BY, můžeme dosáhnout výrazně větší rychlosti a snížit využití systémových prostředků, protože SQL Server nebude nucen vykonávat SORT operaci v exekučním plánu, která patří k těm nejnákladnějším.

Nyní zjednodušíme celý batch generátor takto:

BatchGenerator_RowsToDelete

Generátor vygeneroval opět 4 dávky, ale místo rozmezí od – do určité hodnoty máme nyní ve sloupci RowsToDelete počet řádků, které se mají z tabulky smazat s využitím TOP() klauzule. Naše podmínka, určující data ke smazání v jednotlivé dávce, bude nově vypadat takto:

A celý skript včetně WHILE cyklu takto:

BatchGenerator_RowsDelete

Všechny řádky z tabulky byly podle očekávání smazány.

První způsob generování dávek se hodí spíše pro speciální případy, kdy potřebujeme mazat data podle určitého rozsahu hodnot, např. objednávky podle data. Druhý způsob je univerzální a ideální pro smazání všech dat v tabulce.

Leave a Reply

Your email address will not be published. Required fields are marked *