SQL Server optimalizace aktualizačních dotazů pro velké objemy dat

SQL Server optimalizace aktualizačních dotazů pro velké objemy dat

aktualizace velmi velkých tabulek může být časově náročný úkol a někdy může trvat hodiny. Kromě toho může také způsobit problémy s blokováním.

zde je několik tipů, jak SQL Server optimalizovat aktualizace na velkých objemech dat.

1. odstranění indexu ve sloupci, který má být aktualizován.
2. provádění aktualizace v menších dávkách.
3. zakázání spouštěčů mazání.
4. nahrazení příkazu aktualizace operací hromadného vložení.

s tím bylo řečeno, použijme výše uvedené body pro optimalizaci aktualizačního dotazu.

níže uvedený kód vytvoří fiktivní tabulku s 200 000 řádky a požadovanými indexy.

CREATE TABLE tblverylargetable ( sno INT IDENTITY, col1 CHAR(800), col2 CHAR(800), col3 CHAR(800) ) GO DECLARE @i INT=0 WHILE( @i < 200000 ) BEGIN INSERT INTO tblverylargetable VALUES ('Dummy', Replicate('Dummy', 160), Replicate('Dummy', 160)) SET @i=@i + 1 ENDGOCREATE INDEX ix_col1 ON tblverylargetable(col1) GO CREATE INDEX ix_col2_col3 ON tblverylargetable(col2) INCLUDE(col3)

zvažte následující aktualizační dotaz, který má být optimalizován. Je to velmi přímočarý dotaz na aktualizaci jednoho sloupce.

UPDATE tblverylargetable SET col1 = 'D'WHERE col1 = 'Dummy'

provedení dotazu trvá 2: 19 minut.

podívejme se na plán provedení dotazu zobrazeného níže. Kromě aktualizace seskupeného indexu je aktualizován také index ix_col1. Operace aktualizace indexu a třídění dohromady zabírají 64% nákladů na provedení.

1. Odstranění indexu ve sloupci, který má být aktualizován

stejný dotaz trvá 14-18 sekund, pokud na col1 není žádný index. Aktualizační dotaz tedy běží rychleji, pokud sloupec, který má být aktualizován, není sloupcem indexového klíče. Index lze vždy vytvořit po dokončení aktualizace.

2. Provedení aktualizace v menších dávkách

dotaz lze dále optimalizovat provedením v menších dávkách. To je obecně rychlejší. Níže uvedený kód aktualizuje záznamy v dávkách 20000.

DECLARE @i INT=1 WHILE( @i <= 10 ) BEGIN UPDATE TOP(20000) tblverylargetable SET col1 = 'D' WHERE col1 = 'Dummy' SET @i=@i + 1 END

provedení výše uvedeného dotazu trvá 6-8 sekund. Při aktualizaci v dávkách, i když aktualizace selže nebo je třeba ji zastavit, jsou vráceny pouze řádky z aktuální dávky.

3. Zakázání spouštěčů Delete

spouštěče s kurzory mohou extrémně zpomalit výkon dotazu delete. Zakázání spouštěčů po odstranění výrazně zvýší výkon dotazu.

4. Nahrazení příkazu Update operací Bulk-Insert

příkaz update je plně zaznamenaná operace, a proto bude jistě trvat značné množství času, pokud mají být aktualizovány miliony řádků.Nejrychlejší způsob, jak urychlit aktualizační dotaz, je nahradit jej operací hromadného vložení. Jedná se o minimálně zaznamenanou operaci v jednoduchém a hromadně zaznamenaném modelu obnovy. To lze snadno provést hromadným vložením do nové tabulky a přejmenováním tabulky na původní. Požadované indexy a omezení lze podle potřeby vytvořit v nové tabulce.

níže uvedený kód ukazuje, jak lze aktualizaci převést na operaci hromadného vložení. Provedení trvá 4 sekundy.

SELECT sno, CASE col1 WHEN 'Dummy' THEN 'D' ELSE col1 END AS col1, col2, col3 INTO tblverylargetabletemp FROM tblverylargetable

hromadná vložka může být dále optimalizována, aby se dosáhlo dalšího zvýšení výkonu.

doufám, že to pomůže!!!

Lajkni nás na FaceBook / Sledujte nás na Twitteru / Připojte se k nejrychleji rostoucí skupině SQL Server na FaceBook