SQL-запрос для удаления самых старых строк по определенному количеству строки?
У меня есть таблица, которая содержит записи в журнале для программы, которую я пишу. Я ищу идеи о SQL-запросе (я использую SQL Server Express 2005), который сохранит новейшее X количеством записей, и удалять остальных. У меня есть столбец даты и времени, который является меткой времени для записи в журнале.
Я полагаю, что что-то как следующее работало бы, но я не уверен в производительности с В пункте для большего числа записей. Производительность не очень важна, но я мог бы также приложить все усилия, я могу в первый раз.
DELETE FROM MyTable WHERE PrimaryKey NOT IN
(SELECT TOP 10,000 PrimaryKey FROM MyTable ORDER BY TimeStamp DESC)
Я должен упомянуть, что этот запрос будет работать 3-4 раза в день (как часть другого процесса), таким образом, количество записей, которые будут удалены с каждым запросом, будет небольшим по сравнению с количеством учета, который будут вести.
5 ответов
Попробуйте это:
DECLARE @X int
SELECT @X=COUNT(*) FROM MyTable
SET @X=@X-10000
DELETE MyTable
WHERE PrimaryKey IN (SELECT TOP(@x) PrimaryKey
FROM MyTable
ORDER BY TimeStamp ASC
)
зависит от того, удаляете ли вы менее 10 000 строк, если да, то это может работать быстрее, так как определяет строки для удаления, а не строки для сохранения.
DELETE FROM MyTable
WHERE TimeStamp < (SELECT min(TimeStamp)
FROM (SELECT TOP 10,000 TimeStamp
FROM MyTable
ORDER BY TimeStamp DESC))
или
DELETE FROM MyTable
WHERE TimeStamp < (SELECT min(TimeStamp)
FROM MyTable
WHERE PrimaryKey IN (SELECT TOP 10,000 TimeStamp
FROM MyTable
ORDER BY TimeStamp DESC))
Не уверен, что это улучшение с точки зрения эффективности.
Попробуйте это, использует CTE для получения порядкового номера строки, а затем удаляет только X строк за раз. Вы можете изменить эту переменную в соответствии со своим сервером.
Добавление подсказки таблицы ReadPast должно предотвратить блокировку.
:
DECLARE @numberToDelete INT;
DECLARE @ROWSTOKEEP INT;
SET @ROWSTOKEEP = 50000;
SET @numberToDelete =1000;
WHILE 1=1
BEGIN
WITH ROWSTODELETE AS
(
SELECT ROW_NUMBER() OVER(ORDER BY dtsTimeStamp DESC) rn,
*
FROM MyTable
)
DELETE TOP (@numberToDelete) FROM ROWSTODELETE WITH(READPAST)
WHERE rn>@ROWSTOKEEP;
IF @@ROWCOUNT=0
BREAK;
END;
Запрос, который у вас есть, примерно так же эффективен, как и получается, и читается.
NOT IN и NOT EXISTS более эффективны, чем LEFT JOIN/IS NULL, но только потому, что оба столбца никогда не могут быть пустыми. Вы можете прочитать эту ссылку для более глубокого сравнения.
Это зависит от вашего сценария (возможен ли он для вас) и от количества имеющихся у вас строк, но есть потенциально более оптимальный подход.
- Создать новую копию таблицы журнала с новым именем
- Вставить в новую таблицу 10 000 последних записей из исходной таблицы
- Отбросить исходную таблицу (или переименовать)
- Переименовать новую table, на собственное имя
Это, очевидно, требует большего внимания, чем просто удаление строк (например, если в таблице есть столбец IDENTITY, его необходимо установить в новой таблице и т. д.). Но если у вас большая таблица, было бы более эффективно скопировать 10 000 строк в новую таблицу, а затем удалить исходную таблицу, чем пытаться удалить миллионы строк, чтобы оставить только 10 000.