Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Taблицы HISTORY.  [new]
GoldDragon
Member

Откуда: Canada
Сообщений: 317
Наш проект предусматривает сохранение HISTORY о всех действиях производимых над главными таблицами.
Для этого мы создали параллельные таблицы с той же структурой, что и главные таблицы + history fields.
А в тригерах записали условия сохранения. Чтобы эти таблицы не тормозили проект они построенны без ключей, но теперь настолько большие, что когда мы пытаемся перебросить данные из неё в архив, то это занимает оооочень много времени и в конце концов падает не выполнив то, что нам нужно. Кроме того, переброска данных из HISTORY в Архив не должен занимать больше 2, максимум 3 часов.

Что можно сделать, чтобы решить эту проблему?
20 окт 04, 16:26    [1049192]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
YellowMan
Member

Откуда: острова
Сообщений: 1047
Можно поинтересоваться как по вашему наличие к примеру кластерного индекса уменьшает производительность ?
Да и наличие некластерного не особо замедлит вашу работу - есть мнение что наоборот, намного ускорит...
20 окт 04, 16:29    [1049211]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
GoldDragon
Member

Откуда: Canada
Сообщений: 317
Когда ты делаешь, допустим 100-200 INSERT-ов, что предпочтительнее, когда есть индексы или когда их нет, вообще?
20 окт 04, 17:03    [1049421]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
Glory
Member

Откуда:
Сообщений: 104760
и в конце концов падает не выполнив то, что нам нужно.
С сообщением что transaction log is full ?
Если да то, единственное что вы можете сделать
- либо увеличить место отведенное под transaction log
- либо переносить записи порциями.
20 окт 04, 17:04    [1049432]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
YellowMan
Member

Откуда: острова
Сообщений: 1047
GoldDragon
Когда ты делаешь, допустим 100-200 INSERT-ов, что предпочтительнее, когда есть индексы или когда их нет, вообще?


Предпочтительнее чтобы их не было вообще - если конечно кроме инсертов с этой таблицей не выполняется ни селекта, ни апдейта, ни удаления.
Правда не совсем понятно зачем хранить такие данные - может их проще вообще никуда не записывать ?
20 окт 04, 17:11    [1049462]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
GoldDragon
Member

Откуда: Canada
Сообщений: 317
Верно.
И мы это делаем, вручную. Но, это должно происходить ночью, как отдельный процесс. И непросто переносить, а согласно определённому критерию. Часть данных остаётся, а, до определлённой даты, переносится в архив. Но в один день может сохранится только 1 запись, а в другой от 10000 до 20000. Поэтому трудно выделить определлённые блоки. И всё это выполняется внутри COM-транзакции. Нужно, чтобы INSERT и DELETE или оба выполнились или оба не выполнились. А также нельзя взять следующую партию до тех пор, пока предыдущая не прошла. В таком случае, падает весь процесс, и на следующую ночь в таблице уже находятся данные за 2 дня, потихоньку накапливаясь :(. Кроме того, как я сказал, всё это не должно занимать больше чем пару часов, потому что есть другие ночные процессы, которые должны выполнятся.
20 окт 04, 17:18    [1049494]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
GoldDragon
Member

Откуда: Canada
Сообщений: 317
YellowMan
GoldDragon
Когда ты делаешь, допустим 100-200 INSERT-ов, что предпочтительнее, когда есть индексы или когда их нет, вообще?


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


Скажем так, эта таблица на 98% используется под INSERT.
Только, когда клиент хочет узнать, почему вдруг у него такие данные, а не другие, он может посмотреть в таблицу HISTORY и узнать, кто и когда сделать ту или иную запись. Наш клиент - это Банк. Сам понимаешь, что основные данные должны быть под контролем и если что, чтобы можно было вернуть их. Backup не всегда помогает. Иногда нужно вернуть только 1 запись, а не весь DB.
20 окт 04, 17:23    [1049529]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
Glory
Member

Откуда:
Сообщений: 104760
он может посмотреть в таблицу HISTORY и узнать, кто и когда сделать ту или иную запись.
И сколько клиент согласен ждать результатов такого запроса ?
А сколько он согласен ждать выполнения INSERT-а при записи истории ?
20 окт 04, 17:32    [1049575]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
GoldDragon
Member

Откуда: Canada
Сообщений: 317
Glory
он может посмотреть в таблицу HISTORY и узнать, кто и когда сделать ту или иную запись.
И сколько клиент согласен ждать результатов такого запроса ?
А сколько он согласен ждать выполнения INSERT-а при записи истории ?


INSERT выполняется каждый день, а вот просмотр HISTORY - ооооочень редко. По крайней мере сейчас. Поэтому, если ему вдруг захочется посмотреть HISTORY, то ему придётся немного подождать. Поэтому, я и спрашиваю здесь, может, то что сделано, сделано неверно и нужно было по другому всё это сделать. Например, сейчас проект в 200-300 заданий сохраняется в DB в течении 2 минут, из-за различных проверок. А я не говорю про проекты в 1000-3000 заданий. Так, что если ещё и тригеры будут замедлять больше чем надо, то нас вышвырнут оттуда за милую душу :)
20 окт 04, 17:56    [1049716]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
Glory
Member

Откуда:
Сообщений: 104760
Поэтому, я и спрашиваю здесь, может, то что сделано, сделано неверно и нужно было по другому всё это сделать.
Не совсем понятно тогда. Если клиент(или еще кто) удовлетворен временем как INSERT-а в таблицу истории так SELECT-а из нее то в чем проблема-то ?
Если не удовлетворен то нужно искать узкое место конкретной операции и пытаться егго оптимизировать.
20 окт 04, 18:03    [1049748]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
GoldDragon
Member

Откуда: Canada
Сообщений: 317
Вопрос не в этом. Рано или поздно эта таблица будет настолько большая, что хочешь, не хочешь она будет мешать и INSERT-у и SELECT-у из неё. Поэтому нам нужен ночной процесс, который бы перебрасывал старые данные из неё в архив. В этом и проблема. Ночью он падает по причине LOG-a.
20 окт 04, 18:39    [1049874]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
Glory
Member

Откуда:
Сообщений: 104760
Ночью он падает по причине LOG-a.
Про заполнение лога транзакций я вам ответил. Наличие отсутсутствие индекса на таблице источнике не влияет на размер транзакции и необходимого под нее места в журнале.
20 окт 04, 18:48    [1049899]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
GoldDragon
Member

Откуда: Canada
Сообщений: 317
Glory
Ночью он падает по причине LOG-a.
Про заполнение лога транзакций я вам ответил. Наличие отсутсутствие индекса на таблице источнике не влияет на размер транзакции и необходимого под нее места в журнале.


Не совсем.
1. Мы не можем без ограничений увеличивать LOG(уже сейчас он 10G). Кроме того, время выполнения оооочень долгое.

2. Про разбиение на куски я уже ответил.И объяснил с какими проблемами мы столкнулись
20 окт 04, 18:57    [1049930]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
Glory
Member

Откуда:
Сообщений: 104760
2. Про разбиение на куски я уже ответил.И объяснил с какими проблемами мы столкнулись
Эээ. Это где ? Что-то я не вижу.
20 окт 04, 19:00    [1049939]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
GoldDragon
Member

Откуда: Canada
Сообщений: 317
Glory
2. Про разбиение на куски я уже ответил.И объяснил с какими проблемами мы столкнулись
Эээ. Это где ? Что-то я не вижу.


запись - 1049494
20 окт 04, 19:03    [1049946]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
Glory
Member

Откуда:
Сообщений: 104760
Ну извините. Вы ставите невыпонимые условия - хотите чтобы перенос происходил как одна транзакции и при этом не можете выделить ресурсы для ее осуществления. Придется выбирать.
К тому же не понятно. Почему вы не хотите сделать INSERT/DELETE в транзакции но только для скажем 1000 записей? И повторять эту операцию в цикле пока есть записи для переноса
20 окт 04, 19:10    [1049956]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
YellowMan
Member

Откуда: острова
Сообщений: 1047
автор
Мы не можем без ограничений увеличивать LOG(уже сейчас он 10G). Кроме того, время выполнения оооочень долгое.


Может быть его стоит обрезать перед копированием или между блоками ?
Я правильно понял что в таблице с актуальными данными индексов нет и каждую ночь из этой таблицы в таблицу History переносится часть данных попадающих под определенный критерий ? И то что переносится значит удаляется в основной таблице и записывается в History ? И все это делается в одной транзакции ?
20 окт 04, 19:25    [1049980]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
GoldDragon
Member

Откуда: Canada
Сообщений: 317
Glory
Ну извините. Вы ставите невыпонимые условия - хотите чтобы перенос происходил как одна транзакции и при этом не можете выделить ресурсы для ее осуществления. Придется выбирать.
К тому же не понятно. Почему вы не хотите сделать INSERT/DELETE в транзакции но только для скажем 1000 записей? И повторять эту операцию в цикле пока есть записи для переноса


Ты предлагаешь такой вариант? :
BEGIN TRAN

Select top 1000 .... where criteria ...

если есть данные, то:
INSERT .... FROM SELECT TOP 1000 ... where criteria
DELETE .... FROM SELECT TOP 1000 ... where criteria

END TRAN

Приведи пример, пожалуйста, но с участием 2 или более ключей, предпочтительно без IN-ов.
20 окт 04, 19:26    [1049984]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
GoldDragon
Member

Откуда: Canada
Сообщений: 317
YellowMan
автор
Мы не можем без ограничений увеличивать LOG(уже сейчас он 10G). Кроме того, время выполнения оооочень долгое.


Может быть его стоит обрезать перед копированием или между блоками ?
Я правильно понял что в таблице с актуальными данными индексов нет и каждую ночь из этой таблицы в таблицу History переносится часть данных попадающих под определенный критерий ? И то что переносится значит удаляется в основной таблице и записывается в History ? И все это делается в одной транзакции ?


Нет, не совсем правильно.
Вот исходные данные:
Есть таблица X, X_H и X_A.
Когда в таблице происходит INSERT, UPDATE, DELETE, то с помощью триггера старые данные сохраняются в X_H и пишется какое действие произошло, когда , кто и т.д. и т.п.
После этого каждую ночь нужно наиболее старые данные, согласно определённому критерию, переправить из X_H в Х_А. Чтобы X_H работала более эффективно, индексы были убртаны из неё.

Проблема заключается в том как выбрать наиболее оптимальный блок. Как это сделать с помощью TOP 1000 я не знаю, а вот с помощью к примеру даты, могу сделать, но в этом случае может случится так, что в определённый день было ооооочень много различных изменений и снова из-за LOG-а данные не перейдут из X_H в X_A.
А так как я не могу взять другой день пока не выполнен наиболее старые, то данные не переходят из Х_H в X_A.
20 окт 04, 19:39    [1049997]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
Glory
Member

Откуда:
Сообщений: 104760
1. Для таблицы приемника делаем триггер
create trigger ti_table2 on table2
for insert
as
if @@rowcount > 0
begin
set nocount on
delete table1 
from table1 a inner join inserted b on b.f1 = a.f1 and b.f2 = a.f2
end
go

2. Собственно цикл удаления
declare @limit integer
set @limit = 1000
set rowccount @limit
select 'Starting'
while @@rowcount > 0
	insert table2(...)
	select ... from table1 where ...

set rowccount 0
go

Вот для времени выполнения запроса из триггера - delete table1 from table1 a inner join inserted b on b.f1 = a.f1 and b.f2 = a.f2 - как раз может сыграть роль наличие у table1 индекса по столбцам используемым в соединении on b.f1 = a.f1 and b.f2 = a.f2
20 окт 04, 19:39    [1049998]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
YellowMan
Member

Откуда: острова
Сообщений: 1047
автор
После этого каждую ночь нужно наиболее старые данные, согласно определённому критерию, переправить из X_H в Х_А. Чтобы X_H работала более эффективно, индексы были убртаны из неё.
.
.
.
...из-за LOG-а данные не перейдут из X_H в X_A.


Во первых нужен кластерный индекс в Х_Н по критерию отбора ненужных данных - это для того чтобы запрос не выполнялся 3-4 часа.
Во вторых, как уже было сказанно, данные надо убирать кусками определенного размера и в промежутках между этим чистить лог, т.е. часть кода от Glory переписать вот так :

while @@rowcount > 0
             --добавить
             begin tran
	insert table2(...)
	select ... from table1 where ...
             --добавить
             delete from table1 where ...
             commit tran
             backup log ... truncate_only 
set rowccount 0
20 окт 04, 20:05    [1050024]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
GoldDragon
Member

Откуда: Canada
Сообщений: 317
Glory
1. Для таблицы приемника делаем триггер


Совсем неплохо. О тригере в архиве я совсем не подумал. Мне rowcount не поможет, потому что мы не работаем с SP, но триггер будет запускаться всякий раз, когда будут добавляться в него записи.

А что если написать просто такой INSERT.
INSERT X_A(...)
SELECT TOP 1000 (...) FROM X_H WHERE Criteria.

И как мне кажется больше ничего не нужно :)
20 окт 04, 20:11    [1050032]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
GoldDragon
Member

Откуда: Canada
Сообщений: 317
И ещё, я совсем не подумал. Что случится если COM транзакция упадёт перед тем как trigger сделает delete?
20 окт 04, 20:21    [1050040]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
Glory
Member

Откуда:
Сообщений: 104760
А что если написать просто такой INSERT.
INSERT X_A(...)
SELECT TOP 1000 (...) FROM X_H WHERE Criteria.

И как мне кажется больше ничего не нужно :)

Хм. А цикл как получится ?

Что случится если COM транзакция упадёт перед тем как trigger сделает delete?
Триггера всегда выполняются в транзакции.
Если откатится INSERT X_A(...) SELECT (...) FROM X_H WHERE Criteria. То и действия триггера откатится. И наоборот. Вот почему в приведенном мною примере вообще нет begin/commit transaction
20 окт 04, 20:25    [1050046]     Ответить | Цитировать Сообщить модератору
 Re: Taблицы HISTORY.  [new]
GoldDragon
Member

Откуда: Canada
Сообщений: 317
Glory

И как мне кажется больше ничего не нужно :)
Хм. А цикл как получится ?


TOP 1000 ? Разве он не принесёт только 1000 строчек?
20 окт 04, 20:32    [1050057]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить