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):
DECLARE @BatchSize INT DECLARE @BatchCount INT SET @BatchSize = 50 SET @BatchCount = 8 ;WITH Batchlist AS ( SELECT 1 BatchId, 1 BatchStart, @BatchSize BatchEnd UNION ALL SELECT BatchId + 1, @BatchSize + BatchStart, @BatchSize + BatchEnd FROM BatchList WHERE BatchId <= @BatchCount - 1 ) SELECT * FROM BatchList OPTION (MAXRECURSION 0)
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:
SELECT 1 BatchId, 1 BatchStart, @BatchSize BatchEnd
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:
SELECT BatchId + 1, @BatchSize + BatchStart, @BatchSize + BatchEnd FROM BatchList WHERE BatchId <= @BatchCount - 1
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:
CREATE TABLE dbo.SampleData
( ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Value VARCHAR(100)
)
GO
INSERT INTO dbo.SampleData
(Value)
SELECT TOP (100399)
s1.name + '_' + s2.name
FROM master..spt_values s1
CROSS JOIN master..spt_values s2
GO
SELECT COUNT(*) FROM dbo.SampleData
GO
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í.
SET NOCOUNT ON
DECLARE @BatchSize INT
DECLARE @BatchCount INT
DECLARE @BatchId INT
DECLARE @BatchStart INT
DECLARE @BatchEnd INT
DECLARE @Batches TABLE (BatchId INT NOT NULL PRIMARY KEY,
BatchStart INT NOT NULL,
BatchEnd INT NOT NULL)
SET @BatchSize = 30000
SET @BatchCount = (SELECT COUNT(*) / @BatchSize + CASE WHEN COUNT(*) % @BatchSize > 0 THEN 1 ELSE 0 END FROM dbo.SampleData)
;WITH Batchlist AS
(
SELECT 1 BatchId, 1 BatchStart, @BatchSize BatchEnd
UNION ALL
SELECT BatchId + 1, @BatchSize + BatchStart, @BatchSize + BatchEnd
FROM BatchList
WHERE BatchId <= @BatchCount-1
)
INSERT INTO @Batches
(BatchId, BatchStart, BatchEnd)
SELECT
BatchId, BatchStart, BatchEnd
FROM BatchList
OPTION (MAXRECURSION 0)
WHILE EXISTS (SELECT * FROM @Batches)
BEGIN
SELECT TOP(1)
@BatchId = BatchId,
@BatchStart = BatchStart,
@BatchEnd = BatchEnd
FROM @Batches
ORDER BY BatchId
PRINT CAST(@BatchId AS VARCHAR(10)) + ' => ' + CAST(@BatchStart AS VARCHAR(10)) + ' - ' + CAST(@BatchEnd AS VARCHAR(10))
DELETE FROM dbo.SampleData WHERE ID BETWEEN @BatchStart AND @BatchEnd
PRINT CAST(@@ROWCOUNT AS VARCHAR(10))
DELETE FROM @Batches WHERE BatchId = @BatchId
END
GO
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:
DELETE FROM dbo.SampleData WHERE ID BETWEEN @BatchStart AND @BatchEnd
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:
DECLARE @BatchSize INT
DECLARE @BatchId INT
DECLARE @TotalRows INT
DECLARE @RowsToDelete INT
DECLARE @Batches TABLE (BatchId INT NOT NULL PRIMARY KEY,
RowsToDelete INT NOT NULL)
SET @BatchSize = 30000
SET @RowsToDelete = (SELECT COUNT(*) FROM dbo.SampleData)
;WITH Batchlist AS
(
SELECT 1 BatchId
UNION ALL
SELECT BatchId + 1
FROM BatchList
WHERE BatchId < ( @RowsToDelete / @BatchSize + CASE WHEN @RowsToDelete % @BatchSize > 0 THEN 1 ELSE 0 END )
)
INSERT INTO @Batches
(BatchId, RowsToDelete)
SELECT
BatchId, CASE
WHEN (BatchId * @BatchSize) <= @RowsToDelete THEN @BatchSize
ELSE @RowsToDelete % @BatchSize
END
FROM BatchList
OPTION (MAXRECURSION 0)
SELECT * FROM @Batches
GO
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:
DELETE TOP(@RowsToDelete) FROM dbo.SampleData
A celý skript včetně WHILE cyklu takto:
DECLARE @BatchSize INT
DECLARE @BatchId INT
DECLARE @TotalRows INT
DECLARE @RowsToDelete INT
DECLARE @Batches TABLE (BatchId INT NOT NULL PRIMARY KEY,
RowsToDelete INT NOT NULL)
SET @BatchSize = 30000
SET @RowsToDelete = (SELECT COUNT(*) FROM dbo.SampleData)
;WITH Batchlist AS
(
SELECT 1 BatchId
UNION ALL
SELECT BatchId + 1
FROM BatchList
WHERE BatchId < ( @RowsToDelete / @BatchSize + CASE WHEN @RowsToDelete % @BatchSize > 0 THEN 1 ELSE 0 END )
)
INSERT INTO @Batches
(BatchId, RowsToDelete)
SELECT
BatchId, CASE
WHEN (BatchId * @BatchSize) <= @RowsToDelete THEN @BatchSize
ELSE @RowsToDelete % @BatchSize
END
FROM BatchList
OPTION (MAXRECURSION 0)
WHILE EXISTS (SELECT * FROM @Batches)
BEGIN
SELECT TOP(1)
@BatchId = BatchId,
@RowsToDelete = RowsToDelete
FROM @Batches
ORDER BY BatchId
PRINT CAST(@BatchId AS VARCHAR(10))
DELETE TOP(@RowsToDelete) FROM dbo.SampleData
PRINT CAST(@@ROWCOUNT AS VARCHAR(10))
DELETE FROM @Batches WHERE BatchId = @BatchId
END
GO
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.