Najskuteczniejsza metoda wykrywania zmiany kolumn w MS SQL Server
Nasz system działa na SQL Server 2000, a my jesteśmy w trakcie przygotowań do aktualizacji do SQL Server 2008. Mamy dużo kodu wyzwalającego, w którym musimy wykryć zmianę w danej kolumnie, a następnie operować na tej kolumnie, jeśli się zmieniła.
Oczywiście SQL Server dostarcza funkcje UPDATE () i COLUMNS_UPDATED () , ale te funkcje mówią tylko, które kolumny zostały zamieszane w instrukcję SQL, nie które kolumny mają rzeczywiście zmieniony.
Aby określić, które kolumny się zmieniły, potrzebujesz kodu podobnego do poniższego (dla kolumny obsługującej null):
IF UPDATE(Col1)
SELECT @col1_changed = COUNT(*)
FROM Inserted i
INNER JOIN Deleted d ON i.Table_ID = d.Table_ID
WHERE ISNULL(i.Col1, '<unique null value>')
!= ISNULL(i.Col1, '<unique null value>')
Ten kod musi być powtarzany dla każdej kolumny, którą chcesz przetestować. Następnie można sprawdzić wartość "zmieniona", aby określić, czy należy wykonywać kosztowne operacje. Oczywiście ten kod sam w sobie jest problematyczny, ponieważ mówi tylko, że co najmniej jedna wartość w kolumnie zmieniła się we wszystkich wierszach, które były zmodyfikowany.
Możesz przetestować poszczególne Instrukcje aktualizacji za pomocą czegoś takiego:
UPDATE Table SET Col1 = CASE WHEN i.Col1 = d.Col1
THEN Col1
ELSE dbo.fnTransform(Col1) END
FROM Inserted i
INNER JOIN Deleted d ON i.Table_ID = d.Table_ID
... ale to nie działa dobrze, gdy musisz wywołać procedurę składowaną. W tych przypadkach trzeba wycofać się na inne podejścia, o ile mogę powiedzieć.
Moje pytanie brzmi, czy ktoś ma wgląd (lub, jeszcze lepiej, twarde dane), jakie jest najlepsze / najtańsze podejście do problemu przewidywania operacji bazy danych w wyzwalaczu, czy dana wartość kolumny w zmodyfikowany wiersz rzeczywiście się zmienił lub nie. Żadna z powyższych metod nie wydaje się idealna i zastanawiałem się, czy istnieje lepsza metoda.
5 answers
Zacznijmy od tego, że nigdy nie wywołałbym zapisanego proc w wyzwalaczu. Aby uwzględnić wkładkę wielowierszową, trzeba by kursor przez proc. Oznacza to, że 200 000 wierszy, które właśnie załadowałeś, chociaż zapytanie oparte na zestawie (powiedzmy upddating wszystkich cen o 10%) może również zablokować tabelę na wiele godzin, ponieważ WYZWALACZ próbuje dzielnie obsłużyć obciążenie. Dodatkowo, jeśli coś się zmieni w proc, możesz w ogóle rozbić wszelkie wkładki do stołu lub nawet całkowicie zawiesić stół. Jestem firmą beliver ten kod wyzwalacza nie powinien wywoływać niczego innego poza wyzwalaczem.
Osobiście wolę po prostu wykonywać swoje zadanie. Jeśli napisałem poprawnie akcje, które chcę wykonać w wyzwalaczu, zaktualizuje się, usunie lub wstawi tylko tam, gdzie kolumny się zmieniły.Przykład: załóżmy, że chcesz zaktualizować pole last_name, które przechowujesz w dwóch miejscach ze względu na denormalizację umieszczoną tam ze względów wydajnościowych.
update t
set lname = i.lname
from table2 t
join inserted i on t.fkfield = i.pkfield
where t.lname <>i.lname
Jak widać zaktualizowałoby tylko nazwy, które są inne niż to, co jest obecnie w tabeli aktualizuję.
Jeśli chcesz wykonać audyt i nagrać tylko te wiersze, które się zmieniły, zrób porównanie używając wszystkich pól gdzie i. field1 d.field1 lub i.field2 d.field3 (itd. przez wszystkie pola)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-03-16 22:46:11
Myślę, że możesz chcieć zbadać używając operatora EXCEPT. Jest to operator oparty na zestawie, który może oddzielić wiersze, które nie uległy zmianie. Fajną rzeczą jest to, że uważa wartości null za równe, ponieważ wygląda dla wierszy w pierwszym zbiorze wymienionym przed operatorem EXCEPT, a nie w drugim wymienionym po EXCEPT
WITH ChangedData AS (
SELECT d.Table_ID , d.Col1 FROM deleted d
EXCEPT
SELECT i.Table_ID , i.Col1 FROM inserted i
)
/*Do Something with the ChangedData */
Rozwiązuje problem kolumn, które zezwalają na null bez użycia ISNULL() w wyzwalaczu i zwraca tylko identyfikatory wierszy ze zmianami na col1 dla ładne podejście oparte na zestawie do wykrywania zmian. Nie testowałem tego podejścia, ale może to być warte twojego czasu. Myślę, że oprócz został wprowadzony z SQL Server 2005.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-09-02 15:43:43
Chociaż HLGEM dał kilka dobrych rad powyżej, nie było to dokładnie to, czego potrzebowałem. Zrobiłem sporo testów w ciągu ostatnich kilku dni, i pomyślałem, że przynajmniej podzielę się wynikami tutaj, biorąc pod uwagę, że wygląda na to, że nie będzie więcej informacji.
Stworzyłem tabelę, która była węższym podzbiorem (9 kolumn) jednej z podstawowych tabel naszego systemu i wypełniłem ją danymi produkcyjnymi, tak aby była tak głęboka, jak nasza wersja produkcyjna tabeli.
I wtedy powielał tę tabelę, a na pierwszej napisał wyzwalacz, który próbował wykryć każdą zmianę kolumny, a następnie oparł każdą aktualizację kolumny na tym, czy dane w tej kolumnie rzeczywiście się zmieniły, czy nie.
Dla drugiej tabeli napisałem wyzwalacz, który używał rozbudowanej warunkowej logiki przypadków, aby wykonać wszystkie aktualizacje wszystkich kolumn w jednej instrukcji.
Potem przeprowadziłem 4 testy:
- Aktualizacja jednej kolumny do jednego wiersza
- Jednokolumnowy aktualizacja do 10000 wierszy
- Aktualizacja dziewięciu kolumn do jednego wiersza
- 9-kolumnowa aktualizacja do 10000 wierszy
Powtórzyłem ten test zarówno dla indeksowanych, jak i nieindeksowanych wersji tabel, a następnie powtórzyłem całość na serwerach SQL 2000 i SQL 2008.
Wyniki, które otrzymałem były dość interesujące:
Druga metoda (jedna pojedyncza Instrukcja aktualizacji z logiką przypadku w klauzuli SET) była jednolicie lepsza od indywidualnej zmiany wykrywanie (w mniejszym lub większym stopniu w zależności od testu) z pojedynczym wyjątkiem zmiany pojedynczej kolumny wpływającej na wiele wierszy, w których kolumna była indeksowana, uruchamianej na SQL 2000. W naszym konkretnym przypadku nie robimy wielu wąskich, głębokich aktualizacji, takich jak ten, więc dla moich celów podejście oparte na jednym oświadczeniu jest zdecydowanie rozwiązaniem.
[1]} chciałbym usłyszeć wyniki innych ludzi podobnych typów badań, aby zobaczyć, czy moje wnioski są tak uniwersalne, jak podejrzewam są lub są specyficzne dla naszej konkretnej konfiguracji.
Aby zacząć, oto skrypt testowy, którego użyłem -- oczywiście musisz wymyślić inne dane, aby go wypełnić:
create table test1
(
t_id int NOT NULL PRIMARY KEY,
i1 int NULL,
i2 int NULL,
i3 int NULL,
v1 varchar(500) NULL,
v2 varchar(500) NULL,
v3 varchar(500) NULL,
d1 datetime NULL,
d2 datetime NULL,
d3 datetime NULL
)
create table test2
(
t_id int NOT NULL PRIMARY KEY,
i1 int NULL,
i2 int NULL,
i3 int NULL,
v1 varchar(500) NULL,
v2 varchar(500) NULL,
v3 varchar(500) NULL,
d1 datetime NULL,
d2 datetime NULL,
d3 datetime NULL
)
-- optional indexing here, test with it on and off...
CREATE INDEX [IX_test1_i1] ON [dbo].[test1] ([i1])
CREATE INDEX [IX_test1_i2] ON [dbo].[test1] ([i2])
CREATE INDEX [IX_test1_i3] ON [dbo].[test1] ([i3])
CREATE INDEX [IX_test1_v1] ON [dbo].[test1] ([v1])
CREATE INDEX [IX_test1_v2] ON [dbo].[test1] ([v2])
CREATE INDEX [IX_test1_v3] ON [dbo].[test1] ([v3])
CREATE INDEX [IX_test1_d1] ON [dbo].[test1] ([d1])
CREATE INDEX [IX_test1_d2] ON [dbo].[test1] ([d2])
CREATE INDEX [IX_test1_d3] ON [dbo].[test1] ([d3])
CREATE INDEX [IX_test2_i1] ON [dbo].[test2] ([i1])
CREATE INDEX [IX_test2_i2] ON [dbo].[test2] ([i2])
CREATE INDEX [IX_test2_i3] ON [dbo].[test2] ([i3])
CREATE INDEX [IX_test2_v1] ON [dbo].[test2] ([v1])
CREATE INDEX [IX_test2_v2] ON [dbo].[test2] ([v2])
CREATE INDEX [IX_test2_v3] ON [dbo].[test2] ([v3])
CREATE INDEX [IX_test2_d1] ON [dbo].[test2] ([d1])
CREATE INDEX [IX_test2_d2] ON [dbo].[test2] ([d2])
CREATE INDEX [IX_test2_d3] ON [dbo].[test2] ([d3])
insert into test1 (t_id, i1, i2, i3, v1, v2, v3, d1, d2, d3)
-- add data population here...
insert into test2 (t_id, i1, i2, i3, v1, v2, v3, d1, d2, d3)
select t_id, i1, i2, i3, v1, v2, v3, d1, d2, d3 from test1
go
create trigger test1_update on test1 for update
as
begin
declare @i1_changed int,
@i2_changed int,
@i3_changed int,
@v1_changed int,
@v2_changed int,
@v3_changed int,
@d1_changed int,
@d2_changed int,
@d3_changed int
IF UPDATE(i1)
SELECT @i1_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
ON i.t_id = d.t_id WHERE ISNULL(i.i1,0) != ISNULL(d.i1,0)
IF UPDATE(i2)
SELECT @i2_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
ON i.t_id = d.t_id WHERE ISNULL(i.i2,0) != ISNULL(d.i2,0)
IF UPDATE(i3)
SELECT @i3_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
ON i.t_id = d.t_id WHERE ISNULL(i.i3,0) != ISNULL(d.i3,0)
IF UPDATE(v1)
SELECT @v1_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
ON i.t_id = d.t_id WHERE ISNULL(i.v1,'') != ISNULL(d.v1,'')
IF UPDATE(v2)
SELECT @v2_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
ON i.t_id = d.t_id WHERE ISNULL(i.v2,'') != ISNULL(d.v2,'')
IF UPDATE(v3)
SELECT @v3_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
ON i.t_id = d.t_id WHERE ISNULL(i.v3,'') != ISNULL(d.v3,'')
IF UPDATE(d1)
SELECT @d1_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
ON i.t_id = d.t_id WHERE ISNULL(i.d1,'1/1/1980') != ISNULL(d.d1,'1/1/1980')
IF UPDATE(d2)
SELECT @d2_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
ON i.t_id = d.t_id WHERE ISNULL(i.d2,'1/1/1980') != ISNULL(d.d2,'1/1/1980')
IF UPDATE(d3)
SELECT @d3_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
ON i.t_id = d.t_id WHERE ISNULL(i.d3,'1/1/1980') != ISNULL(d.d3,'1/1/1980')
if (@i1_changed > 0)
begin
UPDATE test1 SET i1 = CASE WHEN i.i1 > d.i1 THEN i.i1 ELSE d.i1 END
FROM test1
INNER JOIN inserted i ON test1.t_id = i.t_id
INNER JOIN deleted d ON i.t_id = d.t_id
WHERE i.i1 != d.i1
end
if (@i2_changed > 0)
begin
UPDATE test1 SET i2 = CASE WHEN i.i2 > d.i2 THEN POWER(i.i2, 1.1) ELSE POWER(d.i2, 1.1) END
FROM test1
INNER JOIN inserted i ON test1.t_id = i.t_id
INNER JOIN deleted d ON i.t_id = d.t_id
WHERE i.i2 != d.i2
end
if (@i3_changed > 0)
begin
UPDATE test1 SET i3 = i.i3 ^ d.i3
FROM test1
INNER JOIN inserted i ON test1.t_id = i.t_id
INNER JOIN deleted d ON i.t_id = d.t_id
WHERE i.i3 != d.i3
end
if (@v1_changed > 0)
begin
UPDATE test1 SET v1 = i.v1 + 'a'
FROM test1
INNER JOIN inserted i ON test1.t_id = i.t_id
INNER JOIN deleted d ON i.t_id = d.t_id
WHERE i.v1 != d.v1
end
UPDATE test1 SET v2 = LEFT(i.v2, 5) + '|' + RIGHT(d.v2, 5)
FROM test1
INNER JOIN inserted i ON test1.t_id = i.t_id
INNER JOIN deleted d ON i.t_id = d.t_id
if (@v3_changed > 0)
begin
UPDATE test1 SET v3 = LEFT(i.v3, 5) + '|' + LEFT(i.v2, 5) + '|' + LEFT(i.v1, 5)
FROM test1
INNER JOIN inserted i ON test1.t_id = i.t_id
INNER JOIN deleted d ON i.t_id = d.t_id
WHERE i.v3 != d.v3
end
if (@d1_changed > 0)
begin
UPDATE test1 SET d1 = DATEADD(dd, 1, i.d1)
FROM test1
INNER JOIN inserted i ON test1.t_id = i.t_id
INNER JOIN deleted d ON i.t_id = d.t_id
WHERE i.d1 != d.d1
end
if (@d2_changed > 0)
begin
UPDATE test1 SET d2 = DATEADD(dd, DATEDIFF(dd, i.d2, d.d2), d.d2)
FROM test1
INNER JOIN inserted i ON test1.t_id = i.t_id
INNER JOIN deleted d ON i.t_id = d.t_id
WHERE i.d2 != d.d2
end
UPDATE test1 SET d3 = DATEADD(dd, 15, i.d3)
FROM test1
INNER JOIN inserted i ON test1.t_id = i.t_id
INNER JOIN deleted d ON i.t_id = d.t_id
end
go
create trigger test2_update on test2 for update
as
begin
UPDATE test2 SET
i1 =
CASE
WHEN ISNULL(i.i1, 0) != ISNULL(d.i1, 0)
THEN CASE WHEN i.i1 > d.i1 THEN i.i1 ELSE d.i1 END
ELSE test2.i1 END,
i2 =
CASE
WHEN ISNULL(i.i2, 0) != ISNULL(d.i2, 0)
THEN CASE WHEN i.i2 > d.i2 THEN POWER(i.i2, 1.1) ELSE POWER(d.i2, 1.1) END
ELSE test2.i2 END,
i3 =
CASE
WHEN ISNULL(i.i3, 0) != ISNULL(d.i3, 0)
THEN i.i3 ^ d.i3
ELSE test2.i3 END,
v1 =
CASE
WHEN ISNULL(i.v1, '') != ISNULL(d.v1, '')
THEN i.v1 + 'a'
ELSE test2.v1 END,
v2 = LEFT(i.v2, 5) + '|' + RIGHT(d.v2, 5),
v3 =
CASE
WHEN ISNULL(i.v3, '') != ISNULL(d.v3, '')
THEN LEFT(i.v3, 5) + '|' + LEFT(i.v2, 5) + '|' + LEFT(i.v1, 5)
ELSE test2.v3 END,
d1 =
CASE
WHEN ISNULL(i.d1, '1/1/1980') != ISNULL(d.d1, '1/1/1980')
THEN DATEADD(dd, 1, i.d1)
ELSE test2.d1 END,
d2 =
CASE
WHEN ISNULL(i.d2, '1/1/1980') != ISNULL(d.d2, '1/1/1980')
THEN DATEADD(dd, DATEDIFF(dd, i.d2, d.d2), d.d2)
ELSE test2.d2 END,
d3 = DATEADD(dd, 15, i.d3)
FROM test2
INNER JOIN inserted i ON test2.t_id = i.t_id
INNER JOIN deleted d ON test2.t_id = d.t_id
end
go
-----
-- the below code can be used to confirm that the triggers operated identically over both tables after a test
select top 10 test1.i1, test2.i1, test1.i2, test2.i2, test1.i3, test2.i3, test1.v1, test2.v1, test1.v2, test2.v2, test1.v3, test2.v3, test1.d1, test1.d1, test1.d2, test2.d2, test1.d3, test2.d3
from test1 inner join test2 on test1.t_id = test2.t_id
where
test1.i1 != test2.i1 or
test1.i2 != test2.i2 or
test1.i3 != test2.i3 or
test1.v1 != test2.v1 or
test1.v2 != test2.v2 or
test1.v3 != test2.v3 or
test1.d1 != test2.d1 or
test1.d2 != test2.d2 or
test1.d3 != test2.d3
-- test 1 -- one column, one row
update test1 set i3 = 64 where t_id = 1000
go
update test2 set i3 = 64 where t_id = 1000
go
update test1 set i3 = 64 where t_id = 1001
go
update test2 set i3 = 64 where t_id = 1001
go
-- test 2 -- one column, 10000 rows
update test1 set v3 = LEFT(v3, 50) where t_id between 10000 and 20000
go
update test2 set v3 = LEFT(v3, 50) where t_id between 10000 and 20000
go
-- test 3 -- all columns, 1 row, non-self-referential
update test1 set i1 = 1000, i2 = 2000, i3 = 3000, v1 = 'R12345123', v2 = 'Happy!', v3 = 'I am v3!!!', d1 = '1/1/1985', d2 = '1/1/1988', d3 = NULL
where t_id = 3000
go
update test2 set i1 = 1000, i2 = 2000, i3 = 3000, v1 = 'R12345123', v2 = 'Happy!', v3 = 'I am v3!!!', d1 = '1/1/1985', d2 = '1/1/1988', d3 = NULL
where t_id = 3000
go
-- test 4 -- all columns, 10000 rows, non-self-referential
update test1 set i1 = 1000, i2 = 2000, i3 = 3000, v1 = 'R12345123', v2 = 'Happy!', v3 = 'I am v3!!!', d1 = '1/1/1985', d2 = '1/1/1988', d3 = NULL
where t_id between 30000 and 40000
go
update test2 set i1 = 1000, i2 = 2000, i3 = 3000, v1 = 'R12345123', v2 = 'Happy!', v3 = 'I am v3!!!', d1 = '1/1/1985', d2 = '1/1/1988', d3 = NULL
where t_id between 30000 and 40000
go
-----
drop table test1
drop table test2
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-03-27 13:59:42
Zalecam użycie operatora EXCEPT set, jak wspomniano powyżej przez Todda / arghtype.
Dodałem tę odpowiedź, ponieważ umieściłem "wstawiony" przed "usunięty" tak, że Wstawki będą wykrywane, jak również aktualizacje. Więc zwykle mogę mieć jeden WYZWALACZ, aby pokryć zarówno wstawki, jak i aktualizacje. Można również wykryć usuwanie przez dodanie OR(NOT EXISTS(SELECT * FROM inserted) and EXISTS (SELECT * FROM deleted))
Określa, czy wartość zmieniła się tylko w podanych kolumnach. Ja nie zbadałem jego wydajność w porównaniu z innymi rozwiązaniami, ale działa dobrze w mojej bazie danych.
Używa operatora EXCEPT set do zwracania wierszy z lewego zapytania, które nie są również Znalezione w prawym zapytaniu. Kod ten może być używany w wyzwalaczach INSERT, UPDATE I DELETE.
Kolumna "PKID" jest kluczem podstawowym. Wymagane jest, aby umożliwić dopasowanie między dwoma zestawami. Jeśli masz wiele kolumn dla klucza podstawowego, musisz dołączyć wszystkie kolumny do zrobienia poprawne dopasowanie między wstawionymi i usuniętymi zestawami.
-- Only do trigger logic if specific field values change.
IF EXISTS(SELECT PKID
,Column1
,Column7
,Column10
FROM inserted
EXCEPT
SELECT PKID
,Column1
,Column7
,Column10
FROM deleted ) -- Tests for modifications to fields that we are interested in
OR (NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)) -- Have a deletion
BEGIN
-- Put code here that does the work in the trigger
END
Jeśli chcesz użyć zmienionych wierszy w późniejszej logice wyzwalania, Zwykle umieszczam wyniki zapytania EXCEPT w zmiennej table, do której można się odwoływać później.
Mam nadzieję, że to jest interesujące: -)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-11-29 04:22:56
W SQL Server 2008 istnieje inna technika śledzenia zmian:
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-11-30 07:46:55