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):

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)

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:

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

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:

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

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:

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

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.