AKTUALIZUJ wydajność, gdy dane się nie zmieniają

Jeśli mam UPDATEinstrukcję, która tak naprawdę nie zmienia żadnych danych (ponieważ dane są już w stanie zaktualizowanym). Czy dodanie WHEREklauzuli w celu zapobieżenia aktualizacji ma jakąkolwiek korzyść w zakresie wydajności ?

Na przykład, czy istnieje jakakolwiek różnica w szybkości wykonywania między UPDATE 1 i UPDATE 2 w następujących przypadkach:

CREATE TABLE MyTable (ID int PRIMARY KEY, Value int);
INSERT INTO MyTable (ID, Value)
VALUES
    (1, 1),
    (2, 2),
    (3, 3);

-- UPDATE 1
UPDATE MyTable
SET
    Value = 2
WHERE
    ID = 2
    AND Value <> 2;
SELECT @@ROWCOUNT;

-- UPDATE 2
UPDATE MyTable
SET
    Value = 2
WHERE
    ID = 2;
SELECT @@ROWCOUNT;

DROP TABLE MyTable;

Pytam dlatego, że potrzebuję liczby wierszy, aby uwzględnić niezmieniony wiersz, więc wiem, czy zrobić wstawkę, jeśli identyfikator nie istnieje. Jako taki użyłem formularza UPDATE 2. Jeśli korzystanie z formularza UPDATE 1 przynosi korzyść w zakresie wydajności, czy jest możliwe uzyskanie potrzebnej liczby wierszy?

Jeśli mam instrukcję UPDATE, która tak naprawdę nie zmienia żadnych danych (ponieważ dane są już w stanie zaktualizowanym), czy jest jakaś korzyść z wydajności poprzez umieszczenie w klauzuli where klauzuli where, aby zapobiec aktualizacji?

Z pewnością może istnieć niewielka różnica w wydajności spowodowana aktualizacją 1 :

• nie aktualizuje żadnych wierszy (stąd nic do zapisania na dysku, nawet minimalna aktywność dziennika), i
• wyjmowanie mniej restrykcyjnych blokad niż to, co jest wymagane do przeprowadzenia aktualnej aktualizacji (stąd lepiej dla współbieżności) ( patrz sekcja Aktualizacja pod koniec )

Jaką różnicę należy jednak zmierzyć w systemie za pomocą schematu, danych i obciążenia systemu. Istnieje kilka czynników, które wpływają na to, jak duży wpływ ma nieaktualizowana AKTUALIZACJA:

• ilość rywalizacji na stole jest aktualizowana
• liczba aktualizowanych wierszy
• jeśli w tabeli są aktualizowane wyzwalacze UPDATE (jak zauważył Mark w komentarzu do pytania). Jeśli wykonasz polecenie UPDATE TableName SET Field1 = Field1, wyzwalacz aktualizacji zostanie uruchomiony i zasygnalizuje, że pole zostało zaktualizowane (jeśli sprawdzisz za pomocą funkcji UPDATE () lub COLUMNS_UPDATED ) i że pole zarówno w tabeli, jak INSERTEDi w DELETEDtabeli ma tę samą wartość.

Ponadto następujący rozdział podsumowujący znajduje się w artykule Paula White'a, Wpływ aktualizacji niezwiązanych z aktualizacją (jak zauważył @spaghettidba w komentarzu do swojej odpowiedzi):

SQL Server zawiera szereg optymalizacji, aby uniknąć niepotrzebnego rejestrowania lub opróżniania strony podczas przetwarzania operacji UPDATE, która nie spowoduje żadnych zmian w trwałej bazie danych.

• Brak aktualizacji aktualizacji tabeli klastrowej zazwyczaj pozwala uniknąć dodatkowego rejestrowania i opróżniania strony, chyba że operacja aktualizacji wpłynie na kolumnę tworzącą (część) klucza klastra.
• Jeśli jakakolwiek część klucza klastra zostanie „zaktualizowana” do tej samej wartości, operacja jest rejestrowana tak, jakby dane uległy zmianie, a dotknięte strony są oznaczone jako brudne w puli buforów. Jest to konsekwencją konwersji UPDATE na operację usuwania, a następnie wstawiania.
• Tabele stert zachowują się tak samo jak tabele klastrowane, z tym wyjątkiem, że nie mają klucza klastra, który powodowałby dodatkowe rejestrowanie lub opróżnianie strony. Dzieje się tak nawet wtedy, gdy na stercie istnieje nieklastrowany klucz podstawowy. W związku z tym brak aktualizacji aktualizacji sterty zazwyczaj pozwala uniknąć dodatkowego rejestrowania i opróżniania (ale patrz poniżej).
• Zarówno stosy, jak i tabele klastrowe będą podlegać dodatkowemu rejestrowaniu i czyszczeniu dla każdego wiersza, w którym kolumna LOB zawierająca więcej niż 8000 bajtów danych jest aktualizowana do tej samej wartości przy użyciu dowolnej składni innej niż „SET nazwa_kolumny = nazwa_kolumny”.
• Po prostu włączenie dowolnego poziomu izolacji wersji wiersza w bazie danych zawsze powoduje dodatkowe rejestrowanie i opróżnianie. Dzieje się tak niezależnie od poziomu izolacji obowiązującego dla transakcji aktualizacji.

Proszę pamiętać (zwłaszcza jeśli nie klikniesz linku, aby zobaczyć pełny artykuł Paula), następujące dwa elementy:

1. Aktualizacje, które nie aktualizują się, nadal mają pewne aktywności w dzienniku, co wskazuje, że transakcja zaczyna się i kończy. Tyle tylko, że nie następuje modyfikacja danych (co nadal stanowi dobrą oszczędność).

2. Jak wspomniałem powyżej, musisz przetestować swój system. Skorzystaj z tych samych zapytań badawczych, z których korzysta Paul i sprawdź, czy uzyskasz te same wyniki. Widzę nieco inne wyniki w moim systemie niż te pokazane w artykule. Nadal nie ma brudnych stron do napisania, ale trochę więcej aktywności w logach.

... Potrzebuję liczby wierszy, aby uwzględnić niezmieniony wiersz, więc wiem, czy zrobić wstawkę, jeśli identyfikator nie istnieje. ... czy jest możliwe uzyskanie potrzebnej liczby wierszy?

Upraszczając, jeśli masz do czynienia tylko z jednym rzędem, możesz wykonać następujące czynności:

UPDATE MyTable
SET    Value = 2
WHERE  ID = 2
AND Value <> 2;

IF (@@ROWCOUNT = 0)
BEGIN
  IF (NOT EXISTS(
                 SELECT *
                 FROM   MyTable
                 WHERE  ID = 2 -- or Value = 2 depending on the scenario
                )
     )
  BEGIN
     INSERT INTO MyTable (ID, Value) -- or leave out ID if it is an IDENTITY
     VALUES (2, 2);
  END;
END;

W przypadku wielu wierszy można uzyskać informacje potrzebne do podjęcia tej decyzji przy użyciu OUTPUTklauzuli. Przechwytując dokładnie, które wiersze zostały zaktualizowane, możesz zawęzić elementy, aby wyszukać różnicę między nie aktualizowaniem wierszy, które nie istnieją, a nie aktualizowaniem wierszy, które istnieją, ale nie wymagają aktualizacji.

Podstawową implementację pokazuję w następującej odpowiedzi:

Jak uniknąć używania zapytania scalającego podczas wstawiania wielu danych za pomocą parametru xml?

Metoda pokazana w tej odpowiedzi nie odfiltrowuje istniejących wierszy, które nie wymagają aktualizacji. Tę część można dodać, ale najpierw musisz dokładnie pokazać, gdzie otrzymujesz zestaw danych, z którym się łączysz MyTable. Czy pochodzą z tymczasowego stołu? Parametr wyceniony w tabeli (TVP)?

AKTUALIZACJA 1:

W końcu mogłem przeprowadzić testy i oto, co znalazłem w odniesieniu do dziennika transakcji i blokowania. Najpierw schemat tabeli:

CREATE TABLE [dbo].[Test]
(
  [ID] [int] NOT NULL CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED,
  [StringField] [varchar](500) NULL
);

Następnie test aktualizuje pole do wartości, która już ma:

UPDATE rt
SET    rt.StringField = '04CF508B-B78E-4264-B9EE-E87DC4AD237A'
FROM   dbo.Test rt
WHERE  rt.ID = 4082117

Wyniki:

-- Transaction Log (2 entries):
Operation
----------------------------
LOP_BEGIN_XACT
LOP_COMMIT_XACT


-- SQL Profiler (3 Lock:Acquired events):
Mode            Type
--------------------------------------
8 - IX          5 - OBJECT
8 - IX          6 - PAGE
5 - X           7 - KEY

Na koniec test, który odfiltrowuje aktualizację z powodu niezmienionej wartości:

UPDATE rt
SET    rt.StringField = '04CF508B-B78E-4264-B9EE-E87DC4AD237A'
FROM   dbo.Test rt
WHERE  rt.ID = 4082117
AND    rt.StringField <> '04CF508B-B78E-4264-B9EE-E87DC4AD237A';

Wyniki:

-- Transaction Log (0 entries):
Operation
----------------------------


-- SQL Profiler (3 Lock:Acquired events):
Mode            Type
--------------------------------------
8 - IX          5 - OBJECT
7 - IU          6 - PAGE
4 - U           7 - KEY

Jak widać, podczas odfiltrowywania wiersza nic nie jest zapisywane w Dzienniku transakcji, w przeciwieństwie do dwóch pozycji oznaczających początek i koniec transakcji. I chociaż prawdą jest, że te dwa wpisy są prawie niczym, wciąż są czymś.

Również blokowanie zasobów PAGE i KEY jest mniej restrykcyjne podczas odfiltrowywania wierszy, które nie uległy zmianie. Jeśli żadne inne procesy nie wchodzą w interakcje z tą tabelą, to prawdopodobnie nie jest to problem (ale jak prawdopodobne jest to naprawdę?). Należy pamiętać, że testy pokazane na każdym z blogów, do których prowadzą linki (a nawet moje testy), domyślnie zakładają, że nie ma sprzeczności na stole, ponieważ nigdy nie jest to część testów. Mówiąc, że aktualizacje nie są tak lekkie, że filtrowanie się nie opłaca, należy przeprowadzić z odrobiną soli, ponieważ testy przeprowadzono mniej więcej w próżni. Ale w produkcji ta tabela najprawdopodobniej nie jest izolowana. Oczywiście równie dobrze może być tak, że odrobina rejestrowania i bardziej restrykcyjne blokady nie przekładają się na mniejszą wydajność. Więc najbardziej wiarygodne źródło informacji, aby odpowiedzieć na to pytanie? SQL Server. Konkretnie:twój SQL Server. Pokaże Ci, która metoda jest lepsza dla twojego systemu :-).

AKTUALIZACJA 2:

Jeśli operacje, w których nowa wartość jest taka sama jak bieżąca wartość (tj. Bez aktualizacji), liczą operacje, w których nowa wartość jest inna i aktualizacja jest konieczna, to następujący wzór może okazać się jeszcze lepszy, szczególnie jeśli na stole jest wiele sporów. Chodzi o to, aby SELECTnajpierw zrobić prosty, aby uzyskać bieżącą wartość. Jeśli nie otrzymasz wartości, masz odpowiedź dotyczącą INSERT. Jeśli masz wartość, możesz zrobić proste IFi wydać UPDATE tylko jeśli jest to potrzebne.

DECLARE @CurrentValue VARCHAR(500) = NULL,
        @NewValue VARCHAR(500) = '04CF508B-B78E-4264-B9EE-E87DC4AD237A',
        @ID INT = 4082117;

SELECT @CurrentValue = rt.StringField
FROM   dbo.Test rt
WHERE  rt.ID = @ID;

IF (@CurrentValue IS NULL) -- if NULL is valid, use @@ROWCOUNT = 0
BEGIN
  -- row does not exist
  INSERT INTO dbo.Test (ID, StringField)
  VALUES (@ID, @NewValue);
END;
ELSE
BEGIN
  -- row exists, so check value to see if it is different
  IF (@CurrentValue <> @NewValue)
  BEGIN
    -- value is different, so do the update
    UPDATE rt
    SET    rt.StringField = @NewValue
    FROM   dbo.Test rt
    WHERE  rt.ID = @ID;
  END;
END;

Wyniki:

-- Transaction Log (0 entries):
Operation
----------------------------


-- SQL Profiler (2 Lock:Acquired events):
Mode            Type
--------------------------------------
6 - IS          5 - OBJECT
6 - IS          6 - PAGE

Tak więc uzyskano tylko 2 blokady zamiast 3, i obie te blokady są zamierzone współużytkowane, a nie Intent eXclusive lub Intent Update ( zgodność zamków ). Pamiętając, że każda nabyta blokada zostanie również zwolniona, każda blokada to tak naprawdę 2 operacje, więc ta nowa metoda to w sumie 4 operacje zamiast 6 operacji w pierwotnie zaproponowanej metodzie. Biorąc pod uwagę, że ta operacja jest wykonywana raz na 15 ms (w przybliżeniu, jak podano w OP), czyli około 66 razy na sekundę. Oryginalna propozycja wynosi więc 396 operacji blokowania / odblokowywania na sekundę, podczas gdy ta nowa metoda zapewnia jedynie 264 operacji blokowania / odblokowywania na sekundę nawet lżejszych blokad. Nie jest to gwarancja niesamowitej wydajności, ale z pewnością warte przetestowania :-).

Pomniejsz trochę i pomyśl o większym obrazie. Czy w rzeczywistości rzeczywiste oświadczenie o aktualizacji będzie wyglądać tak:

UPDATE MyTable
  SET Value = 2
WHERE
     ID = 2
     AND Value <> 2;

Czy może będzie to wyglądać bardziej tak:

UPDATE Customers
  SET AddressLine1 = '123 Main St',
      AddressLine2 = 'Apt 24',
      City = 'Chicago',
      State = 'IL',
      (and a couple dozen more fields)
WHERE
     ID = 2
     AND (AddressLine1 <> '123 Main St'
     OR AddressLine2 <> 'Apt 24'
     OR City <> 'Chicago'
     OR State <> 'IL'
      (and a couple dozen more fields))

Ponieważ w prawdziwym świecie tabele mają wiele kolumn. Oznacza to, że będziesz musiał wygenerować dużo złożonej dynamicznej logiki aplikacji, aby zbudować ciągi dynamiczne, LUB będziesz musiał za każdym razem określać zawartość każdego pola przed i po.

Jeśli budujesz te instrukcje aktualizacji dynamicznie dla każdej tabeli, tylko przekazując pola, które są aktualizowane, możesz szybko natknąć się na problem zanieczyszczenia pamięci podręcznej planu podobny do problemu wielkości parametrów NHibernate sprzed kilku lat. Co gorsza, jeśli zbudujesz instrukcje aktualizacji w SQL Server (tak jak w procedurach przechowywanych), to spalisz cenne cykle procesora, ponieważ SQL Server nie jest strasznie wydajny w łączeniu łańcuchów razem na dużą skalę.

Ze względu na tę złożoność zwykle nie ma sensu przeprowadzać tego rodzaju porównania rząd po rzędzie podczas aktualizacji. Zamiast tego pomyśl o operacjach opartych na zbiorze.

Można zauważyć wzrost wydajności podczas pomijania wierszy, które nie muszą być aktualizowane tylko wtedy, gdy liczba wierszy jest duża (mniej rejestrowania, mniej brudnych stron do zapisu na dysku).

W przypadku aktualizacji w jednym wierszu, jak w twoim przypadku, różnica w wydajności jest całkowicie znikoma. Jeśli aktualizacja wierszy we wszystkich przypadkach ułatwia ci to, zrób to.

Aby uzyskać więcej informacji na ten temat, zobacz Nieprzeprowadzanie aktualizacji przez Paula White'a

Możesz połączyć aktualizację i wstawić w jedną instrukcję. W SQL Server można użyć instrukcji MERGE , aby wykonać aktualizację i wstawić, jeśli nie zostanie znaleziona. W przypadku MySQL można użyć INSERT ON DUPLICATE KEY UPDATE .

Zamiast sprawdzać wartości wszystkich pól, czy nie możesz uzyskać wartości skrótu za pomocą interesujących kolumn, a następnie porównać ją z wartością skrótu zapisaną względem wiersza w tabeli?

IF EXISTS (Select 1 from Table where ID =@ID AND HashValue=Sha256(column1+column2))
GOTO EXIT
ELSE