Удаление данных из большой таблицы

При падении стола даже очень большого, производительность довольно высока. Итак, вот что я бы сделал. Составьте сценарий своей таблицы с индексами из Management Studio. Отредактируйте сценарий и запустите его, чтобы создать копию вашей таблицы. Назовите это table2. Сделайте выборочную вставку, чтобы сохранить данные, которые вы хотите сохранить, в новой таблице2. Переименуйте старую таблицу, скажем, tableOld. Переименуйте table2 с исходным именем. Подождите. Если на вас никто не кричит, бросьте table2. Риск есть. 1) Проверьте, есть ли в исходной таблице триггеры или ограничения. Они не могут быть включены в сценарий, созданный студией управления. 2) если исходная таблица имеет поля идентификаторов, возможно, вам придется включить identity_insert перед вставкой в ​​новую таблицу.

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

Кроме того, как уже упоминалось, удаление должно выполняться несколькими фрагментами вместо одного огромного оператора. Это предотвращает слишком долгую блокировку таблицы и превышение времени ожидания другими процессами завершения удаления.

Если вы используете oracle, Я бы установил раздел по дате в ваших таблицах и индексах. Затем вы удаляете данные, отбрасывая раздел ... данные волшебным образом уйдут вместе с разделом.

Это простой шаг - он не засоряет ваши журналы повторов и т. Д.

Есть базовое введение в все это здесь

Я предполагаю, что вы не можете остановить производственную систему (или поставить в очередь результаты GPS для вставки после завершения очистки).

Я бы согласился с вашей склонностью удалять по частям (возможно, 10%) в зависимости от производительности, которую вы обнаружите в своей тестовой системе.

Индексирована ли ваша таблица? Это может помочь,

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

PseudoSQL: Выберите max (primId) <3_months_ago Удалите из таблицы, где primId

А теперь самое интересное: все эти удаления МОГУТ сделать ваши индексы беспорядочными и потребовать их перестройки, чтобы машина не замедлялась. В этом случае вам придется либо заменить подчиненное устройство на последнюю версию, либо просто потерпеть некоторое время простоя. Убедитесь, что вы проверили этот возможный случай на своей тестовой машине.

Добро пожаловать в хранилище данных. Вам необходимо разделить ваши данные на две части.

• Фактическое приложение, только с текущими данными.

• История.

Вам нужно написать небольшое задание «ETL» для перемещения данных из текущих в историю и удалите перемещенную историю.

Вам нужно запускать это периодически. Ежедневно - еженедельно - ежемесячно, ежеквартально - технически не имеет значения. Важно то, для чего нужна история и кто ее использует.

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

DELETE FROM TABLENAME 
WHERE datediff(day,tableDateTime,getdate() > 90

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

Если вы используете SQL Server 2005 или 2008, разделение с помощью скользящего окна является идеальным решением для этого - мгновенное архивирование или очистка без каких-либо заметных блокировок. Дополнительную информацию см. здесь .

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

Конечно, тогда вам нужно будет беспокоиться о том, чтобы сделать это снова в 6 месяцев или года.

Лучше создать временную таблицу и вставить только те данные, которые вы хотите сохранить. Затем обрежьте исходную таблицу и скопируйте резервную копию.

Синтаксис Oracle (SQL Server аналогичен)

create table keep as select * from source where data_is_good = 1;
truncate table source;
insert into source select * from keep;

Вам необходимо отключить внешние ключи, если они есть в исходной таблице.

В Oracle индексируйте имена должны быть уникальными для всей схемы, а не только для каждой таблицы. В SQL-сервере вы можете дополнительно оптимизировать это, просто переименовав «keep» в «source», так как вы можете легко создавать индексы с одинаковым именем для обеих таблиц

Я бы, вероятно, делал это партиями, как вы уже придумали. Другой вариант - вставить важные данные в другую таблицу, обрезать таблицу GPS, а затем повторно вставить важные данные. У вас будет небольшое окно, в котором вы бы пропустили последние исторические данные. Насколько маленьким будет это окно, будет зависеть от того, сколько данных вам нужно повторно вставить. Кроме того, вам нужно быть осторожным, если в таблице используются числа с автоинкрементом или другие значения по умолчанию, чтобы вы использовали исходные значения.

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

Мои 2 цента:

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

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

I came up with the following T-SQL script which gets an arbitrary amount of recent data.

IF EXISTS(SELECT name FROM sys.tables WHERE name = 'tmp_xxx_tblGPSVehicleInfoLog')
BEGIN
    PRINT 'Dropping temp table tmp_xxx_tblGPSVehicleInfoLog'
    DROP TABLE tmp_xxx_tblGPSVehicleInfoLog
END
GO

PRINT 'Creating temp table tmp_xxx_tblGPSVehicleInfoLog'
CREATE TABLE [dbo].[tmp_xxx_tblGPSVehicleInfoLog](
    [GPSVehicleInfoLogId] [uniqueidentifier] NOT NULL,
    [GPSVehicleInfoId] [uniqueidentifier] NULL,
    [Longitude] [float] NULL,
    [Latitude] [float] NULL,
    [GroundSpeed] [float] NULL,
    [Altitude] [float] NULL,
    [Heading] [float] NULL,
    [GPSDeviceTimeStamp] [datetime] NULL,
    [Milliseconds] [float] NULL,
    [DistanceNext] [float] NULL,
    [UpdateDate] [datetime] NULL,
    [Stopped] [nvarchar](1) NULL,
    [StopTime] [datetime] NULL,
    [StartTime] [datetime] NULL,
    [TimeStopped] [nvarchar](100) NULL
) ON [PRIMARY]
GO

PRINT 'Inserting data from tblGPSVehicleInfoLog to tmp_xxx_tblGPSVehicleInfoLog'
SELECT * INTO tmp_xxx_tblGPSVehicleInfoLog 
FROM tblGPSVehicleInfoLog 
WHERE tblGPSVehicleInfoLog.UpdateDate between '03/30/2009 23:59:59' and '05/19/2009  00:00:00'
GO

PRINT 'Truncating table tblGPSVehicleInfoLog'
TRUNCATE TABLE tblGPSVehicleInfoLog
GO

PRINT 'Inserting data from tmp_xxx_tblGPSVehicleInfoLog to tblGPSVehicleInfoLog'
INSERT INTO tblGPSVehicleInfoLog 
SELECT * FROM tmp_xxx_tblGPSVehicleInfoLog 
GO

Чтобы журнал транзакций не вышел из-под контроля, измените его следующим образом:

DECLARE @i INT
SET @i = 1
SET ROWCOUNT 10000

WHILE @i > 0
BEGIN
    BEGIN TRAN
        DELETE TOP 1000 FROM dbo.SuperBigTable
        WHERE RowDate < '2009-01-01'
    COMMIT
    SELECT @i = @@ROWCOUNT
END
SET ROWCOUNT 0

А вот версия, использующая предпочтительный синтаксис TOP для SQL 2005 и 2008:

DECLARE @i INT
SET @i = 1

WHILE @i > 0
BEGIN
    BEGIN TRAN
        DELETE TOP 1000 FROM dbo.SuperBigTable
        WHERE RowDate < '2009-01-01'
    COMMIT
    SELECT @i = @@ROWCOUNT
END