Массовое удаление записей через CCADOConnection

[Shirmin Oleg]

Всем доброго времени суток!
Задача: удалить большое количество записей из таблицы. Т.к. while select и delete_from сильно тормозили, решил использовать CCADOConnection:

  CCADOConnection connection;
  CCADOCommand    command;
  str             sql;
  ;
  command    = new CCADOCommand();
  connection = new CCADOConnection();
  connection.connectionString(new SqlSystem().loginConnectString());
  connection.open();
  command.activeConnection(connection);
  command.commandType(1);

  sql = strfmt("delete from SALESTABLE_TONTRA40478 where SALESTABLE_TONTRA40478.month = '%1'", date2str(01\03\2010, 321, 2, 3, 2, 3, 4));

  command.commandText(sql);
  command.execute();
  connection.close();

Проблема в том, что при небольшом кол-ве строк в таблице (порядка 20000) все прекрасно работает, но если записей много (около 600000), падает со след. ошибкой:

Метод "execute" в COM-объекте класса "ADODB.Command" возвратил код ошибки 0x80040E31 (<неизвестно>), который означает: [Microsoft][SQL Native Client]Query timeout expired.

Можно ли это как-то победить?

p.s. База - SQL Server 2005, и в 4.0 и в 2009 результат один и тот же

[sukhanchik]

У класса CCADOCommand есть метод commandTimeout. Попробуйте поиграть с этим параметром (увеличив его). Тогда получится.
Но вообще - в целом - удаление столь большого кол-ва записей может занять продолжительное время (минут 30 к примеру). Связано это с тем - что информация об этих записях сначала копируется в лог (на случай отмены), а потом только удаляется. Поэтому - я бы рекомендовал отказаться от удаления такого кол-ва записей.

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

[Shirmin Oleg]

Цитата:

У класса CCADOCommand есть метод commandTimeout. Попробуйте поиграть с этим параметром (увеличив его).

От безысходности параметр выставлял аж в значение 1000000, визуально вообще ничего не изменилось (падало за то же время, что и без него)

Цитата:

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

Отказаться, к сожалению, невозможно, надо удалять.
Это периодическая операция - из внешней базы берем данные за период, если за этот же период данные уже были закачаны, то их нужно удалить.
Самое интересное, что пару раз у меня этот запрос отрабатывал, и удаление занимало примерно 15-20с (против 2-3 часов при использовании других методов)

Цитата:

можно создать копию таблицы, туда перелить нужные данные, а старую таблицу удалить.

Удаляемая часть данных - малая толика от всей таблицы, боюсь в данном случае времени на перекачку данных в новую таблицу уйдет еще больше, чем при удалении через delete_from

[sukhanchik]

Цитата:

Сообщение от Shirmin Oleg

От безысходности параметр выставлял аж в значение 1000000, визуально вообще ничего не изменилось (падало за то же время, что и без него)
...
Отказаться, к сожалению, невозможно, надо удалять.
...
Удаляемая часть данных - малая толика от всей таблицы, боюсь в данном случае времени на перекачку данных в новую таблицу уйдет еще больше, чем при удалении через delete_from

Тогда могу лишь порекомендовать либо последовать совету lev, либо ... последовать его совету, но попытаться бить на отдельные транзакции удаляемые записи.
Т.е. написать что-то типа этого:

Counter i;
;
ttsbegin;
while select forupdate mytable
{
      mytable.dodelete();
      i++;
      if (i > 10000) // Число подобрать надо
      {
            break;
      }
}
ttscommit;

И вызывать этот метод несколько раз. Общее время может увеличиться, но зато не будет "зависаний" надолго, как и уменьшится вероятность блокировок. Плюс еще прогресс-бар можно вставить.

[S.Kuskov]

Цитата:

Сообщение от sukhanchik

попытаться бить на отдельные транзакции

И даже тогда можно не отказываться от преимуществ конструкции delete_from. 'Порционного' удаления можно добиться при помощи фильтрации.

SALESTABLE_TONTRA40478 Table;
date month = 01\03\2010;
RecId minRecId, maxRecId, fromRecId, toRecId;
int partSize = 10000 // Число подобрать надо;

select minof(RecId) from Table where Table.month == month;
fromRecId = Table.RecId;

select maxof(RecId) from Table where Table.month == month;
toRecId = Table.RecId;

for(fromRecId = minRecId; fromRecId <= maxRecId;  fromRecId += partSize)
{
    toRecId = fromRecId + partSize;
    
    Table.skipDatabaseLog(true);
    Table.skipDeleteActions(true);
    Table.skipDeleteMethod(true)
    delete_from Table 
    where 
        Table.month == month &&        
        Table.RecId >= fromRecId &&
        Table.RecId <= toRecId;
}

[mazzy]

Сложно то как

Цитата:

Сообщение от S.Kuskov

'Порционного' удаления можно добиться при помощи фильтрации.

Да. Но брать порциями по PartSize - бесполезно. В интервале может быть и 10тыс записей, и ни одной. Достаточно просто сузить диапазон.

А главное - обрамить tts-скобками

SALESTABLE_TONTRA40478 Table;
date month;

while(true)
{
    select minof(month),minof(recid) from Table;
    if( !Table.recid ) break;

    month = Table.month;

    ttsbegin;
    Table.skipDatabaseLog(true);
    Table.skipDeleteActions(true);
    Table.skipDeleteMethod(true)
    delete_from Table 
    where 
        Table.month == month;
    ttscommit;
}

[S.Kuskov]

Цитата:

Сообщение от mazzy

Сложно то как

Зато универсально. Таким способом можно удалить любую другую выборку.

Цитата:

Сообщение от mazzy

Да. Но брать порциями по PartSize - бесполезно. В интервале может быть и 10тыс записей, и ни одной.

Идея в том что их гарантированно не будет больше 10тыс. Те таблица если и будет заблокированна то не на долго. А если цикл прогонит пару итераций в холостую, что ж с того?

Цитата:

Сообщение от mazzy

Достаточно просто сузить диапазон

На сколько я понял условие задачи. Удаляють нужно не все записи из таблицы, а только за определённый месяц. Причём даже за один месяц число записей > 600 000;

Цитата:

Сообщение от mazzy

А главное - обрамить tts-скобками

Зачем? Или это шутка юмора такая?

[sukhanchik]

Цитата:

Сообщение от S.Kuskov

И даже тогда можно не отказываться от преимуществ конструкции delete_from. 'Порционного' удаления можно добиться при помощи фильтрации.

У фильтрации есть тот минус, что по фильтруемому полю нужно иметь адекватный индекс, дабы фильтрация состоялась в ожидаемое время, а не произошел бы Full Table Scan. А далеко не всегда существует индекс в котором бы было единственное поле - RecID.
Конечно брать тупо по 10 (20,30) тыс. записей - тоже не вариант - объем записей бывает разный.
В общем - надо искать "золотую" середину.

[mazzy]

Цитата:

Сообщение от sukhanchik

У фильтрации есть тот минус, что по фильтруемому полю нужно иметь адекватный индекс, дабы фильтрация состоялась в ожидаемое время, а не произошел бы Full Table Scan.

Тоже верно!
причем в сообщении автора условие по этом полю и было. вполне возможно, что проблема с отсутствии индекса и в Full Table Scan.

[lev]

а перед delete_from делали skipDatabaseLog(true), skipDeleteActions(true), skipDeleteMethod(true) ?

на сколько я знаю если всё выше указанное выполнить, то delete_from должно удалить данные одним запросом к базе, т.е. делает тоже самое, что Вы делает через CCADOConnection.

[lev]

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

Т.е. я хочу сказать, не следует использовать скипы в слепую, возможно необходимо будет дописать такое же удаление подчиненных таблиц, и добавить что-то важное из метода delete()...

[Shirmin Oleg]

Цитата:

общее количество удаляемых записей сейчас порядка 500 тыс.ежедневно

За какое время удаляются?

[Zabr]

Было часа два. Стало минут 25-30.

[George Nordic]

Хороший метод устранить проблему - просто не заполнять "проблемные" данные. Перекрываете insert на таблице, пишете insert(boolean _doInsert = false), если в метод приходит false, то ничего не далаете. Тогда по-умолчанию в таблицу данные не попадут. А если надо вставить, находите откуда их необходимо заполнять и указываете xxxx.insert(true),перекрестные ссылки Вм в помощь.

Удачи!

С Уважением,
Георгий

[Zabr]

Цитата:

Сообщение от George Nordic

Хороший метод устранить проблему - просто не заполнять "проблемные" данные.

У меня данные не проблемные. На момент создания они нужны. А по прошествии строго определенного времени они становятся не нужны и их можно безболезненно чистить. Специфика конкретного решения.

[S.Kuskov]

Цитата:

Сообщение от George Nordic

Перекрываете insert на таблице, пишете insert(boolean _doInsert = false)

Радикально. Этакий антиспам фильтр получается . Но при такой трактовке задачи не лучше ли будет устранить сам источник "проблемных" данных?

[George Nordic]

Да просто давныыым-давно сталкивался именно с подобной проблемой. Например, при сводном планировании нужны InventSumLogTTS (много времени прошло, могу не помнить точно), а после - ну что с ними далать? а они расползались...

Короче, подобным способом и решили проблему. Или периодическую очистку делали?

С Уважением,
Георгий

[sukhanchik]

Цитата:

Сообщение от George Nordic

Да просто давныыым-давно сталкивался именно с подобной проблемой. Например, при сводном планировании нужны InventSumLogTTS (много времени прошло, могу не помнить точно), а после - ну что с ними далать? а они расползались...

Эээээ а команды типа TRUNCATE TABLE не пробовали? Быстро работают

Тут другая ситуация. Грузят данные. Они актуальны (например данные с магазинов). Потом (например, в течении дня) присылают откорректированные данные (кто-то ошибся). Старые данные еще не обрабатывали (они автоматически закачались, но их обработка будет к примеру на след. день). Соотв - старые данные нужно стереть, а новые закачать.

Я с подобной потребностью сталкивался при импорте строк выписки. Сотрудник заказывает предварительную (по операциям текущего дня) выписку и получает ее (включая автозагрузку в АХ). Банк кстати вполне может пойти навстречу по такой услуге, особенно если это свой банк . Затем (на след. день) выписка приходит уже окончательная. Соотв - предварительная выписка в АХ не обрабатывается в тот же день. Куда деть предварительную выписку? Стереть.

А теперь считаем, что фирма (холдинг) у нас состоит не из одного юрлица и не одного расчетного счета... Да и операций у нас много.... В общем - объемно получается

[George Nordic]

Да подожди ты таблицы резать

Надо сначала найти источник проблемы.
1. Понять какие данные нужны.
2. Когда они устаревают.
3. На что эти данные влияют? Не пригодяться ли удаленные данные впоследствии? Связи.
4. Можно ли это решить настройками. Или, например переносить в другой журнал и удалять.
5. Если нельзя программно, то сколько времени займет доработка.
6. Можно ли запускать данную процедуру на периодической основе.

А программистам сразу бы drop table


Георгий

[Shirmin Oleg]

Переделал на удаление через delete_from по частям (по 100000 записей)
Удаление 600000 записей - 1 час, удаление 1200000 записей - 2 часа
Удаление 600000 записей напрямую в SQL Server Management Studio - 16 минут.
Собственно, почему я не хотел использовать delete_from, надеялся, что будет работать быстрее, но, похоже, не судьба...