SQL Server 2000 удаляет вершину (1000)
У меня есть большая база данных SQL Server с таблицей приблизительно в 45 миллионах записей. Я архивирую эту таблицу и потребность удалить все записи, больше, чем два года назад. У меня есть вставка в мою хорошо работающую архивную таблицу, но у меня есть проблемы с эффективностью при удалении.
Моя проблема в настоящее время заключается в индексах на таблице. Я хотел бы удалить (и архивная вставка) в 1 000 рекордных блоков. Чтобы сделать это, я должен определить "главные" 1 000 записей, выполняющих требование (больше, чем два года). Штамп DateTime на строке является кластерным индексом, так здорово для захвата строк. Однако SQL, который не позволяет 2000, УДАЛЯЕТ TOP 1000...., таким образом, я должен сделать что-то как:
DELETE FROM <table> WHERE [UniqueID] IN
(SELECT TOP 1000 [UniqueID] FROM <table> WHERE [DateTime] < @TwoYearsAgo)
Это работало бы отлично, если бы UniqueID был индексирован. Так как это не, это занимает очень долгое время (это сканирует таблицу для каждой из 1 000 записей, которые будут удалены). Нет никаких других индексов на таблице, которые однозначно определяют записи. Мне говорят, что это было бы слишком дорогостоящим для вычислений индекса на UniqueID, поскольку это - живой DB. Кто-либо может указать на способ оптимизировать этот запрос?
6 ответов
How about rewriting the query?
SET ROWCOUNT 1000
DELETE FROM <table> WHERE [DateTime] < @TwoYearsAgo
See documentation on SET ROWCOUNT (Transact-SQL).
Also note that per the documentation for DELETE, it supports the TOP clause, but that is apparently new for SQL Server 2005 and up. I'm saying this since it sounds like it isn't supported on your database server, but have you actually tried using it? I don't have access to SQL Server 2000 documentation so I'm unsure if it is supported on that version. It very well might not be.
DELETE TOP (1000) FROM <table> WHERE [DateTime] < @TwoYearsAgo
Note the difference from the way TOP on select can be written, without the parenthesis. For UPDATE, DELETE and INSERT, the expression must be parenthesized, even if it's only a constant number like above.
Вы можете удалить подзапрос:
DELETE <table> FROM (
SELECT TOP 1000 *
FROM <table>
WHERE [DateTime] < @TwoYearsAgo);
См. Пример E: в SQL 2000 DELETE Syntax . Это рекомендуется вместо подхода SET ROWCOUNT. В SQL 2005 и более поздних версиях вы можете напрямую указать TOP в УДАЛИТЬ.
вы также можете сделать
DELETE TOP(1000) FROM <table> WHERE [DateTime] < @TwoYearsAgo
Бог знает, почему они используют top (x) для удаления и top x для выбора, большинство людей, кажется, даже не знают об этой функции!
edit: Очевидно, это 2005+, так что вам, вероятно, следует игнорировать это.
Интересно, должны ли вы придерживаться требования к блокам в 1000 записей. Если это связано с загрузкой сервера или произвольным образом, вы можете попробовать следующее, поскольку у вас уже есть кластеризованный индекс на [DateTime]:
DELETE FROM <table>
WHERE [DateTime] < @TwoYearsAgo
and [DateTime] < (select dateadd(day, 1, min([DateTime])) from <table>)
Некоторое время назад мне пришлось проделать нечто подобное - сделать легкую вставку и удаление, чтобы переместить старые записи в архивную таблицу. Хотя это противоречит интуиции, самым быстрым и наименее эффективным решением, которое я нашел, было:
Создайте небольшую таблицу #temp со значениями идентификаторов для верхних (x) строк. Если ID действительно не может быть проиндексирован в вашем сценарии, вы можете вместо этого использовать дату И ID, чтобы их комбинация могла использовать индекс.
begin tran
Вставить в таблицу архива, где ID и DATE в (# temp)
Удалить из основной таблицы, где ID и DATE в (#temp)
commit
Truncate #temp
Repeat
Наличие временной таблицы для обработки идентификаторов строк - это больше общей работы, чем прямое удаление,
Вы можете использовать SET ROWCOUNT :
SET ROWCOUNT 1000
DELETE FROM <table> WHERE [DateTime] < @TwoYearsAgo