Доброго всем времени суток! Подскажите пожайлуста куда копать...

Сервер MS SQL 2000. Есть таблица с данными (более 40 полей) встала задача сохранять историю записей этой таблицы. В среднем таблица имеет 300.000 записей. История нужна для ведения в течение года, чтобы потом выцеплять изменения данных в течение определенного периода. Какой вижу выход(помидорами не закидывать):
1. Делать бэкап таблицы каждый раз после изменений (но данный способ кажется мне неподходящим)
2. Весить триггеры на таблицу(ы) и отслеживать Update, Insert, Delete. НО, так как база не моя, над таблицей проводится множество апдейтов по разным полям, поэтому триггер отрабатывая после каждого апдейта будет заносить в таблицу истории лишние данные, а надо всю строку.
3. Добавлять доп.столбцы в основную таблицу и отслеживать дату

Подскажите кто как выходил из данной ситуации?

Здравствуйте, dronlinux, Вы писали:

D>Доброго всем времени суток! Подскажите пожайлуста куда копать...

D>Сервер MS SQL 2000. Есть таблица с данными (более 40 полей) встала задача сохранять историю записей этой таблицы. В среднем таблица имеет 300.000 записей. История нужна для ведения в течение года, чтобы потом выцеплять изменения данных в течение определенного периода. Какой вижу выход(помидорами не закидывать):
D>1. Делать бэкап таблицы каждый раз после изменений (но данный способ кажется мне неподходящим)
D>2. Весить триггеры на таблицу(ы) и отслеживать Update, Insert, Delete. НО, так как база не моя, над таблицей проводится множество апдейтов по разным полям, поэтому триггер отрабатывая после каждого апдейта будет заносить в таблицу истории лишние данные, а надо всю строку.
D>3. Добавлять доп.столбцы в основную таблицу и отслеживать дату

D>Подскажите кто как выходил из данной ситуации?


С триггером мне кажется оптимально — или несколько полей меняются отдельными апдейтами?

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

Здравствуйте, dronlinux, Вы писали:

D>Доброго всем времени суток! Подскажите пожайлуста куда копать...

D>Сервер MS SQL 2000. Есть таблица с данными (более 40 полей) встала задача сохранять историю записей этой таблицы. В среднем таблица имеет 300.000 записей. История нужна для ведения в течение года, чтобы потом выцеплять изменения данных в течение определенного периода. Какой вижу выход(помидорами не закидывать):
D>1. Делать бэкап таблицы каждый раз после изменений (но данный способ кажется мне неподходящим)
D>2. Весить триггеры на таблицу(ы) и отслеживать Update, Insert, Delete. НО, так как база не моя, над таблицей проводится множество апдейтов по разным полям, поэтому триггер отрабатывая после каждого апдейта будет заносить в таблицу истории лишние данные, а надо всю строку.
D>3. Добавлять доп.столбцы в основную таблицу и отслеживать дату

D>Подскажите кто как выходил из данной ситуации?

Я за триггеры. Если же апдейты идут отдельно по каждой колонке (само по себе неоптимально) и хочется немного соптимизировать, то надо задать критерий группировки. Например:
— один и тот же пользователь;
— одна и та же дата (час);
— колонка изменяется впервые (учёт внутридневных изменений).

Хотя ИМХО лучше позволить триггерам сохранять в простом формате, а уже потом группировать через вьюшки по вкусу.

Здравствуйте, Delight, Вы писали:

D>Здравствуйте, dronlinux, Вы писали:

D>>Доброго всем времени суток! Подскажите пожайлуста куда копать...

D>>Сервер MS SQL 2000. Есть таблица с данными (более 40 полей) встала задача сохранять историю записей этой таблицы. В среднем таблица имеет 300.000 записей. История нужна для ведения в течение года, чтобы потом выцеплять изменения данных в течение определенного периода. Какой вижу выход(помидорами не закидывать):
D>>1. Делать бэкап таблицы каждый раз после изменений (но данный способ кажется мне неподходящим)
D>>2. Весить триггеры на таблицу(ы) и отслеживать Update, Insert, Delete. НО, так как база не моя, над таблицей проводится множество апдейтов по разным полям, поэтому триггер отрабатывая после каждого апдейта будет заносить в таблицу истории лишние данные, а надо всю строку.
D>>3. Добавлять доп.столбцы в основную таблицу и отслеживать дату

D>>Подскажите кто как выходил из данной ситуации?

D>Я за триггеры. Если же апдейты идут отдельно по каждой колонке (само по себе неоптимально) и хочется немного соптимизировать, то надо задать критерий группировки. Например:
D>- один и тот же пользователь;
D>- одна и та же дата (час);
D>- колонка изменяется впервые (учёт внутридневных изменений).

D>Хотя ИМХО лучше позволить триггерам сохранять в простом формате, а уже потом группировать через вьюшки по вкусу.

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

dronlinux пишет:
> Вы абсолютно правы, идет куча апдейтов по разным колонкам(изменить
> положение вещей не могу, так как повторюсь база не моя). Ваш вариант
> вроде неплох, но я думаю не скажется ли это на производительности? Я уже

Замедление? На 300 000 записей? Врядли.

> начинаю склонятся к варианту процедуры, которая будет сохранять
> изменения после всех апдейтов. В T-SQL не очень силен Подскажите
> ссылочки, если есть такие, может где разбиралась подобная тема?

create trigger tr_table_history on dbo.my_table
for update, delete
as
insert into my_table_history select ... from deleted

Здравствуйте, Ромашка, Вы писали:

Р>dronlinux пишет:
>> Вы абсолютно правы, идет куча апдейтов по разным колонкам(изменить
>> положение вещей не могу, так как повторюсь база не моя). Ваш вариант
>> вроде неплох, но я думаю не скажется ли это на производительности? Я уже

Р>Замедление? На 300 000 записей? Врядли.

Можно эти 300 000 записей менять 1000 раз в час.

Здравствуйте, dronlinux, Вы писали:

D>Вы абсолютно правы, идет куча апдейтов по разным колонкам(изменить положение вещей не могу, так как повторюсь база не моя).

Немного непонятно насчёт "база не моя". Имеется в виду, что её меняет приложение, которое вы не можете изменить?

D>Ваш вариант вроде неплох, но я думаю не скажется ли это на производительности?

Ну тут уже считать и тестировать надо. От конкретных условий зависит.

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

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

D>В T-SQL не очень силен Подскажите ссылочки, если есть такие, может где разбиралась подобная тема?

Сам уже давно на T-SQL не писал, так что тут сильно помочь не могу.

Здравствуйте, dronlinux, Вы писали:

D>Вы абсолютно правы, идет куча апдейтов по разным колонкам(изменить положение вещей не могу, так как повторюсь база не моя). Ваш вариант вроде неплох, но я думаю не скажется ли это на производительности? Я уже начинаю склонятся к варианту процедуры, которая будет сохранять изменения после всех апдейтов. В T-SQL не очень силен Подскажите ссылочки, если есть такие, может где разбиралась подобная тема?

Посмотрите на эту статейку.. по моему более менее исчерпывающая.

Delight пишет:
> Можно эти 300 000 записей менять 1000 раз в час.

То есть одну запись в три секунды??? У Вас там что, 8086 в качестве
сервера???

Здравствуйте, Ромашка, Вы писали:

Р>Delight пишет:
>> Можно эти 300 000 записей менять 1000 раз в час.

Р>То есть одну запись в три секунды??? У Вас там что, 8086 в качестве
Р>сервера???

Кхм-кхм. Сервер-то не у меня. Я просто указал на то, что по количеству основных записей невозможно определить сколько будет создаваться записей в историю. Может оказаться, что history в тысячи раз больше основной таблицы.

Здравствуйте, Ромашка, Вы писали:

>> Можно эти 300 000 записей менять 1000 раз в час.

Р>То есть одну запись в три секунды???

Небольшая поправочка: больше 80000 раз в секунду.

Здравствуйте, Delight, Вы писали:
> Может оказаться, что history в тысячи раз больше основной таблицы.

И поверьте — окажется.
Хотя бы сравните логи базы данных с размерами рабочей таблицы, разница где-то от 5х-10х.

Здравствуйте, Lloyd, Вы писали:

L>Здравствуйте, Ромашка, Вы писали:

>>> Можно эти 300 000 записей менять 1000 раз в час.

Р>>То есть одну запись в три секунды???

L>Небольшая поправочка: больше 80000 раз в секунду.

Что-то с цифрами не то, может жара... Наверное все-таки 83 раза

Здравствуйте, rameel, Вы писали:

L>>Небольшая поправочка: больше 80000 раз в секунду.

R>Что-то с цифрами не то, может жара... Наверное все-таки 83 раза

(300000 * 1000) / (60 * 60)?

Здравствуйте, Lloyd, Вы писали:

Здравствуйте, Lloyd, Вы писали:

Я же говорю жара Извиняюсь, читал наискосок . Может тогда ~80000 записей в секунду...

Здравствуйте, Norex, Вы писали:

>> Может оказаться, что history в тысячи раз больше основной таблицы.

N>И поверьте — окажется.
N>Хотя бы сравните логи базы данных с размерами рабочей таблицы, разница где-то от 5х-10х.

Зачем гадать (логи — это немного не в ту степь)? Отношение количества записей в истории к основным данным может быть как 0.001, так и 1000. Пусть автор смотрит и решает.

Здравствуйте, Jericho113, Вы писали:

J>Здравствуйте, dronlinux, Вы писали:

D>>Вы абсолютно правы, идет куча апдейтов по разным колонкам(изменить положение вещей не могу, так как повторюсь база не моя). Ваш вариант вроде неплох, но я думаю не скажется ли это на производительности? Я уже начинаю склонятся к варианту процедуры, которая будет сохранять изменения после всех апдейтов. В T-SQL не очень силен Подскажите ссылочки, если есть такие, может где разбиралась подобная тема?

J>Посмотрите на эту статейку.. по моему более менее исчерпывающая.

Всем спасибо за мнения! Действительно логику не программы, но сервера (именно эти апдейты), я изменить не могу. Спасибо Jericho113 за предоставленную ссылку, но данную статью я уже прочел (на неё ссылаются все). Дело в том что в процедуре, обновляющей основную таблицу проходит около 15 апдейтов на разные поля. При использовании триггера в таблицу истории уйдут все 15 изменений для одной записи (извините если не так понял тему триггеров). Хотелось бы перенести измененую запись уже после всех этих апдейтов. Имеется ли возможность сделать это с помощью триггеров? То есть получается история будет состоять не из записей при изменении одного из 30 полей, а просто строка полностью и не важно по каким полям проводились изменения. Блин чего то загнул

dronlinux пишет:
> Хотелось бы перенести измененую запись уже после
> всех этих апдейтов. Имеется ли возможность сделать это с помощью
> триггеров?

Ты знаешь, какое поле апдейтится последним?

Здравствуйте, Ромашка, Вы писали:

Р>dronlinux пишет:
>> Хотелось бы перенести измененую запись уже после
>> всех этих апдейтов. Имеется ли возможность сделать это с помощью
>> триггеров?

Р>Ты знаешь, какое поле апдейтится последним?

Да знаю. А я понял вашу мысль. То есть как только оно изменится тогда брать всю запись и в историю? Но условие обновления этого поля таковы, что оно может изменится а может и нет.
На данный момент сделан триггер:

ALTER trigger [dbo].[trg_t1_u]
on [dbo].[t1]
for update
as
begin
    insert into arch_t1 (op_id,id, f1, f2)
    select
        2,i.id, i.f1, i.f2
    from
        deleted i
        inner join inserted d on i.id = d.id
    where
        (i.f1 <> d.f1)
        and (i.f2 <> d.f2)
end

Триггер тестовый, но записи вносятся только если обновляется сразу два поля f1 и f2. Независимо сколько апдейтов над записью, вносится последнее изменение.

dronlinux пишет:
> Р>Ты знаешь, какое поле апдейтится последним?
>
> Да знаю. А я понял вашу мысль. То есть как только оно изменится тогда
> брать всю запись и в историю? Но условие обновления этого поля таковы,
> что оно может изменится а может и нет.

попробуйте использовать конструкцию

if updated(f1)
...

данный вариант не зависит от того, новое значение запишется в f1 или
нет. как только выполнится update my_table set f1=... все сработает.