Способы ускорения огромного DELETE FROM <table> без предложений

Использование SQL Server 2005.

Я выполняю огромный DELETE FROM без каких-либо предложений. Это в основном эквивалентно утверждению TRUNCATE TABLE, за исключением того, что мне не разрешено использовать TRUNCATE. Проблема в том, что таблица огромна - 10 миллионов строк, и для ее завершения требуется более часа. Есть ли способ сделать это быстрее:

  • Использование Truncate
  • Отключение или отбрасывание индексов

t-журнал уже находится на отдельном диске.

Любые предложения приветствуются!

34 голоса | спросил tuseau 15 MarpmTue, 15 Mar 2011 16:53:12 +03002011-03-15T16:53:12+03:0004 2011, 16:53:12

4 ответа


38

Что вы можете сделать, так это пакетное удаление:

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable

Где ххх, скажем, 50000

Модификация этого, если вы хотите удалить очень высокий процент строк ...

SELECT col1, col2, ... INTO #Holdingtable
           FROM MyTable WHERE ..some condition..

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable WHERE ...

INSERT MyTable (col1, col2, ...)
           SELECT col1, col2, ... FROM #Holdingtable
ответил gbn 15 MarpmTue, 15 Mar 2011 16:57:56 +03002011-03-15T16:57:56+03:0004 2011, 16:57:56
19

Вы можете использовать предложение TOP, чтобы сделать это легко:

WHILE (1=1)
BEGIN
    DELETE TOP(1000) FROM table
    IF @@ROWCOUNT < 1 BREAK
END
ответил SQLRockstar 15 MarpmTue, 15 Mar 2011 17:22:10 +03002011-03-15T17:22:10+03:0005 2011, 17:22:10
6

Я согласен с предложениями по пакету удаления в управляемые куски, если вы не можете использовать TRUNCATE, и мне нравится предложение drop /create для его оригинальности, но мне интересно узнать следующий комментарий в вашем вопросе:

  

Это в основном эквивалентно утверждению TRUNCATE TABLE - , за исключением того, что мне не разрешено использовать TRUNCATE

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

Предполагая, что это так, мне интересно, если создание хранимой процедуры, использующей TRUNCATE TABLE, и использование «EXECUTE AS» будет считаться жизнеспособной альтернативой предоставлению обеспечительных прав, необходимых для усечения таблицы напрямую.

Надеюсь, это даст вам необходимую вам скорость, а также устранит проблемы безопасности, которые могут возникнуть у вашей компании при добавлении вашей учетной записи в роль db_ddladmin.

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

Если по какой-то причине это не приемлемое решение, и ваша необходимость удалить данные в этой таблице, это то, что нужно сделать один раз в день /час /и т. д., я бы попросил, чтобы было создано задание агента SQL обрезать таблицу в запланированное время каждый день.

Надеюсь, это поможет!

ответил Jeff 18 MarpmFri, 18 Mar 2011 19:13:23 +03002011-03-18T19:13:23+03:0007 2011, 19:13:23
5

За исключением усечений .. только удаление пакетами может помочь вам.

Вы можете отказаться от таблицы и воссоздать ее со всеми ограничениями и индексами, вне курса. В Management Studio вы можете сценаризировать таблицу, которую нужно удалить и создать, поэтому это должен быть тривиальный вариант. Но это только в том случае, если вам разрешено делать DDL-действия, и я вижу, что это не вариант.

ответил Marian 15 MarpmTue, 15 Mar 2011 18:44:22 +03002011-03-15T18:44:22+03:0006 2011, 18:44:22

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132