Méthode la plus efficace pour détecter le changement de colonne dans MS SQL Server
notre système fonctionne sur SQL Server 2000, et nous sommes en train de préparer une mise à niveau vers SQL Server 2008. Nous avons beaucoup de code de déclenchement où nous devons détecter un changement dans une colonne donnée et puis opérer sur cette colonne si elle a changé.
évidemment SQL Server fournit le UPDATE () et COLUMNS_UPDATED () fonctions, mais ces fonctions ne vous disent que quelles colonnes ont été impliquées dans la déclaration SQL, qui les colonnes ont réellement changé.
pour déterminer quelles colonnes ont changé, vous avez besoin d'un code similaire à ce qui suit (pour une colonne supportant NULLs):
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>')
Ce code doit être répétée pour chaque colonne que vous souhaitez essayer. Vous pouvez ensuite vérifier la valeur' changed ' pour déterminer si oui ou non effectuer des opérations coûteuses. Bien sûr, ce code est lui-même problématique, car il vous dit seulement qu'au moins une valeur dans la colonne a changé dans tous les lignes qui ont été modifiées.
vous pouvez tester des déclarations de mise à jour individuelles avec quelque chose comme ceci:
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
... mais cela ne fonctionne pas bien lorsque vous avez besoin pour appeler une procédure stockée. Dans ces cas, vous devez vous rabattre sur d'autres approches pour autant que je peux dire.
ma question Est de savoir si quelqu'un a une idée (ou, mieux encore, des données concrètes) de ce qu'est la meilleure / moins chère approche est au problème de la prédiction d'une opération de base de données dans un déclencheur sur si une valeur de colonne particulière dans une rangée modifiée a réellement changé ou non. Aucune des méthodes ci-dessus ne semble idéale, et je me demandais s'il existait une meilleure méthode.
5 réponses
commençons par Je ne ferais jamais et je veux dire jamais invoquer un proc stocké dans un déclencheur. Pour tenir compte d'un insert à plusieurs rangées, vous devez faire un curseur à travers le proc. Cela signifie que les 200.000 lignes que vous venez de charger par le biais d'une requête basée sur un ensemble (disons upddating all prices by 10%) pourraient bien verrouiller la table pendant des heures alors que le déclencheur tente vaillamment de gérer la charge. De plus, si quelque chose change dans le proc, vous pourriez casser n'importe quel inserts à la table du tout ou même complètement raccrocher la table. Je suis une entreprise le code de déclenchement ne doit rien appeler en dehors de la gâchette.
personnellement je préfère simplement faire ma tâche. Si j'ai écrit les actions que je veux faire correctement dans le déclencheur il ne mettra à jour, supprimera ou insérera où les colonnes ont changé.
exemple: supposons que vous voulez mettre à jour le champ last_name que vous stockez à deux endroits en raison d'une dénormalisation placée là pour des raisons de performance.
update t
set lname = i.lname
from table2 t
join inserted i on t.fkfield = i.pkfield
where t.lname <>i.lname
comme vous pouvez le voir, il ne ferait que mettre à jour les noms qui sont différents de ce qui est actuellement dans le tableau que je mets à jour.
si vous voulez faire l'audit et enregistrer seulement ces lignes qui ont changé alors faire la comparaison en utilisant tous les champs quelque chose comme où je.field1 < >D. field1 ou I. field2 < > D. field3 (etc à travers tous les champs)
je pense que vous pourriez vouloir enquêter en utilisant L'opérateur EXCEPT. C'est un ensemble en fonction de l'opérateur qui peuvent éliminer les lignes qui n'ont pas changé. Ce qui est bien, c'est que les valeurs nulles sont aussi égales que les lignes dans le premier ensemble listées avant l'opérateur de Sauf et pas dans le second listées après le
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 */
ceci gère la question des colonnes qui permettent des Nulls sans l'utilisation de ISNULL() dans le déclenchement et retourne uniquement les id des lignes avec des modifications de col1 pour une belle série d'une approche fondée sur la détection des changements. Je n'ai pas testé l'approche, mais ça vaut peut-être le coup. Je pense que excepté a été introduit avec SQL Server 2005.
bien que HLGEM ait donné quelques bons conseils ci-dessus, ce n'était pas exactement ce dont j'avais besoin. J'ai fait pas mal de tests ces derniers jours, et j'ai pensé que je pourrais au moins partager les résultats ici étant donné qu'il semble qu'aucune autre information ne sera disponible.
j'ai mis en place une table qui était effectivement un sous-ensemble plus étroit (9 colonnes) d'une des tables primaires de notre système, et l'ai peuplée avec des données de production de sorte qu'elle était aussi profonde que notre version de production de la table.
j'ai alors dupliqué cette table, et sur la première a écrit un déclencheur qui a tenté de détecter chaque changement de colonne individuelle, et puis a prédit chaque mise à jour de colonne sur le fait que les données dans cette colonne avaient effectivement changé ou non.
pour la deuxième table, j'ai écrit un trigger qui utilisait une logique de cas conditionnel extensive pour faire toutes les mises à jour de toutes les colonnes d'une même instruction.
j'ai ensuite fait 4 tests:
- Une seule colonne mise à jour d'une seule ligne
- Une seule colonne de mise à jour de 10000 lignes
- neuf-mise à jour de colonne à une seule ligne
- neuf-mise à jour de colonne de 10000 lignes
j'ai répété ce test pour les versions indexées et non indexées des tables, puis j'ai répété tout le test sur les serveurs SQL 2000 et SQL 2008.
Les résultats que j'ai été assez intéressant:
la seconde méthode (une seule déclaration de mise à jour avec CAS chevelu logique dans la clause SET) était uniformément plus performant que la détection de changement individuel (dans une mesure plus ou moins grande selon le test) à l'exception d'un changement de colonne unique affectant de nombreuses lignes où la colonne était indexée, en cours D'exécution sur SQL 2000. Dans notre cas particulier, nous ne faisons pas beaucoup de mises à jour étroites et profondes comme celle-ci, donc pour mes besoins l'approche à une seule déclaration est certainement la voie à suivre.
je serais intéressé par d'autres les résultats de tests similaires, pour voir si mes conclusions sont aussi universelles que je le pense ou si elles sont spécifiques à notre configuration particulière.
pour commencer, voici le script de test que j'ai utilisé -- vous aurez évidemment besoin de trouver d'autres données pour le remplir avec:
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
je recommande D'utiliser L'opérateur EXCEPT set comme mentionné par Todd / arghtype ci-dessus.
j'ai ajouté cette réponse parce que j'ai mis le "inséré" avant le "supprimé" de sorte que les INSERTs seront détectés ainsi que les mises à jour. Donc je peux habituellement avoir un déclencheur pour couvrir les inserts et les mises à jour. Peut également détecter les suppressions en ajoutant ou (N'existe pas (sélectionner * de inséré) et existe (sélectionner * De supprimé))
Il détermine si une valeur a changé seulement dans les colonnes spécifiées. Je n'ai pas étudié ses performances par rapport aux autres solutions mais il fonctionne bien dans ma base de données.
il utilise L'opérateur EXCEPT set pour retourner toutes les lignes de la requête de gauche qui ne se trouvent pas sur la requête de droite. Ce code peut être utilisé dans INSERT, UPDATE et DELETE triggers.
la colonne "PKID" est la clé primaire. Il est nécessaire pour permettre l'appariement entre les deux ensembles. Si vous avez plusieurs colonnes pour la clé primaire alors vous devrez inclure toutes les colonnes pour faire la correspondance correcte entre les ensembles insérés et supprimés.
-- 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
si vous voulez utiliser les lignes modifiées dans la logique de déclenchement subséquente, je mets habituellement les résultats de la requête EXCEPT dans une variable de table qui peut être référencée plus tard.
j'espère que c'est de l'intérêt :-)
Il existe une autre technique dans SQL Server 2008 pour le suivi des modifications:
comparaison de la saisie des données sur les changements et du suivi des changements