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

Откуда:
Сообщений: 79
Добрый день, подскажите пожалуйста.

Есть ли смысл в порционном удалении (по 10к строк) в таблице, среднее количество удаляемых строк 1-3 миллиона, если это удаление обернуто в пользовательскую транзакцию?
В самой транзакции наряду с удалением уйма остальных операций (вставка, обновление…).

....
BEGIN TRY
      BEGIN TRAN

      WHILE EXISTS ( SELECT NULL 
                     FROM TABLE1 WHERE ....)

		DELETE TOP ( 10000 )
		FROM TABLE1
		WHERE ...
      ....
	 INSERT ....
	 UPDATE ....

	 COMMIT TRAN
END TRY
BEGIN CATCH
      ROLLBACK TRAN
END CATCH
15 июн 16, 12:02    [19295007]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8807
СТУДЕНТ123,

зависит от "железа". Экспериментируйте.
15 июн 16, 12:30    [19295146]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
СТУДЕНТ123
Member

Откуда:
Сообщений: 79
Владислав Колосов,

ОК, спасибо, буду тестить.
15 июн 16, 12:59    [19295331]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
СТУДЕНТ123,

Смысл порционного update/delete:
а) получить менее ресурсоемкий план выполнения и, в конечном итоге, выиграть в общем времени выполнения;
б) сократить потребление журнала транзакций при простой модели восстановления, соответственно уменьшив затраты на откат при ошибке;
в) сократить число блокировок;
г) при ошибке откатить результат обработки только текущей порции.

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

От "железа" это не зависит.
15 июн 16, 13:19    [19295419]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
СТУДЕНТ123
Member

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

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

Буду сравнивать планы, есть ли прирост по факту.
15 июн 16, 13:25    [19295449]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
Владислав Колосов
Member

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

разве сбор журнала на диск не зависит от производительности и объема оперативной памяти оборудования?
15 июн 16, 14:02    [19295601]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8807
*сброс
15 июн 16, 14:03    [19295607]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Владислав Колосов
invm,

разве сбор журнала на диск не зависит от производительности и объема оперативной памяти оборудования?
Что такое "сбор журнала"?
15 июн 16, 14:04    [19295612]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Владислав Колосов
*сброс
От памяти вообще никак не зависит, от диска... ну, быстрее или медленнее запись в журнал будет производиться.
15 июн 16, 14:05    [19295615]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Владислав Колосов
разве сбор журнала на диск не зависит от производительности и объема оперативной памяти оборудования?
Сброс буфера журнала на диск от памяти никак не зависит. И от диска тоже не зависит.
От диска зависит лишь производительность этого самого сброса.

Объем потребленного места в ЖТ не зависит от того выполнилась ли инструкция целиком или была поделена на порции. Потому что в ЖТ каждая обработанная строка регистрируется отдельно.
15 июн 16, 14:15    [19295682]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
invm
Объем потребленного места в ЖТ не зависит от того выполнилась ли инструкция целиком или была поделена на порции. Потому что в ЖТ каждая обработанная строка регистрируется отдельно.
Ну, строго говоря, есть в журнале отдельные отметки о начале и конце транзакции. Но по сравнению с логируемыми строками таблицы они настолько малы по размеру, что ими можно пренебречь.
15 июн 16, 14:53    [19295858]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Гавриленко Сергей Алексеевич
Ну, строго говоря, есть в журнале отдельные отметки о начале и конце транзакции.
Есть такое.
Но тут же речь ведется о разбиении на порции в рамках одной транзакции.
15 июн 16, 14:57    [19295880]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
Владислав Колосов
Member

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

тогда в чем заключается эффект ускорения "порций" - только в автоприращении журнала в случае простой модели восстановления базы?
15 июн 16, 15:31    [19296029]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
по поводу одной транзакции - бессмысленно вы ничего не освобождаете и меньше не пишете. минусы вот:
http://dbadiaries.com/how-to-delete-millions-of-rows-using-t-sql-with-reduced-impact
Для себя я вынес физическое удаление за пределы бизнес логики обработки(метится запись на удаление), и удаляем "по свободе"
15 июн 16, 15:49    [19296105]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Владислав Колосов
тогда в чем заключается эффект ускорения "порций"

- в менее затратном плане выполнения.
- в отсутствии необходимости приращений ЖТ, если таковые потребуются, при простой модели восстановления и обработке порций в отдельных транзакциях.
15 июн 16, 15:58    [19296159]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
o-o
Guest
invm
Владислав Колосов
тогда в чем заключается эффект ускорения "порций"

- в менее затратном плане выполнения.
- в отсутствии необходимости приращений ЖТ, если таковые потребуются, при простой модели восстановления и обработке порций в отдельных транзакциях.

у ТС-то все равно все обернуто в одну мега-транзакцию.
дели-не дели, журнал не будет очищен до завершения этой транзакции.
поэтому остается один только пункт, другой план выполнения.
можете привести пример или просто подать идею,
за счет чего план может оказаться "менее затратным"?
15 июн 16, 16:10    [19296217]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
o-o
у ТС-то все равно все обернуто в одну мега-транзакцию.
У ТС да, но ответ был для Владислава Колосова.
o-o
можете привести пример или просто подать идею,
за счет чего план может оказаться "менее затратным"?
Например, "без порций" в плане будут hash-джойны со сканами таблиц/индексов и спиллом в tempdb.
А "с порциями" получатся NL с index seek.
15 июн 16, 16:20    [19296303]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
o-o
Guest
invm
Например, "без порций" в плане будут hash-джойны со сканами таблиц/индексов и спиллом в tempdb.
А "с порциями" получатся NL с index seek.

а пример можно, когда один hash join
проигрывает куче NL?
я запущу на местном сервере для проверки.
у нас любой тормоз на поверку оказывается NL,
а насильное его приведение к hash join сразу улучшает ситуацию.

если такой пример есть и прокатит,
я все NL побью на циклы,
а то хинтить нехорошо
15 июн 16, 16:33    [19296369]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Разиение одной большой операции на порции как такового ускорения не дает, но дает управляемость процессом: хочешь быстрее (в несколько потоков), хочешь медленее с waitfor delay между тиерациями(потому что очередь на Redo растет, напрмер), хочешь, вообще останови.
15 июн 16, 16:46    [19296457]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
o-o
Guest
Гавриленко Сергей Алексеевич
хочешь, вообще останови.

это точно о примере ТС или абстрактно?
у него все завернуто в одну обрамляющую транзакцию,
хоть так останови, хоть сяк, роллбэк *на все* гарантирован.
15 июн 16, 16:57    [19296536]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8807
Гавриленко Сергей Алексеевич,

дает, как ни странно, в этом я успел убедиться при выполнении update данных за месяц. На одном из стареньких сервером эмпирическим потолком было около 2 млн записей, после этого начиналась резкая деградация производительности. Пачки по 1.5-2.0 млн записей работали в целом раз в 10 быстрее, чем при попытке обновить полный набор строк.

История, правда, давняя, но запомнилась.
15 июн 16, 18:05    [19296983]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
o-o
а пример можно, когда один hash join
проигрывает куче NL?
Пожалуйста. Ограничьте память серверу одним-двумя Гб и запустите:
+
use tempdb;
set nocount on;
go

create table dbo.t1 (id int identity not null primary key, s char(100));
create table dbo.t2 (id int identity not null primary key, s char(100));

insert into dbo.t1 with (tablock)
select top (5000000)
 'a'
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

insert into dbo.t2 with (tablock)
select s from dbo.t1 order by id;
go

declare @s char(200), @dt datetime2, @id int;

select @dt = sysdatetime();

select
 @s = t1.s + t2.s
from
 dbo.t1 join
 dbo.t2 on t2.id = t1.id
option
 (hash join, maxdop 1);

select datediff(ms, @dt, sysdatetime());

select @dt = sysdatetime(), @id = 0;

while @id < 500
 begin
  select
   @s = t1.s + t2.s
  from
   dbo.t1 join
   dbo.t2 on t2.id = t1.id
  where
   t1.id > @id * 10000 and t1.id <= (@id + 1) * 10000
  option
   (loop join, maxdop 1);

  select @id += 1;
 end;

select datediff(ms, @dt, sysdatetime());
go

drop table dbo.t1, dbo.t2;
go

o-o
если такой пример есть и прокатит,
я все NL побью на циклы,
а то хинтить нехорошо
Универсального рецепта нет. И далеко не факт, что в вашем конкретном случае будет положительный результат.
15 июн 16, 18:38    [19297095]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция  [new]
o-o
Guest
invm,
Ясно, спасибо.
Наверное, это и есть случай Колосова.
У нас гиг поболее :)
15 июн 16, 19:38    [19297276]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить