Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Перенос данных в хранилище  [new]
Micoa
Member

Откуда:
Сообщений: 22
Необходимо организовать перенос данных в специально выделенную БД данных одной из таблиц боевой базы.
Таблица содержит журнал изменений, вносимых программной платформой в данные в таблицах. Таблица - не маленькая - весит порядка 10 гигов и содержит 13 миллионнов записей. При том, что бэкап всей БД, без этой таблицы и с очищенным журналом транзакций весит от силы 500 метров.
Смысл танцев - отделение мух от котлет :-)

Ключ - unique idenitfier.
Предполагается, что в боевой базе в таблице аудита будет лежать инфа за текущий календарный месяц. Всё, что старше - переносится в хранилище.

Вопрос, как лучше это сделать - как переносить и какую структуру придумать.

Вариант А - SELECT - INSERT
INSERT INTO DWH (*) SELECT * FROM SERVER.dbo.AUDIT WHERE DT<='01.07.2009'
DELETE FROM SERVER.dbo.AUDIT WHERE DT<='01.07.2009'

Вариант Б - курсором обходить и делать INSERT DWH /DELETE SERVER.dbo.AUDIT
Вариант В - триггер AFTER INSERT на SERVER.dbo.AUDIT, пишущий эти данные в DWH

А может и сама постановка не правильна и есть другие варианты?
1 июл 09, 13:34    [7363246]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных в хранилище  [new]
petsa
Member

Откуда:
Сообщений: 1708
автор
А может и сама постановка не правильна и есть другие варианты?

Как Вам сказать, это уж сами решайте. Я подобную задачу решаю именно через INSERT...SELECT.
В час каждой ночи запускается джоб, переписывает из боевой базы в архивную данные из таблицы за прошедшие сутки, следующим шагом прибивает записи в таблице боевой базы, которые старше 3-х суток. Вроде напряга нет, отрабатывает за 10-20 секунд. количество записей от 12 до 30 тысяч.
1 июл 09, 14:20    [7363526]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных в хранилище  [new]
baracs
Member

Откуда: Москва
Сообщений: 7198
Micoa,
По варианту "А" была тема: тынц

Варианты "Б" и "В" - не понял.
1 июл 09, 14:26    [7363562]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных в хранилище  [new]
Micoa
Member

Откуда:
Сообщений: 22
вариант Б -
DECLARE Cur CURSOR FOR SELECT * FROM SERVER.dbo.AUDIT WHERE DT<='01.07.2009'
OPEN Cur
FETCH NEXT FROM Cur INTO @ID,...
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO DWH (поля) VALUES (@ID, ...)
DELETE FROM SERVER.dbo.AUDIT WHERE ID=@ID
FETCH...
END

Курсором конечно получается дольше по времени, но более прогнозируемо, плюс практически нулевой расход оперативной памяти. Даже не представляю, сколько ресурсов и времени займёт перенос одним запросом данных из одной в БД в другую. Хотя нет, представляю. Только что закончилось тестовое выполнение на 8000 записей. Заняло 13 минут....

Плюс есть ещё такой момент - идентификатор - не совсем UNIQUEIDENTIFIER, а скорее укороченный UNIQUEIDENTIFIER в формате VARCHAR с убранными символами '-'. Поменять это никак нельзя - платформа такая.
Ну и есть вариант, что в списки записей для INSERT-а будет уже сеществующая в DWH строка. Т.е. весь запрос откатится. А в случае с курсором - такой проблемы уже нет.

Вариант В - на боевой базе в таблице Audit сделать триггер AFTER INSERT с примерно таким текстом:
INSERT INTO DWH (...)
SELECT * FROM Inserted
Не нравится то, что база DWH должна быть постоянно доступна. В перспективе возможен перенос истории аудита на резервный сервер.
1 июл 09, 14:59    [7363786]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных в хранилище  [new]
Micoa
Member

Откуда:
Сообщений: 22
baracs,

В той теме рассматривается удаление. А я пока копаю в сторону копирования. Проблем со временем вставки - нет.
Но подозреваю, что будут проблемы с удалением данных во время последних экспериментов - заканчивалось место под LOG-файл на винте :-(
1 июл 09, 15:03    [7363819]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных в хранилище  [new]
baracs
Member

Откуда: Москва
Сообщений: 7198
Micoa
В той теме рассматривается удаление. А я пока копаю в сторону копирования.

Ваш первый пост начинается словами:
Micoa
Необходимо организовать перенос данных...

Вы уж с задачей определитесь :-)

Micoa
Но подозреваю, что будут проблемы с удалением данных во время последних экспериментов - заканчивалось место под LOG-файл на винте :-(

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

Триггер на боевой таблице может создать Вам проблемы со временем вставки в нее. А если он не сможет выполниться, то проблема будет с самой вставкой
1 июл 09, 15:32    [7364026]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных в хранилище  [new]
Micoa
Member

Откуда:
Сообщений: 22
Возник ещё один вопрос - корректно ли пройдёт откат транзации в этом случае:

SET XACT_ABORT ON
begin tran

INSERT INTO DWH_Audit.dbo.Audit (*) SELECT * FROM SERVER.dbo.AUDIT WHERE DT<='01.07.2009'


DELETE FROM SERVER.dbo.AUDIT WHERE DT<='01.07.2009'

commit tran

DBCC SHRINKFILE (SERVER_log, 0)
DBCC SHRINKFILE (DWH_Audit_log, 0)

Причина вот в чём - при переносе большого объёма инфы частенько заканчивается место на HDD с log-файлами. Приходится делать SHRINK.
Т.е. если в какой-либо момент запроса не хватило места для размещения логов, или записи в базе DWH_Audit уже существовали - откат произошёл бы в обеих базах.

Смысла размещать SHRINK в теле транзакции нет? По-идее он выполнится не должен будет - ведь тогда транзакция не сможет откатиться.
7 июл 09, 18:05    [7388273]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить