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

Откуда: Белгород
Сообщений: 458
Подскажите как поступить?
Имеется таблица, в ней примерно 16 млн. записей
Нужно удалить примерно 12 млн. записей
Пробывал выполнять обычным скриптом:
delete from table_name where key_value1='' and key_value2=0
выполнялся очень долго, около часа, в ldf съел 16 гектар места на диске, места больше нет.
Какие имеются еще способы быстрого удаления данных?
29 авг 11, 09:09    [11193183]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
aleks2
Guest
1. Быстрых нет. Есть правильные.
2. Если таблица не связана кучей foreign keys с другими - создать копию, скопировать туда нужные строки, дропнуть старую, переименовать новую.
3. Если п2. невозможен - удалять пачками по 1000-10000 записей, коммитя транзакцию после удаления пачки (на модели Full это не канает).
29 авг 11, 09:15    [11193206]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34750
Блог
если это нужно делать постоянно - делите таблицу на секции и удаляете ненужные за микросекунды
29 авг 11, 09:26    [11193249]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
Алексей Кр
Member

Откуда: Белгород
Сообщений: 458
Нет, нужно один раз.
Подскажите тогда, как это удалять пачками, как count прикрутить к delete?
Тогда все нужно обернуть в курсор (по циклу) ?
29 авг 11, 09:38    [11193294]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
DENIS_CHEL
Member

Откуда:
Сообщений: 23097
Алексей Кр
Нет, нужно один раз.
Подскажите тогда, как это удалять пачками, как count прикрутить к delete?
Тогда все нужно обернуть в курсор (по циклу) ?


Версия какая?

DELETE FROM [ваша таблица]
WHERE [ID] IN
(SELECT TOP 10000 * FROM [ваша таблица])

В 2005

DELETE TOP (10000) 
FROM [ваша таблица] 
29 авг 11, 09:45    [11193315]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
DENIS_CHEL
Member

Откуда:
Сообщений: 23097
Про блок

where key_value1='' and key_value2=0

или что там у вас думаем сами...
29 авг 11, 09:46    [11193321]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37152
DENIS_CHEL
Про блок

where key_value1='' and key_value2=0

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

Сообщение было отредактировано: 29 авг 11, 09:49
29 авг 11, 09:48    [11193327]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
aleks2
Guest
Алексей Кр
Нет, нужно один раз.
Подскажите тогда, как это удалять пачками, как count прикрутить к delete?
Тогда все нужно обернуть в курсор (по циклу) ?

Какие умные слова (курсор) он знает!
declare @rc int

set @rc=1
set ROWCOUNT 1000

while @rc>0 begin
  delete FROM aTable WHERE ...
  set @rc=@@rowcount
  while @@trancount>0 commit
end
29 авг 11, 12:02    [11194064]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
aleks2
set ROWCOUNT 1000

и
aleks2
delete...

- я б поостерегся:
BOL
Использование инструкции SET ROWCOUNT не будет оказывать влияния на инструкции DELETE, INSERT и UPDATE в следующем выпуске SQL Server. В новых разработках следует избегать совместного использования инструкции SET ROWCOUNT с инструкциями DELETE, INSERT и UPDATE. Также необходимо запланировать внесение изменений в используемые приложения. Кроме того, рекомендуется переписать инструкции DELETE, INSERT и UPDATE, которые используют инструкцию SET ROWCOUNT в настоящее время, используя ключевое слово TOP.
29 авг 11, 12:18    [11194169]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47051
aleks2
Алексей Кр
Нет, нужно один раз.
Подскажите тогда, как это удалять пачками, как count прикрутить к delete?
Тогда все нужно обернуть в курсор (по циклу) ?

Какие умные слова (курсор) он знает!
declare @rc int

set @rc=1
set ROWCOUNT 1000

while @rc>0 begin
  delete FROM aTable WHERE ...
  set @rc=@@rowcount
  while @@trancount>0 commit
end
Прошу прощения, но где же BEGIN TRANSACTION?
А если его нет, то DELETE и сам закоммитится.
Предлагаю убрать строчку с commit.

Ну и для Version>=90 лучше всё же DELETE TOP(1000) вместо SET ROWCOUNT
29 авг 11, 12:18    [11194173]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34750
Блог
SamMan,

автору топика нужно сделать это 1 раз, вряд ли он будет ждать следующей версии))
29 авг 11, 12:28    [11194261]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
aleks2
Guest
iap
Предлагаю убрать строчку с commit.


Хе-хе? А фдруг у тредстартера ImplicitTransaction ON?
29 авг 11, 16:19    [11196080]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47051
aleks2
iap
Предлагаю убрать строчку с commit.


Хе-хе? А фдруг у тредстартера ImplicitTransaction ON?
Ну да, в некоторых случаях чёрт те что получится.
Но и коммитить всю неизвестно когда стартовавшую транзакцию тоже, знаете ли, как-то неправильно. Или нормально?
29 авг 11, 16:25    [11196134]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
1d0
Member

Откуда: инфа100%
Сообщений: 2521
индексы отключать не забываем
29 авг 11, 16:26    [11196136]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
1d0
Member

Откуда: инфа100%
Сообщений: 2521
я делал так

WHILE 1 = 1
BEGIN
delete TOP (100000) from [dbo].[xxx] 
WHERE  xxx in 
(select xxx from xxx
where xx = '69' and xxx < 21000000) 

IF @@ROWCOUNT < 100000 BREAK;
END
29 авг 11, 16:27    [11196146]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47051
1d0
я делал так

WHILE 1 = 1
BEGIN
delete TOP (100000) from [dbo].[xxx] 
WHERE  xxx in 
(select xxx from xxx
where xx = '69' and xxx < 21000000) 

IF @@ROWCOUNT < 100000 BREAK;
END
А я так:
BeginDelete: DELETE TOP(10000) [Table] WHERE ....;
IF @@ROWCOUNT>0 GOTO BeginDelete;
29 авг 11, 16:36    [11196216]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
flexgen
Member

Откуда: Город на песке
Сообщений: 832
Можно выгрузить нужные 4 миллиона записей с помощью BCP в дамп файл, потом truncate table, потом загрузить данные из дампа в таблицу.
29 авг 11, 17:16    [11196512]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
J.d
Member

Откуда: Москва
Сообщений: 691
а я бы еще забэкапился на всякий случай!!!! =)
29 авг 11, 17:23    [11196567]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
HoBTID
Member

Откуда:
Сообщений: 929
iap
1d0
я делал так

WHILE 1 = 1
BEGIN
delete TOP (100000) from [dbo].[xxx] 
WHERE  xxx in 
(select xxx from xxx
where xx = '69' and xxx < 21000000) 

IF @@ROWCOUNT < 100000 BREAK;
END
А я так:
BeginDelete: DELETE TOP(10000) [Table] WHERE ....;
IF @@ROWCOUNT>0 GOTO BeginDelete;

А я так:
SELECT 1

WHILE @@ROWCOUNT > 0
	DELETE TOP (1000) [Table]
	WHERE ...
Структурное программирование, без GOTO и BREAK.
29 авг 11, 18:14    [11197038]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47051
HoBTID
А я так:
SELECT 1

WHILE @@ROWCOUNT > 0
	DELETE TOP (1000) [Table]
	WHERE ...
Структурное программирование, без GOTO и BREAK.
Какие мелочи!
Зато у меня никакого фиктивного рекордсета не возвращает!
29 авг 11, 20:42    [11197654]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
bacalavr
Member

Откуда:
Сообщений: 311
iap,
declare @t int = 1 
=)
30 авг 11, 15:41    [11201635]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Удаление большого количества данных из таблицы  [new]
Шамиль Фаридович
Member

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

1. Что будет работать быстрее - каскад или поэтапное удаление снизу вверх?
2. Можно ли задать tablock для detail-таблиц при каскадном удалении строк из таблицы-master'a ?
3. Зачем нужно удалять порциями по 1000-10000, это работает реально быстрее,
чем удаление сразу всех необходимых строк?
26 авг 15, 16:09    [18071298]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
o-o
Guest
Шамиль Фаридович
3. Зачем нужно удалять порциями по 1000-10000, это работает реально быстрее,
чем удаление сразу всех необходимых строк?

хотя бы потому, что удаление логируется
и если мелкими транзакциями, лог можно "рециклировать" (в FULL не катит)
+ если прервется удаление терабайта на половине,
будете ждать откат этих полтерабайта + заново начинать с нуля,
а мелкими порциями вы же после каждой коммитите, одна порция отвалится, с нее и начнете,
прождав откат удаления всего тысячи строк
26 авг 15, 16:24    [18071399]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 481
o-o
и если мелкими транзакциями, лог можно "рециклировать"

Я так понимаю речь идет о журнале транзакций. Что значит "рециклировать". Каким образом это улучшает быстродействие, не могли бы вы пояснить или кинуть ссылку?

Имеет ли смысл дробить большой UPDATE данных ?
26 авг 15, 19:13    [18072540]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого количества данных из таблицы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31783
Шамиль Фаридович
Я так понимаю речь идет о журнале транзакций. Что значит "рециклировать". Каким образом это улучшает быстродействие, не могли бы вы пояснить или кинуть ссылку?
Рециклировать - имеется в виду, использовать имеющийся файл лога, когда только что занёсённые в лог данные помечаются ненужными. Это не повышает быстродействие напрямую, но убирает необходимость делать большой файл.

Шамиль Фаридович
Имеет ли смысл дробить большой UPDATE данных ?
Ну вот у меня ежедневно удалялись данные, на протяжение нескольких лет, удаление занимало по 5 часов в день. При этом в таблицу постоянно вставляются данные, каждый час вставка новых данных занимает 40 минут.

Если я буду удалять одной командой, то:
1. При сбое у меня откат будет занимать те же 5 часов, а потом всё заново...
2. Удаление займёт много места в логе (хотя ладно, это ерунда, место можно найти)
3. Вставка записей на время удаления будет приостановлена

Вот из за этого ответ "имеет смысл"
26 авг 15, 21:04    [18072974]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить