Существует ли более быстрый способ перемещения строк данных между базами данных SQL Server, чем при использовании CTE?

Question

Я использую следующий запрос в SQL Server 2012 Express для перемещения данных старше 4 дней из одной [первичной] базы данных в другую [вторичную базу данных] для целей архивирования. Единственная проблема заключается в том, что это переводит базу данных в автономный режим, поскольку она обычно перемещает около 500 тыс. Строк данных, и эта цифра растет с добавлением большего количества точек данных в базу данных. В результате мое веб-приложение не может получить доступ к базе данных (в большинстве случаев) около 2 часов, и это останавливает множество других процессов, а также приложение.

DECLARE @4daysago datetime
SELECT @4daysago = DATEADD(d, -4, GetDate());

SET IDENTITY_INSERT [activetrackarchivedb].dbo.[Data Import] ON;

--Transfer from current (production) DB to Archive DB
WITH CTE as (
    SELECT TOP 1000000 *
    FROM [activetrackdb].dbo.[Data Import] 
    WHERE [activetrackdb].dbo.[Data Import].[Receive Date] < @7daysago
    ORDER BY [Receive Date] ASC)
DELETE CTE
  OUTPUT DELETED.id, 
  DELETED.[Company id], 
  DELETED.[Site id],
  DELETED.[Site name],
  DELETED.[Receive date],
  DELETED.[Detect date],
  INTO  [activetrackarchivedb].dbo.[Data Import] 
  (id, 
  [Company id], 
  [Site id],
  [Site name],
  [Receive date],
  [Detect date]);

Есть ли лучший способ, который я мог бы использовать для "передачи" этих строк? Даже если новый метод был медленнее, он мог бы по крайней мере разрешить доступ к базе данных. На его внедрение у меня ушла неделя (я новичок) и была задействована помощь сообщества Stackru. До сих пор это было здорово, но по мере того, как объем данных увеличивался, процесс стал очень громоздким.

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

Любая помощь будет принята с благодарностью.

Answer 1

Исходя из вашего комментария, CTE кажется ненужным. Вы можете сделать это с помощью гораздо более простого запроса.

DELETE FROM [activetrackdb].dbo.[Data Import]
  OUTPUT 
      DELETED.id, 
      DELETED.[Company id], 
      DELETED.[Site id],
      DELETED.[Site name],
      DELETED.[Receive date],
      DELETED.[Detect date]
  INTO  [activetrackarchivedb].dbo.[Data Import] 
      (id, 
       [Company id], 
       [Site id],
       [Site name],
       [Receive date],
       [Detect date]) 
WHERE [Receive Date] < @7daysago

Answer 2

В SSIS (2008) и многих других местах по умолчанию SQL Server имеет размер номера строки 10000. Возможно, вы захотите поэкспериментировать с размером строк, которые вы делаете за один раз, но я подозреваю, что меньший размер может закончиться выполнением намного быстрее,

Answer 3

Почему бы просто не запустить что-то вроде этого (при условии, что таблицы имеют одинаковые поля):

SET IDENTITY_INSERT [activetrackarchivedb].dbo.[Data Import] ON;

DELETE FROM [activetrackdb].dbo.[Data Import]
OUTPUT DELETED.* INTO  [activetrackarchivedb].dbo.[Data Import] 
WHERE [activetrackdb].dbo.[Data Import].[Receive Date] < DATEADD(DAY,-4,GETDATE())

Я не понимаю, почему это займет гораздо больше времени, и, возможно, намного меньше, чем это. 500 тыс. Строк не так уж много для SQL Server. Аналогичное утверждение с таким количеством строк занимает около 5 секунд на моем ноутбуке.