Co jest lepsze w przypadku dużych zmian w tabeli: USUŃ i WSTAW za każdym razem, czy istnieje AKTUALIZACJA?

Robię projekt, w którym muszę codziennie zmieniać około 36 000 rekordów w jednej tabeli. Zastanawiam się, co będzie działać lepiej:

1. usuń wiersze i wstaw nowe, lub
2. zaktualizuj już istniejące wiersze

Dla mnie łatwiej jest po prostu usunąć wszystkie wiersze i wstawić nowe, ale jeśli to spowoduje fragmentację tabeli i indeksów oraz poprawę wydajności, wolę robić aktualizacje tam, gdzie to możliwe, i usuwać / wstawiać tylko w razie potrzeby.

To będzie usługa nocna i nie zamierzam poprawiać szybkości samego procesu. Jestem bardziej zaniepokojony wydajnością zapytań w stosunku do tej tabeli, w której mam już 89 milionów rekordów i tym, jak wpłynie na to ten nocny proces.

Czy powinienem usunąć / wstawić rekordy, czy też powinienem zaktualizować istniejące (jeśli to możliwe) dla tego nocnego procesu?

To zależy od tego, ile danych się zmienia. Powiedzmy, że ta tabela ma 20 kolumn. I masz również 5 indeksów - każdy na różnicę. kolumna.

Teraz, jeśli wartości we wszystkich 20 kolumnach się zmieniają LUB nawet jeśli dane w 5 kolumnach się zmieniają, a wszystkie te 5 kolumn są indeksowane, być może lepiej będzie „usunąć i wstawić”. Ale jeśli zmieniają się tylko 2 kolumny i powiedzmy, że nie są one częścią indeksów nieklastrowych, być może lepiej będzie „Aktualizować” rekordy, ponieważ w tym przypadku aktualizowany będzie tylko indeks klastrowany (i indeksy nie będą musiały być na bieżąco).

Podczas dalszych badań odkryłem, że powyższy komentarz jest w pewnym sensie zbędny, ponieważ SQL Server wewnętrznie ma 2 oddzielne mechanizmy do przeprowadzania UPDATE. - „Aktualizacja na miejscu” (tj. Poprzez zmianę wartości kolumn na nową w oryginalnym wierszu) lub jako „nieaktualna aktualizacja” (USUŃ, a następnie WSTAW).

Aktualizacje na miejscu są regułą i są wykonywane, jeśli to możliwe. Tutaj wiersze pozostają dokładnie w tym samym miejscu na tej samej stronie w tym samym zakresie. Tylko bajty, których to dotyczy, są dzielone. Tlog ma tylko jeden rekord (pod warunkiem, że nie ma żadnych wyzwalaczy aktualizacji). Aktualizacje odbywają się na miejscu, jeśli sterty są aktualizowane (a na stronie jest wystarczająca ilość miejsca). Aktualizacje odbywają się również w przypadku zmiany klucza klastrowania, ale wiersz wcale nie musi się przenosić.

Na przykład: jeśli masz klastrowany indeks nazwiska i masz nazwy: Able, Baker, Charlie Teraz chcesz zaktualizować Baker do Beckera. Nie trzeba przenosić rzędów. To może zająć miejsce. Natomiast jeśli musisz zaktualizować Able do Kumar, wiersze będą musiały zostać przesunięte (nawet jeśli będą na tej samej stronie). W takim przypadku program SQL Server wykona operację DELETE, a następnie INSERT.

Biorąc pod uwagę powyższe, sugeruję, abyś wykonał normalną AKTUALIZACJĘ i pozwolił SQL Serverowi znaleźć najlepszy sposób, jak to zrobić wewnętrznie.

Aby uzyskać więcej informacji na temat elementów wewnętrznych „UPDATE” lub, jeśli chodzi o wszelkie elementy wewnętrzne związane z SQL Server, sprawdź książkę Kalen Delaney, Paula Randala i innych - SQL Server 2008 Internals .

Czy sprawdziłeś polecenie MERGE w SQL 2008? Oto podstawowy przykład:

  merge YourBigTable ybt
  using (select distinct (RecordID) from YourOtherTable) yot
     on yot.Recordid = YBT.RecordID
  when NOT matched by target
  then  insert (RecordID)
        values (yot.DeviceID) ;

Jest to w zasadzie polecenie „UPSERT”. Zaktualizuj, jeśli istnieje, wstaw go, jeśli nie istnieje. BARDZO szybkie, bardzo fajne polecenie.

Ale sam sprawdziłem Usuń i Wstaw vs Aktualizacja na stole, który ma 30 milionów (3crore) rekordów. Ta tabela ma jeden klastrowy unikalny klucz złożony i 3 klucze nieklastrowane. W przypadku Usuń i Wstaw zajęło 9 minut. Aktualizacja zajęła 55 minut. W każdym wierszu zaktualizowano tylko jedną kolumnę.

Proszę was, abyście nie zgadli. Równania zmienią się w przypadku dużej tabeli z wieloma kolumnami i dużą ilością danych.

Aktualizacja nie jest tak szybka. Sztuczka polega na tym, aby uzyskać szybkie wstawianie, aby wyłączyć indeksy podczas wstawiania danych.

Rozważ użycie tego:

-- disable indexes
ALTER INDEX [index_name] ON dbo.import_table DISABLE
-- ... disable more indexes

-- don't use delete if you don't care about minimal logging. truncate is faster
TRUNCATE TABLE dbo.import_table

-- just insert the new rows
INSERT dbo.import_table
SELECT
    *
FROM
    dbo.source_table

-- rebuild indexes
ALTER INDEX [index_name] ON dbo.import_table REBUILD
-- ... rebuild more indexes

Jeszcze szybciej jest również wyłączyć automatyczną aktualizację statystyk w opcjach db. Jeśli tabela ulegnie znaczącej zmianie, powinieneś uruchomić:

UPDATE STATISTICS dbo.import_table

lub

EXEC sp_updatestats

jako zadanie regularnie (codziennie, co tydzień w zależności od wielkości bazy danych) w celu zapewnienia aktualności statystyk. Należy zwrócić uwagę na aktualizację statystyk, gdy tabela jest pusta. Spieprzy to statystyki, jeśli nie uruchomisz go po ponownym zapełnieniu tabeli.