Подтвердить что ты не робот

Наиболее эффективный метод обнаружения изменения столбцов в MS SQL Server

Наша система работает на SQL Server 2000, и мы находимся в процессе подготовки к обновлению до SQL Server 2008. У нас есть много кода запуска, где нам нужно обнаружить изменение в данном столбце, а затем работать с этим если он изменился.

Очевидно, что SQL Server предоставляет UPDATE() и COLUMNS_UPDATED(), но эти функции говорят только о том, какие столбцы были вовлечены в оператор SQL, не, которые фактически изменили столбцы.

Чтобы определить, какие столбцы были изменены, вам нужен код, похожий на следующий (для столбца, который поддерживает 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>')

Этот код необходимо повторить для каждого столбца, который вам интересен при тестировании. Затем вы можете проверить "измененное" значение, чтобы определить, следует ли выполнять дорогостоящие операции. Конечно, этот код сам по себе проблематичен, так как он только сообщает вам, что хотя бы одно значение в столбце изменилось по всем измененным строкам.

Вы можете протестировать отдельные инструкции UPDATE с помощью следующего:

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

... но это не работает, когда вам нужно вызвать хранимую процедуру. В этих случаях, насколько я могу судить, вы должны откинуться на другие подходы.

Мой вопрос заключается в том, имеет ли кто-нибудь представление (или, еще лучше, жесткие данные) о том, какой наилучший/самый дешевый подход относится к проблеме предикации операции базы данных в триггере по вопросу о том, имеет ли значение конкретного столбца в модифицированной строке фактически изменился или нет. Ни один из вышеприведенных методов не кажется идеальным, и мне было интересно, существует ли лучший метод.

4b9b3361

ОТВЕТЫ

Ответ 1

Хотя HLGEM дал несколько хороших советов выше, это было не совсем то, что мне было нужно. За последние несколько дней я провел довольно много тестов, и решил, что по крайней мере буду делиться результатами здесь, поскольку похоже, что больше информации не поступит.

Я создал таблицу, которая была фактически более узким подмножеством (9 столбцов) одной из наших основных таблиц системы и заполнила ее производственными данными так, чтобы она была такой же глубокой, как наша производственная версия таблицы.

Затем я продублировал эту таблицу, а на первом написал триггер, который попытался обнаружить каждое отдельное изменение столбца, а затем предсказал каждое обновление столбца о том, действительно ли данные в этом столбце были изменены или нет.

Во второй таблице я написал триггер, который использовал расширенную условную логику CASE для выполнения всех обновлений ко всем столбцам в одном выражении.

Затем я выполнил 4 теста:

  • Одноколоночное обновление для одной строки
  • Одноколоночное обновление до 10000 строк
  • Обновление из девяти столбцов в одну строку
  • Обновление из девяти столбцов до 10000 строк

Я повторил этот тест как для индексированных, так и для неиндексированных версий таблиц, а затем повторил все это на серверах SQL 2000 и SQL 2008.

Результаты, которые я получил, были довольно интересными:

Второй метод (один оператор обновления с волосатой логикой CASE в предложении SET) был равномерно более эффективным, чем индивидуальное обнаружение изменений (в большей или меньшей степени в зависимости от теста) за единственное исключение, изменение столбца, влияющее на многие строки, где индексировался столбец, работает на SQL 2000. В нашем конкретном случае мы не делаем много таких узких, глубоких обновлений, как это, поэтому для моих целей подход с одной заявкой, безусловно, является способом перехода.

Мне было бы интересно услышать результаты других подобных тестов, чтобы узнать, насколько мои выводы настолько универсальны, насколько я подозреваю, или являются ли они конкретными для нашей конкретной конфигурации.

Чтобы начать работу, вот тест script, который я использовал - вам, очевидно, придется придумать другие данные, чтобы заполнить его:

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

Ответ 2

Начнем с того, что я никогда не буду, и я имею в виду никогда не вызывать хранимую процедуру в триггере. Чтобы учесть многострочную вставку, вам нужно будет курсор через proc. Это означает, что 200 000 строк, которые вы только что загрузили, хотя запрос на основе набора (например, обновляющий все цены на 10%), вполне может заблокировать таблицу в течение нескольких часов, поскольку триггер пытается отважно обрабатывать нагрузку. Плюс, если что-то изменится в proc, вы можете полностью сломать любые вставки в таблицу или даже полностью повесить таблицу. Я твердо убежден, что код запуска не должен вызывать ничего другого за пределами запуска.

Лично я предпочитаю просто выполнять свою задачу. Если я написал действия, которые я хочу сделать правильно в триггере, он будет обновлять, удалять или вставлять, когда столбцы были изменены.

Пример: предположим, что вы хотите обновить поле last_name, которое вы храните в двух местах из-за денормализации, размещенной там по соображениям производительности.

update t
set lname = i.lname
from table2 t 
join inserted i on t.fkfield = i.pkfield
where t.lname <>i.lname

Как вы можете видеть, он будет обновлять только имена, отличные от того, что в настоящее время находится в таблице, которую я обновляю.

Если вы хотите выполнять аудит и записывать только те строки, которые изменились, тогда выполните сравнение, используя все поля, что-то вроде   где i.field1 < > d.field1 или i.field2 < > d.field3 (и т.д. через все поля)

Ответ 3

Я думаю, вам может понадобиться расследовать использование оператора EXCEPT. Это оператор на основе набора, который может отсеять строки, которые не изменились. Самое приятное, что считается, что нулевые значения равны, так как он ищет строки в первом наборе, указанном перед оператором EXCEPT, а не во втором списке после 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 */

Это обрабатывает проблему столбцов, которые позволяют Nulls без использования ISNULL() в триггере, и возвращает только идентификаторы строк с изменениями в col1 для хорошего подхода на основе набора для обнаружения изменений. Я не тестировал подход, но он может стоить вашего времени. Я думаю, что EXCEPT был представлен с SQL Server 2005.

Ответ 4

Я рекомендую использовать оператор набора EXCEPT, как указано выше для Тодда/аргтипа.

Я добавил этот ответ, потому что я вставил "вставленный" перед "удаленным", чтобы были обнаружены INSERT, а также UPDATE. Поэтому у меня обычно есть один триггер, который будет охватывать как вставки, так и обновления. Также может обнаруживать удары, добавляя OR (НЕ СУЩЕСТВУЕТ (ВЫБОР * FROM вставлен) И EXISTS (SELECT * FROM deleted))

Определяет, изменилось ли значение только в указанных столбцах. Я не исследовал его производительность по сравнению с другими решениями, но он хорошо работает в моей базе данных.

Он использует оператор набора EXCEPT для возврата любых строк из левого запроса, которые также не найдены в правильном запросе. Этот код можно использовать в триггерах INSERT, UPDATE и DELETE.

Столбец "PKID" является первичным ключом. Требуется разрешить сопоставление между двумя наборами. Если у вас есть несколько столбцов для первичного ключа, вам нужно будет включить все столбцы для правильного соответствия между вставленными и удаленными наборами.

-- 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

Если вы хотите использовать измененные строки в последующей триггерной логике, я обычно помещаю результаты запроса EXCEPT в переменную таблицы, на которую можно ссылаться позже.

Надеюсь, это интересно: -)